If you’re looking for what is in the buffer cache, use this: select COUNT(*) ct, o.object_name, o.subobject_name, s.blocks from v$bh bh, dba_objects o, dba_segments s WHERE o.data_object_id = bh.objd AND o.owner like ‘SCHEMA_OWNER’ AND o.owner = s.owner AND o.object_name = s.segment_name AND NVL(o.subobject_name,’x’) = NVL(s.partition_name,’x’) AND s.blocks > 0 AND bh.status != ‘free’ GROUP BY […]

Introduced in 12c, in some situations we can gather stats online when the data is loaded without taking any extra time: 1) When doing a direct path load (the APPEND or PARALLEL hint uses direct path loads) 2) The table is empty before the load. If both those are true, use the hint GATHER_OPTIMIZER_STATISTICS and […]

Check this if you can’t kill a session

Sometimes a session is in v$session, but when you try to kill it you get an ORA-00030 Session Is does not exist. That session may be from a client that disconnected, and Oracle is now cleaning it up (i.e. rolling back whatever it had done). One way to check is to see if the session’s […]

Read more →

Check system stats for the session

I always have to look this query up, so am posting it here to save time. SELECT s.name,m.value FROM v$sysstat s, v$mystat m WHERE s.STATISTIC#=m.STATISTIC# ORDER BY m.value DESC, s.name /

Read more →

Kill Sessions Running a Program

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 ) /

Read more →

Finding what tables are in INMEMORY

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

Read more →

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 →