Sometimes it is necessary to re-set the value of an Oracle sequence: DECLARE PROCEDURE set_sequence_value (p_sequence_owner IN VARCHAR2, p_sequence_name IN VARCHAR2, p_new_value IN NUMBER) IS l_reduce_by NUMBER; l_last_number NUMBER; l_seq_val NUMBER; BEGIN SELECT last_number INTO l_last_number FROM dba_sequences s WHERE sequence_owner = p_sequence_owner AND sequence_name = p_sequence_name; dbms_output.put_line(‘last number: ‘||l_last_number); IF l_last_number != p_new_value THEN […]

When using a hot/hot environment with GoldenGate, it is necessary to avoid PK collisions. The standard approach is for one side to use even numbers and the other to use odd. Here’s a script that will change all the sequences within a schema to either even or odd: DECLARE PROCEDURE set_sequence (i_owner IN VARCHAR2, i_sequence […]

Identify what tables are updated

Sometimes it is necessary to find if there are any cases where a table is updated, such as if you’re considering compressing a table and want to see if there will be an updates that come along after and uncompress the data. It isn’t perfect, but one handy place to look is in the Plan […]

Read more →

Find object from the file id and block number

Every time I hit a wait event where I want to see what object is being hit, I have to look up the query to get the object from the file id and block #, so I’m posting it here for easy access: select e.segment_name, e.* FROM dba_extents e WHERE file_id = 123456 AND 6327867 […]

Read more →

Execute a step in a different edition

With Oracle’s EBR we have different editions for our different application releases with each edition containing a different version of your application code (packages, views, synonyms, etc). By default, all sessions will run in the Default Edition and see the objects in that edition. We have the option of changing the edition, either with: ALTER […]

Read more →

Good post about library cache pin waits

I was going to write up a post about researching library cache pin locks, but someone else has done a very thorough job already: https://orainternals.wordpress.com/2009/06/02/library-cache-lock-and-library-cache-pin-waits/. Especially useful is this query: select distinct ses.ksusenum sid, ses.ksuseser serial#, ses.ksuudlna username,ses.ksuseunm machine, ob.kglnaown obj_owner, ob.kglnaobj obj_name ,pn.kglpncnt pin_cnt, pn.kglpnmod pin_mode, pn.kglpnreq pin_req , w.state, w.event, w.wait_Time, w.seconds_in_Wait from […]

Read more →

Identify sessions that are blocking other sessions

Sometimes an application will be plagued by sessions that are abandoned partway through a transaction, leaving locks on rows. Other sessions may try to update/delete these locked rows and will hang while waiting for the locks to be release. Here is a simple query against the ASH view that will find any sessions that have […]

Read more →

Handy Edition Based Redefinition queries

A few queries that are useful to have on hand:   SELECT sys_context(‘userenv’,’current_edition_name’) current_edition_name from dual; ALTER SESSION SET EDITION = R_ABC; ALTER SESSION SET EDITION = ora$base; SELECT * FROM dba_editions; SELECT * FROM database_properties WHERE property_name = ‘DEFAULT_EDITION’; SELECT * FROM dba_objects_ae WHERE edition_name IS NOT NULL ORDER BY object_type,object_name; SELECT * FROM dba_editioning_views_ae ORDER BY owner; SELECT username, editions_enabled FROM dba_users ORDER BY username;

Read more →

ORA-38807: Implementation restriction: an edition can have only one child

When creating a new edition, you get: ORA-38807: Implementation restriction: an edition can have only one child Try this: Instead of CREATE EDITION VERSION_10; Use: CREATE EDITION VERSION_10 AS CHILD OF VERSION_9; The problem seems to be an Oracle bug in 11g and some 12c versions where the pointers to the editions are incorrect, so […]

Read more →

Query to identify wait events

Just a quick query to run to see if there’s a particular wait event that’s spiking during a timeframe: select COUNT(*), event, sample_time, inst_id from gv$active_session_history where sample_time BETWEEN TRUNC(SYSDATE) + (10/24) + (50/60)/24 AND TRUNC(SYSDATE) + (10/24) + (56/60)/24 HAVING COUNT(*) > 10 GROUP BY event, sample_time, inst_id ORDER BY sample_time, event, instead ; […]

Read more →