Real-time Oracle DBA SQL query Set packed in as a Survival Kit to Help DB Administrators at Entry Level Support

Sample Queries From The EBook

SAMPLE SQL Queries from the EBook. To Purchase, Use The "Buy Now" Button At Home Page. 

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 the EBook. To Purchase, Use The "Buy Now" Button At The Home Page.