Handy script to kill all sessions that are running a particular package: select distinct ‘ ALTER SYSTEM KILL SESSION ‘||””||sid||’,’||serial#|| ‘,@’ || inst_id ||”” ||’  IMMEDIATE;’   from gv$session s where EXISTS ( select ‘x’ from gv$access a where object = ‘package name’ and a.inst_id = s.inst_id and a.sid = s.sid ) /

Since INMEMORY allows the INEMOMERY and its compression settings to be at the column level, when looking at what we have INMEMORY, besides v$im_segments, we need to also look at V_$IM_COLUMN_LEVEL. When checking for INMEMORY, I look at:   dba_tables – Is the table configured for INMEMORY. v$im_segments – Has the table actually been loaded into […]

Re-set the value of an Oracle sequence

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 […]

Read more →

Setting sequences to only even or odd numbers

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 […]

Read more →

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 →