Real-time Oracle DBA SQL queries packed as an Survival Kit. Oracle Database Administrators are welcome for knowledge sharing and discussions.

Sample Queries from the Survival Kit E-Book

SAMPLE SQL Queries from Oracle DBA Survival Kit. For Complete set of more than 1000 queries/commands/scripts/guides, please buy the "Oracle DBA Survival Kit"

Managing tablespaces

To Find % of free space left

                      (BYTES/1024)/1024 "Used Space(MB)",
                      total  "allocated size(MB)",
                      maxi "maximum allowable (MB)",
                      maxi-(BYTES/1024)/1024 "effectivefree(MB)",
                      --maxi-total "free(MB)",
                      round(((maxi-(BYTES/1024)/1024)/maxi)*100,2) "% FREE"
                      SM$TS_USED,(select sum((BYTES/1024)/1024)
                      total,sum((decode(MAXBYTES,0,bytes,maxbytes)/1024)/1024)  maxi from
                      dba_data_files where tablespace_name in ('&tbs')) where
                      tablespace_name in ('&tbs');

To list all the datafiles of a given tablespace

col file_name for a60
set lines 170
set pages 200  
select tablespace_name,file_name,bytes/1024/1024,maxbytes/1024/1024,autoextensible from dba_data_files
where tablespace_name='&tablespace_name' order by file_name ;

Space left to extend for autoextensible files

col file_name for a40
set lines 170 pages 0  
compute SUM of space_left_to_extend on report
select tablespace_name,file_name,bytes/1024/1024,maxbytes/1024/1024,autoextensible,(maxbytes/1024/1024)-(bytes/1024/1024) space_left_to_extend from dba_data_files
where file_name like '%/idwl/index6/%' and autoextensible='YES' order by file_name;

Complete Tablespace Monitoring query (DATABASE WIDE!!!)

set lines 150
set pages 5000
column dummy noprint
column  pct_used format 999.9       heading "% USED"
column  name    format a30      heading "Tablespace Name"
column  Mbytes   format 999,999,999,999    heading "TOTAL_ALLOCATED_IN_MB"
column  used    format 999,999,999,999   heading "USED_IN_MB"
column  free    format 999,999,999,999  heading "FREE_IN_MB"
column  largest    format 999,999,999,999  heading "LARGEST"
clear breaks
clear computes

PROMPT ==========================

select nvl(b.tablespace_name,
             nvl(a.tablespace_name,'UNKOWN')) name,
       Mbytes_alloc Mbytes,
       Mbytes_alloc-nvl(Mbytes_free,0) used,
       nvl(Mbytes_free,0) free,
                          Mbytes_alloc)*100 pct_used,
       nvl(largest,0) largest
from ( select sum(bytes)/1024/1024 Mbytes_free,
              max(bytes)/1024/1024 largest,
       from  sys.dba_free_space
       group by tablespace_name ) a,
     ( select sum(bytes)/1024/1024 Mbytes_alloc,
       from sys.dba_data_files
       group by tablespace_name )b
where a.tablespace_name (+) = b.tablespace_name order by 1;

 Resolving blocking sessions

Blockers-Waiters Info

col SESS for a12
set lines 132
SELECT DECODE(request,0,'Holder: ','Waiter: ')||sid sess,
   id1, id2, lmode, request, type
WHERE (id1, id2, type) IN
      (SELECT id1, id2, type FROM V$LOCK WHERE request>0)
ORDER BY id1, request;

To check for blocking sessions



select  *  from v$lock  where block>0;

select * from gv$lock where block>0;

select * from gv$lock where request>0;

select distinct BLOCKING_OTHERS from dba_locks;

Blocking sessions in RAC

select L1.sid "Blocking SID", L1.inst_id,
           L2.sid "Waiting SID", L2.inst_id,
           trunc(S2.seconds_in_wait/60) "Blocked Minutes"
    from gv$lock L1, gv$lock L2, gv$session_wait S2
    where L1.block = 1 and L2.request >  0
    and L1.id1 = L2.id1
    and L1.id2 = L2.id2;

select l1.sid, ' IS BLOCKING ', l2.sid
    from gv$lock l1, gv$lock l2
    where l1.block =1 and l2.request > 0
    and l1.id1=l2.id1
    and l1.id2=l2.id2;

 Invalid objects

Indentifying Invalid Objects

select count(*) from dba_objects where status='INVALID';

select owner,object_type,count(*) from dba_objects where status='INVALID' group by owner,object_type;

select owner,object_name,object_type,created,status,last_ddl_time from dba_objects where status='INVALID' group by owner,object_type,object_name,created,status,last_ddl_time;

To Compile Invalid Objects


Manual Compile

set heading off;
set feedback off;
set echo off;
Set lines 999;
Spool run_invalid.sql
select 'ALTER ' || OBJECT_TYPE || ' ' || OWNER || '.' || OBJECT_NAME || ' COMPILE;' from dba_objects where status='INVALID' and object_type in ('PACKAGE','FUNCTION','PROCEDURE');
spool off;
set heading on;
set feedback on;
set echo on;

Library Cache Locks

Library cache resource types waited for over the life of the instance

set linesize 152
column average_wait format 9999990.00

     select     substr(e.event, 1, 40) event,
          e.time_waited / decode(
          'latch free', e.total_waits,
          decode(e.total_waits - e.total_timeouts,0, 1,e.total_waits - e.total_timeouts)) average_wait
     from     sys.v$system_event e,
          sys.v$instance i
     where     e.event like '%library cache%';
Detect sessions waiting for a Library Cache Locks
select sid Waiter, p1raw,
     substr(rawtohex(p1),1,30) Handle,
     substr(rawtohex(p2),1,30) Pin_addr
     from v$session_wait where wait_time=0 and event like '%library cache%';
Detect Library Cache holders that sessions are waiting for
select sid Holder ,KGLPNUSE Sesion , KGLPNMOD Held, KGLPNREQ Req
     from x$kglpn , v$session
     where KGLPNHDL in (select p1raw from v$session_wait
where wait_time=0 and event like '%library cache%')
     and KGLPNMOD <> 0
     and v$session.saddr=x$kglpn.kglpnuse ;
Sessions waiting for lib cache in RAC
select a.sid Waiter,b.SERIAL#,a.event,a.p1raw,
         substr(rawtohex(a.p1),1,30) Handle,
         substr(rawtohex(a.p2),1,30) Pin_addr
         from v$session_wait a,v$session b where a.sid=b.sid
         and a.wait_time=0 and a.event like 'library cache%';
Sessions holding the lib cache in RAC
select a.sid Holder ,a.SERIAL#,b.INST_ID,b.KGLPNUSE Sesion , b.KGLPNMOD Held, b.KGLPNREQ Req
         from x$kglpn b , v$session a
         where b.KGLPNHDL in (select p1raw from v$session_wait
         where wait_time=0 and event like 'library cache%')
         and b.KGLPNMOD <> 0
         and a.saddr=b.kglpnuse ;


SAMPLE SQL Queries from Oracle DBA Survival Kit. For Complete set of more than 1000 queries/commands/scripts/guides, please buy the "Maari Consulting Oracle DBA Survival Kit Version 1"...