Sharing Tips & Tricks from my Database Experience
Tag: sql
Oracle's lock-free reservations, introduced in version 23c, address the issue of concurrent transaction blocking when updating specific columns. This functionality allows multiple transactions to modify the same row without traditional locking mechanisms. Here's a breakdown of how it works: Reservable Columns: You designate specific numeric columns within a table as "reservable." These columns are typically used … Continue reading Using Oracle’s Lock-Free Reservable Columns with GoldenGate
Every GoldenGate configuration requires an initial instantiation of the target database, often referred to as “Initial Load”. GoldenGate for Oracle is very flexible and allows customers to use different methodology and approaches to instantiate the target database. The most common approaches are using database-specific tools like Datapump, RMAN, and other file system approaches. GoldenGate however has its own Initial … Continue reading GoldenGate Microservices Initial Load Instantiation with WebUI
Oracle GoldenGate Replicat is a software program that plays a crucial role in data replication within the Oracle GoldenGate data integration platform. It's responsible for applying changes captured from a source database to a target database, ensuring data consistency between the two database systems. Replicat is also responsible to deliver transaction to Big Data systems with … Continue reading GoldenGate Replicat? Which one should I use?
When Oracle GoldenGate encounters tables without a primary key (PK) for data replication, it has different approaches depending on the situation: 1. Using Unique Indexes: If a unique index exists on the table, GoldenGate can use those columns to identify rows for updates and deletes. This is the preferred approach as it avoids full table scans on … Continue reading No PK with GoldenGate?
-- Shows SQL statements currently in shared pool with high physical I/O, it sorts by the high I/O first SELECT buffer_gets , disk_reads , executions , buffer_gets/executions bufovex , sql_text FROM v$sql WHERE executions != 0 ORDER BY disk_reads desc
Using the link below one can create SQL script to create database http://geekswithblogs.net/dotNETPlayground/archive/2008/04/18/121331.aspx
Find SQL currently executing in a session: https://oraclespin.wordpress.com/2010/05/09/how-to-find-sql-running-for-a-usersid/ Session running waiting on SYS@TESTDB> SELECT event, state, p1, p2, p3, seconds_in_wait FROM v$session_wait where sid = 247; EVENT STATE P1 P2 P3 SECONDS_IN_WAIT ---------------------------------------------------------------- --------------- ---- ---------- ---------- ---------- ------------------- db file scattered read WAITED KNOWN TIME 122 1188369 128 30 1 row selected. SYS@TESTDB> SELECT … Continue reading How to check/find session information (wait, io, lock, time)
Code that be used to scripts to extract role permissions for a database for SQL authenticated accounts, it is useful when rebuilding a server and after databases are reattached the SQL authenticated users access has to be granted. DECLARE db_cursor CURSOR FORSELECT 'exec sp_addrolemember ''' + g.name + ''', ''' + sl.name + '''', sl.nameFROM … Continue reading Script to create grants to roles to database for SQL authenticated users.
DECLARE @role_name VARCHAR(100) DECLARE @member_name VARCHAR(100) DECLARE @rolescur CURSOR DECLARE @rolememberscur CURSOR DECLARE @loginname VARCHAR(100) DECLARE @username VARCHAR(100) DECLARE @logincur CURSOR PRINT '-- Grant user access' SET @logincur = CURSOR FOR SELECT l.name, u.name FROM master..sysxlogins l, sysusers u WHERE l.sid = u.sid AND l.name <> 'sa' OPEN @logincur FETCH NEXT FROM @logincur INTO @loginname, … Continue reading How to create SQL scripts to recreate grants on a database granted through a role?
Using Oracle SID and SERIAL# can turn trace on/off a Oracle session that has already started. The values of SID and serial# can be obtained from GV$SESSION. This will create trace file in directory set by the parameter user_dump_dest. To find the SID and SERIAL# you want to debug you can query GV$session to query … Continue reading Enable trace in a running session from PL/SQL or SQL*Plus
alexlima.com 