Way to success...

--"Running Away From Any PROBLEM Only Increases The DISTANCE From The SOLUTION"--.....--"Your Thoughts Create Your FUTURE"--.....--"EXCELLENCE is not ACT but a HABIT"--.....--"EXPECT nothing and APPRECIATE everything"--.....

Thursday, July 28, 2016

Query To Get Count and List of Invalid Objects


Download(invalids.sql)

REM +======================================================================+
REM                    
REM File Name: invalids.sql
REM 
REM Description:
REM   Query To Get Count and List of Invalid Objects
REM   
REM Notes:
REM   Usage: sqlplus "/ as sysdba" @invalids.sql
REM   
REM +======================================================================+

PROMPT
PROMPT >>>Invalid Objects Count<<<
PROMPT

clear columns
set lines 180
compute sum label "Total Invalid Count" of count on report
break on report
select owner, count(*) count from all_objects where status = 'INVALID' group by owner;

PROMPT
PROMPT >>>List of Invalid Objects<<<
PROMPT

clear columns
SET lines 180
SET pages 150
COL OWNER for a12
SELECT   owner, 
         object_name, 
         object_type, 
         TO_CHAR (created, 'DD-MON-YYYY HH24:MI:SS') "CREATED", 
         TO_CHAR (last_ddl_time, 'DD-MON-YYYY HH24:MI:SS') "LAST_DDL_TIME", 
         TIMESTAMP, 
         status
    FROM all_objects
   WHERE status = 'INVALID'
ORDER BY TO_DATE (last_ddl_time, 'DD-MON-YYYY HH24:MI:SS') ASC;

Query To Check library cache pin Events/Locks


Download(libcachepin.sql)

REM +======================================================================+
REM                    
REM File Name: libcachepin.sql
REM 
REM Description:
REM   Query To Check library cache pin Events/Locks
REM   
REM Notes:
REM   Usage: sqlplus "/ as sysdba" @libcachepin.sql 
REM   
REM +======================================================================+

clear columns
set lines 180
col object_type for a30 WORD_WRAPPED
col object_name for a20 WORD_WRAPPED
col username for a15 WORD_WRAPPED
select decode(lob.kglobtyp, 0, 'NEXT OBJECT', 1, 'INDEX', 2, 'TABLE', 3, 'CLUSTER',
                      4, 'VIEW', 5, 'SYNONYM', 6, 'SEQUENCE',
                      7, 'PROCEDURE', 8, 'FUNCTION', 9, 'PACKAGE',
                      11, 'PACKAGE BODY', 12, 'TRIGGER',
                      13, 'TYPE', 14, 'TYPE BODY',
                      19, 'TABLE PARTITION', 20, 'INDEX PARTITION', 21, 'LOB',
                      22, 'LIBRARY', 23, 'DIRECTORY', 24, 'QUEUE',
                      28, 'JAVA SOURCE', 29, 'JAVA CLASS', 30, 'JAVA RESOURCE',
                      32, 'INDEXTYPE', 33, 'OPERATOR',
                      34, 'TABLE SUBPARTITION', 35, 'INDEX SUBPARTITION',
                      40, 'LOB PARTITION', 41, 'LOB SUBPARTITION',
                      42, 'MATERIALIZED VIEW',
                      43, 'DIMENSION',
                      44, 'CONTEXT', 46, 'RULE SET', 47, 'RESOURCE PLAN',
                      48, 'CONSUMER GROUP',
                      51, 'SUBSCRIPTION', 52, 'LOCATION',
                      55, 'XML SCHEMA', 56, 'JAVA DATA',
                      57, 'SECURITY PROFILE', 59, 'RULE',
                      62, 'EVALUATION CONTEXT',
                     'UNDEFINED') object_type,
       lob.KGLNAOBJ object_name,
       pn.KGLPNMOD lock_mode_held,
       pn.KGLPNREQ lock_mode_requested,
       ses.sid,
       ses.serial#,
       ses.username
  FROM
       x$kglpn pn,
       v$session ses,
       x$kglob lob,
       v$session_wait vsw
  WHERE
   pn.KGLPNUSE = ses.saddr and
   pn.KGLPNHDL = lob.KGLHDADR
   and lob.kglhdadr = vsw.p1raw
   and vsw.event = 'library cache pin'
order by lock_mode_held desc;

Query To Monitor Database Session Waits



Download(session_wait.sql)

REM +======================================================================+
REM                    
REM File Name: session_wait.sql
REM 
REM Description:
REM   Query To Monitor Database Session Waits
REM   
REM Notes:
REM   Usage: sqlplus "/ as sysdba" @session_wait.sql 
REM   
REM +======================================================================+

clear columns
set lines 120
set pages 1000
col event for a30
select inst_id,sid, event, p1, p2, p3, p1raw from gv$session_wait
where event not like '%messag%' and event not in ('pipe get','PL/SQL lock timer','Streams AQ: qmn slave idle wait','Streams AQ: waiting for time management or cleanup tasks','Streams AQ: qmn coordinator idle wait')
and (wait_time=0 or state='WAITING')
order by 2;
/


Query To Monitor Temporary Tablespace Usage


Download(temptsmon.sql)

REM +======================================================================+
REM                    
REM File Name: temptsmon.sql
REM 
REM Description:
REM   Query To Check Temporary Tablespace Usage
REM   
REM Notes:
REM   Usage: sqlplus "/ as sysdba" @temptsmon.sql
REM   
REM +======================================================================+

clear columns
set lines 180
col Per_Used for a10
col Per_Free for a10
select TABLESPACE ,round(MB_TOTAL) MB_TOTAL,round(MB_USED) MB_USED,round(MB_FREE) MB_FREE,
                           round(((MB_TOTAL-MB_FREE)*100)/MB_TOTAL)||'%' Per_Used ,
                           round(((MB_TOTAL-MB_USED)*100)/MB_TOTAL)||'%' Per_Free
                from
                (SELECT   A.tablespace_name tablespace, D.mb_total,
                                 SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_used,
                                 D.mb_total - SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_free
                FROM     v$sort_segment A,
                                 (
                                 SELECT   B.name, C.block_size, SUM (C.bytes) / 1024 / 1024 mb_total
                                 FROM     v$tablespace B, v$tempfile C
                                 WHERE    B.ts#= C.ts#
                                 GROUP BY B.name, C.block_size
                                 ) D
                WHERE    A.tablespace_name = D.name
                GROUP by A.tablespace_name, D.mb_total);

Query To Monitor High Temporary Tablespace Usage Sessions


Download(tempsessions.sql)


REM +======================================================================+
REM                    
REM File Name: tempsessions.sql
REM 
REM Description:
REM   Query To Monitor High Temporary Tablespace Usage Sessions 
REM   
REM Notes:
REM   Usage: sqlplus "/ as sysdba" @tempsessions.sql 
REM 
REM Input Required:
REM    SizeInMB : TEMP Usage Size(MB) For Session
REM   
REM +======================================================================+

ACCEPT SizeInMB PROMPT "Enter TEMP Usage Size(MB) For Session: ";

clear columns
set lines 180
set pages 50
Col TABLESPACE for a12
Col USERNAME for a10
col OSUSER for a20
col MODULE for a20 WORD_WRAPPED
col PROGRAM for a20 WORD_WRAPPED
col MACHINE for a15
col inst_id for 99999
select * from (
SELECT   T.tablespace,S.inst_id,S.SID,S.serial#,S.username, S.osuser, s.module,
     S.program,S.MACHINE , SUM (T.blocks) * TBS.block_size / 1024 / 1024 mb_used, 
     COUNT(*) sort_ops
FROM     gv$sort_usage T, gv$session S, dba_tablespaces TBS, gv$process P
WHERE    T.session_addr = S.saddr
AND      S.paddr = P.addr
AND      T.tablespace = TBS.tablespace_name
GROUP BY S.inst_id,S.sid, S.serial#, S.username, S.osuser, P.spid, S.module,S.client_identifier,
     S.program, TBS.block_size, T.tablespace,S.MACHINE)
         where MB_USED > &SizeInMB;

undef SizeInMB

Query To Check Database Tablespace Usage With Percentage



Download(tsusage.sql)

REM +======================================================================+
REM                    
REM File Name: tsusage.sql
REM 
REM Description:
REM   Query To Check Database Tablespace Usage With Percentage
REM   This Query Also Takes Care If the Datafile Autoextend is ON.
REM
REM Notes:
REM   Usage: sqlplus "/ as sysdba" @tsusage.sql 
REM   
REM +======================================================================+

clear columns
set lines 180
set pages 100
select distinct a.tablespace_name,
    SUM(decode(b.maxextend, null, A.BYTES/1024/1024, b.maxextend*8192/1024/1024)) "Total (MB)",
    (SUM(a.bytes)/1024/1024 - round(c."Free"/1024/1024)) "Used (MB)",
    round(((SUM(a.bytes)/1024/1024 - round(c."Free"/1024/1024))/SUM(decode(b.maxextend, null, A.BYTES/1024/1024, b.maxextend*8192/1024/1024)))*100) "%Used", 
    (SUM(decode(b.maxextend, null, A.BYTES/1024/1024, b.maxextend*8192/1024/1024)) - (SUM(a.bytes)/1024/1024 - round(c."Free"/1024/1024))) "Free (MB)",
    round(((SUM(decode(b.maxextend, null, A.BYTES/1024/1024, b.maxextend*8192/1024/1024)) - (SUM(a.bytes)/1024/1024 - round(c."Free"/1024/1024)))/SUM(decode(b.maxextend, null, A.BYTES/1024/1024, b.maxextend*8192/1024/1024)))*100) "%Free"
    --round(100*(SUM(a.bytes)/1024/1024 - round(c."Free"/1024/1024))/(SUM(decode(b.maxextend, null, A.BYTES/1024/1024, b.maxextend*8192/1024/1024)))) "UPercent"
    from
    dba_data_files a,
    sys.filext$ b,
    (SELECT d.tablespace_name , sum(nvl(c.bytes,0)) "Free" FROM dba_tablespaces d,DBA_FREE_SPACE c where d.tablespace_name = c.tablespace_name(+) group by d.tablespace_name) c
    where a.file_id = b.file#(+)
    and a.tablespace_name = c.tablespace_name
    GROUP by a.tablespace_name, c."Free"/1024
    --HAVING round(((SUM(decode(b.maxextend, null, A.BYTES/1024/1024, b.maxextend*8192/1024/1024)) - (SUM(a.bytes)/1024/1024 - round(c."Free"/1024/1024)))/SUM(decode(b.maxextend, null, A.BYTES/1024/1024, b.maxextend*8192/1024/1024)))*100) <= 10
    order by round(((SUM(decode(b.maxextend, null, A.BYTES/1024/1024, b.maxextend*8192/1024/1024)) - (SUM(a.bytes)/1024/1024 - round(c."Free"/1024/1024)))/SUM(decode(b.maxextend, null, A.BYTES/1024/1024, b.maxextend*8192/1024/1024)))*100) asc
/

Query To Monitor Database Locks



Download(locks.sql)

REM +======================================================================+
REM                    
REM File Name: locks.sql
REM 
REM Description:
REM   Query To Monitor Database Locks
REM   
REM Notes:
REM   Usage: sqlplus "/ as sysdba" @locks.sql 
REM   
REM +======================================================================+

clear columns
set lines 230
set pages 100
col STATUS for a8
col MACHINE for a12 
col "SCHEMA" for a8
col EVENT for a20 WORD_WRAPPED
col ACTION for a20 WORD_WRAPPED
col client_identifier for a15 WORD_WRAPPED
col blocking_sid for a12
col b_inst for a8
col inst_id for 99999
SELECT   a.inst_id, --RAC
       b.status
     , a.SID
     , a.serial#
     , NVL (TO_CHAR (a.blocking_session), ' ') blocking_sid  --RAC
     , NVL (TO_CHAR (a.blocking_instance), ' ') b_inst --RAC
     , a.SCHEMANAME "SCHEMA"
     , a.machine
     , a.event
     , a.status "SIDSTAT"
     --, a.module
     , a.client_identifier --For R12.X
     , a.action
     , round((a.seconds_in_wait / 60)) "WAIT_MINS"     
     , a.last_call_et "LAST_CALL_ET"
     --, a.sql_id
   FROM gv$session a
     , (SELECT   inst_id
     , DECODE (request, 0, 'Blocker ', 'Waiter ') status
     , SID
     FROM gv$lock
       WHERE (id1, id2, TYPE) IN (SELECT id1, id2, TYPE FROM gv$lock WHERE request > 0) ORDER BY id1, request) b
  WHERE a.inst_id = b.inst_id 
  AND a.SID = b.SID;