Useful SQL Scripts

Useful SQL Scripts for day-to-day DBA tasks.

SQL to find the locked object and session that locked the object.

Run below script when user complaints that update statement hangs...

column LOCKED_OBJECT format A35 wrapped
column ORACLE_USERNAME format A17 wrapped
column OS_USER_NAME format A12 wrapped
column SESSION_ID format 999999 wrapped
column SINCE format A21
select o.OWNER || '.' || o.OBJECT_NAME as LOCKED_OBJECT,
lo.ORACLE_USERNAME,
lo.OS_USER_NAME,
lo.SESSION_ID,
lo.PROCESS,
case lo.LOCKED_MODE
when 0 then 'none'
when 1 then 'null (NULL)'
when 2 then 'row-S (SS)'
when 3 then 'row-X (SX)'
when 4 then 'share (S)'
when 5 then 'S/Row-X (SSX)'
when 6 then 'exclusive (X)'
end as LOCKED_MODE,
cast(sysdate-(CTIME/(24*60*60)) as timestamp(0)) as SINCE
from DBA_OBJECTS o,
V$LOCKED_OBJECT lo,
V$LOCK l
where o.OBJECT_ID = lo.OBJECT_ID
and lo.OBJECT_ID = l.ID1 and lo.SESSION_ID = l.SID
order by LOCKED_OBJECT, ORACLE_USERNAME, OS_USER_NAME, SESSION_ID;

------------------------------------------------------------------

When a session is waiting for lock, how to find the blocking session and waiting for object and row ...

To find who is blocking ?

select sb.username || '@' || sb.machine
|| ' ( SID=' || sb.sid || ' ) is blocking '
|| sw.username || '@' || sw.machine || ' ( SID=' || sw.sid || ' ) ' AS blocking_status
from v$lock lb, v$session sb, v$lock lw, v$session sw
where sb.sid=lb.sid and sw.sid=lw.sid
and lb.BLOCK=1 and lw.request > 0
and lb.id1 = lw.id1
and lw.id2 = lw.id2 ;

Waiting for object and rowid ...

select o.object_name, row_wait_obj#,  row_wait_file#,  row_wait_block#, row_wait_row#,
dbms_rowid.rowid_create ( 1, o.DATA_OBJECT_ID, ROW_WAIT_FILE#, ROW_WAIT_BLOCK#, ROW_WAIT_ROW# )
from v$session s, dba_objects o where sid=&waiting_sid and s.ROW_WAIT_OBJ# = o.OBJECT_ID ;
 
Waiting for row..
 
select * from table_name_from_above where rowid =&rowid_returned
 
------------------------------------------------------------

To check dataguard

When you get alert that standby database is out of sync..

Run this at Primary

set pages 1000
set lines 120
column DEST_NAME format a20
column DESTINATION format a35
column ARCHIVER format a10
column TARGET format a15
column status format a10
column error format a15
select DEST_ID,DEST_NAME,DESTINATION,TARGET,STATUS,ERROR from v$archive_dest
where DESTINATION is NOT NULL
/
select ads.dest_id,max(sequence#) "Current Sequence",
max(log_sequence) "Last Archived"
from v$archived_log al, v$archive_dest ad, v$archive_dest_status ads
where ad.dest_id=al.dest_id and al.dest_id=ads.dest_id group by ads.dest_id;

Run this at Standby

select max(al.sequence#) "Last Seq Recieved" from v$archived_log al
/
select max(al.sequence#) "Last Seq Apllied" from v$archived_log al
where applied ='YES'
/
select process,status,sequence# from v$managed_standby
/

Note: This script won't work for RAC

-----------------------------------------------------------------------------------

To get hidden parameters values

-- must be run from SYS.

SELECT
a.ksppinm "Parameter",
a.ksppdesc "Description",
b.ksppstvl "Session Value",
c.ksppstvl "Instance Value"
FROM
sys.x$ksppi a,
sys.x$ksppcv b,
sys.x$ksppsv c
WHERE
a.indx = b.indx
AND
a.indx = c.indx
AND
a.ksppinm LIKE '/_%' escape '/'
/
-------------------------------------------------------------------
To generate user creation sql.

This may be useful when you do schema refresh with expdp/impdp

set pagesize 0
set heading off
set long 999999999
set feedback off
set echo off

SELECT DBMS_METADATA.GET_DDL('USER', '&1') FROM DUAL;
SELECT DBMS_METADATA.GET_GRANTED_DDL('SYSTEM_GRANT','&1') FROM DUAL;
SELECT DBMS_METADATA.GET_GRANTED_DDL( 'ROLE_GRANT','&1') FROM DUAL;
SELECT 'GRANT ' || PRIVILEGE || ' ON ' || OWNER || '.' || TABLE_NAME || ' TO ' || GRANTEE || ';' FROM DBA_TAB_PRIVS WHERE GRANTEE = '&1';
SELECT 'ALTER USER '||USERNAME||' QUOTA UNLIMITED ON '||TABLESPACE_NAME||';' FROM DBA_TS_QUOTAS WHERE USERNAME='&1';

-------------------------------------------------------------------

Check for long running Transactions:

prompt Current transactions open for more than 20 minutes
prompt

col runlength HEAD 'Txn Open Minutes' format 9999.99
col sid HEAD 'Session' format a13
col xid HEAD 'TransactionID' format a18
col terminal HEAD 'Terminal' format a10
col program HEAD 'Program' format a27 wrap

select t.inst_id, sid||','||serial# sid,xidusn||'.'||xidslot||'.'||xidsqn xid, (sysdate - start_date )* 1440
runlength ,terminal,program from gv$transaction t, gv$session s where t.addr=s.taddr
and (sysdate - start_date) * 1440 > 20;

--------------------------------------------------------------------------