Posted in 
chkstats.sql
--- chkstats.sql get a snap shot of sys & sess stats from when chkstatc was run
col STAT_NAME format a40
col STAT_VALUE format 999,999,999,999
select c.STAT_NAME, s.SESS_VALUE - c.SESS_VALUE SESS_VALUE, s.SYS_VALUE- c.SYS_VALUE SYS_VALUE
from chkstat_tmptab c,
(SELECT name.name stat_name, sstat.value SYS_VALUE, mstat.value SESS_VALUE
FROM v$sysstat sstat, v$statname name, v$mystat mstat
WHERE sstat.statistic# = name.statistic#
and mstat.statistic# = name.statistic#
and name.name in
('recursive calls','db block gets','consistent gets','physical reads','redo size','bytes sent via SQL*Net to client'
,'bytes received via SQL*Net from client','SQL*Net roundtrips to/from client','sorts (memory)','sorts (disk)')) s
where c.STAT_NAME = s.STAT_NAME
order by c.STAT_NAME;
orasar.sql
---orasar.sql listing of System Activity for the past 24 hours
set pages 1000 lines 160
col SNAP_Times format a22
col logons format 99999
col CPU format 99999999
col Execs format 9999999
col MBS_S format 99999
col MBS_R format 99999
col REDOMB format 99999
col PARSE format 999999
col Dsorts format 999999
col eqWaits format 999999
--- Set up title
set termout off serveroutput off heading off timing off
col title_name new_value title_name
select '['||to_char(sysdate,'HH24:MI DD-MON-RR')||' '||INSTANCE_NAME||'@'||HOST_NAME||' '||VERSION||'] ' title_name from v$instance;
set termout on serveroutput on heading on
TTitle Left '### DB System Activity Report (&&hours hrs) - ' title_name skip 2
select to_char(lag(si.SNAP_TIME) over (order by si.dbid, si.instance_number, si.snap_id),'DDMONRR HH24:MI') ||'-'|| to_char(si.SNAP_TIME,'HH24:MI') as SNAP_Times,
st.logons, st.Execs, st.Parse, st.CPU, st.eqWaits, st.DBgets, st.ConGets, st.PhyReads, st.ConChgs, st.PhyWrts, st.Dsorts,
round(st.Redo/(1024*1024)) REDOMB, round(st.BytesS/(1024*1024)) MBS_S, round(st.BytesR/(1024*1024)) MBS_R
from stats$snapshot si,
(select SNAP_ID,
case when logons < lag(logons,1,0) over (order by snap_id) then logons
when lag(logons,1,0) over (order by snap_id) = 0 then 0
else logons - lag(logons,1,0) over (order by snap_id) end as logons,
case when Execs < lag(Execs,1,0) over (order by snap_id) then Execs
when lag(Execs,1,0) over (order by snap_id) = 0 then 0
else Execs - lag(Execs,1,0) over (order by snap_id) end as Execs,
case when Parse < lag(Parse,1,0) over (order by snap_id) then Parse
when lag(Parse,1,0) over (order by snap_id) = 0 then 0
else Parse - lag(Parse,1,0) over (order by snap_id) end as Parse,
case when CPU < lag(CPU,1,0) over (order by snap_id) then CPU
when lag(CPU,1,0) over (order by snap_id) = 0 then 0
else CPU - lag(CPU,1,0) over (order by snap_id) end as CPU,
case when ConGets < lag(ConGets,1,0) over (order by snap_id) then ConGets
when lag(ConGets,1,0) over (order by snap_id) = 0 then 0
else ConGets - lag(ConGets,1,0) over (order by snap_id) end as ConGets,
case when PhyReads < lag(PhyReads,1,0) over (order by snap_id) then PhyReads
when lag(PhyReads,1,0) over (order by snap_id) = 0 then 0
else PhyReads - lag(PhyReads,1,0) over (order by snap_id) end as PhyReads,
case when PhyWrts < lag(PhyWrts,1,0) over (order by snap_id) then PhyWrts
when lag(PhyWrts,1,0) over (order by snap_id) = 0 then 0
else PhyWrts - lag(PhyWrts,1,0) over (order by snap_id) end as PhyWrts,
case when DBgets < lag(DBgets,1,0) over (order by snap_id) then DBgets
when lag(DBgets,1,0) over (order by snap_id) = 0 then 0
else DBgets - lag(DBgets,1,0) over (order by snap_id) end as DBgets,
case when eqWaits < lag(eqWaits,1,0) over (order by snap_id) then eqWaits
when lag(eqWaits,1,0) over (order by snap_id) = 0 then 0
else eqWaits - lag(eqWaits,1,0) over (order by snap_id) end as eqWaits,
case when ConChgs < lag(ConChgs,1,0) over (order by snap_id) then ConChgs
when lag(ConChgs,1,0) over (order by snap_id) = 0 then 0
else ConChgs - lag(ConChgs,1,0) over (order by snap_id) end as ConChgs,
case when dsorts < lag(dsorts,1,0) over (order by snap_id) then dsorts
when lag(dsorts,1,0) over (order by snap_id) = 0 then 0
else dsorts - lag(dsorts,1,0) over (order by snap_id) end as dsorts,
case when Redo < lag(Redo,1,0) over (order by snap_id) then Redo
when lag(Redo,1,0) over (order by snap_id) = 0 then 0
else Redo - lag(Redo,1,0) over (order by snap_id) end as Redo,
case when BytesS < lag(BytesS,1,0) over (order by snap_id) then BytesS
when lag(BytesS,1,0) over (order by snap_id) = 0 then 0
else BytesS - lag(BytesS,1,0) over (order by snap_id) end as BytesS,
case when BytesR < lag(BytesR,1,0) over (order by snap_id) then BytesR
when lag(BytesR,1,0) over (order by snap_id) = 0 then 0
else BytesR - lag(BytesR,1,0) over (order by snap_id) end as BytesR
from
(select SNAP_ID,
max(decode(NAME ,'logons cumulative', value )) logons,
max(decode(NAME ,'CPU used by this session', value )) cpu,
max(decode(NAME ,'consistent gets', value )) ConGets,
max(decode(NAME ,'physical reads', value )) PhyReads,
max(decode(NAME ,'physical writes', value )) PhyWrts,
max(decode(NAME ,'parse count (total)', value )) Parse,
max(decode(NAME ,'execute count', value )) Execs,
max(decode(NAME ,'enqueue waits', value )) eqWaits,
max(decode(NAME ,'db block gets', value )) DBgets,
max(decode(NAME ,'consistent changes', value )) ConChgs,
max(decode(NAME ,'sorts (disk)', value )) dsorts,
max(decode(NAME ,'redo size', value )) Redo,
max(decode(NAME ,'bytes sent via SQL*Net to client', value )) BytesS,
max(decode(NAME ,'bytes received via SQL*Net from client', value )) BytesR
from stats$sysstat
where NAME in ('logons cumulative','CPU used by this session','enqueue waits','db block gets','consistent gets','physical reads',
'consistent changes','physical writes','parse count (total)','execute count','redo size',
'bytes sent via SQL*Net to client','bytes received via SQL*Net from client','sorts (disk)')
group by SNAP_ID)) st
where si.SNAP_ID = st.SNAP_ID
and si.SNAP_TIME > sysdate - &&hours/24
order by si.SNAP_TIME;
TTitle off
logs.sql
--- logs.sql Log switch summary set pages 1000 lines 160 COLUMN DAY FORMAT a5 COLUMN H00 FORMAT 99 HEADING '00' COLUMN H01 FORMAT 99 HEADING '01' COLUMN H02 FORMAT 99 HEADING '02' COLUMN H03 FORMAT 99 HEADING '03' COLUMN H04 FORMAT 99 HEADING '04' COLUMN H05 FORMAT 99 HEADING '05' COLUMN H06 FORMAT 99 HEADING '06' COLUMN H07 FORMAT 99 HEADING '07' COLUMN H08 FORMAT 99 HEADING '08' COLUMN H09 FORMAT 99 HEADING '09' COLUMN H10 FORMAT 99 HEADING '10' COLUMN H11 FORMAT 99 HEADING '11' COLUMN H12 FORMAT 99 HEADING '12' COLUMN H13 FORMAT 99 HEADING '13' COLUMN H14 FORMAT 99 HEADING '14' COLUMN H15 FORMAT 99 HEADING '15' COLUMN H16 FORMAT 99 HEADING '16' COLUMN H17 FORMAT 99 HEADING '17' COLUMN H18 FORMAT 99 HEADING '18' COLUMN H19 FORMAT 99 HEADING '19' COLUMN H20 FORMAT 99 HEADING '20' COLUMN H21 FORMAT 99 HEADING '21' COLUMN H22 FORMAT 99 HEADING '22' COLUMN H23 FORMAT 99 HEADING '23' COLUMN TOTAL FORMAT 999 HEADING 'Total' set heading off timing off select '' from dual; select '['||sysdate||']['||VERSION||'] '||INSTANCE_NAME||' - '||HOST_NAME||' ---------------------------------------------------------------------------- ' from v$instance; set heading on SELECT SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH:MI:SS'),1,5) DAY , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'00',1,0)) H00 , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'01',1,0)) H01 , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'02',1,0)) H02 , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'03',1,0)) H03 , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'04',1,0)) H04 , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'05',1,0)) H05 , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'06',1,0)) H06 , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'07',1,0)) H07 , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'08',1,0)) H08 , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'09',1,0)) H09 , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'10',1,0)) H10 , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'11',1,0)) H11 , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'12',1,0)) H12 , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'13',1,0)) H13 , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'14',1,0)) H14 , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'15',1,0)) H15 , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'16',1,0)) H16 , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'17',1,0)) H17 , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'18',1,0)) H18 , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'19',1,0)) H19 , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'20',1,0)) H20 , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'21',1,0)) H21 , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'22',1,0)) H22 , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'23',1,0)) H23 , COUNT(*) TOTAL FROM v$log_history a WHERE (TO_DATE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH:MI:SS'), 1,8), 'MM/DD/RR') >= trunc(sysdate -20)) AND (TO_DATE(substr(TO_CHAR(first_time, 'MM/DD/RR HH:MI:SS'), 1,8), 'MM/DD/RR') <= trunc(sysdate)) GROUP BY SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH:MI:SS'),1,5) order by SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH:MI:SS'),1,5);
sched.sql
set pages 1000 lines 160 col OWNER format a12 col JOB_NAME format a24 col JOB_ACTION format a42 col JOB_MODE format a20 col STATE format a9 col START_DATE format a15 col LAST_START_DATE format a15 col NEXT_RUN_DATE format a15 col LOG_DATE format a15 col Run format 9999 col fails format 9999 set termout off serveroutput off heading off timing off col title_name new_value title_name select '['||to_char(sysdate,'HH24:MI DD-MON-RR')||' '||INSTANCE_NAME||'@'||HOST_NAME||' '||VERSION||'] ' title_name from v$instance; @date ALTER SESSION SET nls_timestamp_tz_format = 'HH24:MI DDMONRR'; ALTER SESSION SET nls_timestamp_format = 'HH24:MI DDMONRR'; set termout on serveroutput on heading on TTitle Left '### Check V$DATAPUMP_JOB - ' title_name skip 2 select LOG_ID,LOG_DATE,OWNER,JOB_NAME,OPERATION,STATUS from DBA_SCHEDULER_JOB_LOG where LOG_DATE > sysdate -7; TTitle Left '### Check DBA_SCHEDULER_JOBS - ' title_name skip 2 select OWNER,JOB_NAME,JOB_ACTION,START_DATE,STATE,RUN_COUNT Run,FAILURE_COUNT Fails,LAST_START_DATE,NEXT_RUN_DATE from DBA_SCHEDULER_JOBS; TTitle Left '### Check v$SCHEDULER_RUNNING_JOBS - ' title_name skip 2 select * from v$SCHEDULER_RUNNING_JOBS; TTitle off
sptopsql.sql
--- topsql.sql list top SQL statements ordered by Elapsed time over the last 4 hours
---
col SNAPIDS_TIME format a24
col EXECS format 9999
col RK format 99
col S_MINS format 9999
col TEXT_SUBSET a30
col CPU_SECS format 999999.99
col ELAPS_SECS format 999999.99
col AVG_ELAPS format 99999.99
break on SNAPIDS_TIME skip 1
--- Set up title
set termout off serveroutput off heading off timing off
col title_name new_value title_name
select '['||to_char(sysdate,'HH24:MI DD-MON-RR')||' '||INSTANCE_NAME||'@'||HOST_NAME||' '||VERSION||'] ' title_name from v$instance;
set termout on serveroutput on heading on
TTitle Left '### Top 10 SQL statements ordered by Elapsed time in last &&hours hours - ' title_name skip 2
--- Top 10 SQL statements ordered by Elapsed
select ssnapid||':'||esnapid||' '||to_char(etime,'HH24:MI DDMON') SNAPIDS_TIME, round((etime-stime)*24*60) S_MINS,
time_rank Rk, HASH_VALUE,EXECS,DISK_RDS,BUF_GETS,ROWSP,
round(CPU_TIME/1000000,2)CPU_SECS ,round(ELAPS_TIME/1000000,2) ELAPS_SECS, round((ELAPS_TIME/EXECS)/1000000,2) AVG_ELAPS, TEXT_SUBSET
from (select ssnapid,esnapid,stime,etime,HASH_VALUE,EXECS,DISK_RDS,BUF_GETS,ROWSP,CPU_TIME,ELAPS_TIME,TEXT_SUBSET,
dense_rank() over (partition by esnapid order by ELAPS_TIME desc) time_rank
from (select ssnapid,esnapid,stime,etime, HASH_VALUE, TEXT_SUBSET,
case when EXECUTIONS < nvl(lag(EXECUTIONS) over (partition by hash_value order by sq.snap_id),0) then EXECUTIONS
else EXECUTIONS - lag(EXECUTIONS) over (partition by hash_value order by sq.snap_id) END EXECS,
case when DISK_READS < nvl(lag(DISK_READS) over (partition by hash_value order by sq.snap_id),0) then DISK_READS
else DISK_READS - lag(DISK_READS) over (partition by hash_value order by sq.snap_id) END DISK_RDS,
case when BUFFER_GETS < nvl(lag(BUFFER_GETS) over (partition by hash_value order by sq.snap_id),0) then BUFFER_GETS
else BUFFER_GETS - lag(BUFFER_GETS) over (partition by hash_value order by sq.snap_id) END BUF_GETS,
case when ROWS_PROCESSED < nvl(lag(ROWS_PROCESSED) over (partition by hash_value order by sq.snap_id),0) then ROWS_PROCESSED
else ROWS_PROCESSED - lag(ROWS_PROCESSED) over (partition by hash_value order by sq.snap_id) END ROWSP,
case when CPU_TIME < nvl(lag(CPU_TIME) over (partition by hash_value order by sq.snap_id),0) then CPU_TIME
else CPU_TIME - lag(CPU_TIME) over (partition by hash_value order by sq.snap_id) END CPU_TIME,
case when ELAPSED_TIME < nvl(lag(ELAPSED_TIME) over (partition by hash_value order by sq.snap_id),0) then ELAPSED_TIME
else ELAPSED_TIME - lag(ELAPSED_TIME) over (partition by hash_value order by sq.snap_id) END ELAPS_TIME
from STATS$SQL_SUMMARY sq,
(SELECT iv.start_snap_id as ssnapid, iv.end_snap_id as esnapid, iv.start_snap_time as stime, iv.end_snap_time as etime
FROM (SELECT lag(dbid) over (order by dbid, instance_number, snap_id) AS start_dbid, dbid AS end_dbid,
lag(snap_id) over (order by dbid, instance_number, snap_id) AS start_snap_id, snap_id AS end_snap_id,
lag(instance_number) over (order by dbid, instance_number, snap_id) AS start_inst_nr, instance_number AS end_inst_nr,
lag(snap_time) over (order by dbid, instance_number, snap_id) AS start_snap_time, snap_time AS end_snap_time,
lag(startup_time) over (order by dbid, instance_number, snap_id) AS start_startup_time, startup_time AS end_startup_time
FROM perfstat.stats$snapshot
where SNAP_TIME > sysdate - &&hours/24
) iv
WHERE iv.start_snap_id IS NOT NULL
AND iv.start_dbid=iv.end_dbid
AND iv.start_inst_nr=iv.end_inst_nr
AND iv.start_startup_time=iv.end_startup_time) i
where i.esnapid = sq.SNAP_ID
)
Where EXECS is not null and EXECS >0
)
where time_rank < 11
order by etime, time_rank;
TTitle off
lock2.sql
select l.sid, l.type,
decode(l.type,
'BL','Buffer hash table instance lock',
'CF',' Control file schema global enqueue lock',
'CI','Cross-instance function invocation instance lock',
'CS','Control file schema global enqueue lock',
'CU','Cursor bind lock',
'DF','Data file instance lock',
'DL','Direct loader parallel index create',
'DM','Mount/startup db primary/secondary instance lock',
'DR','Distributed recovery process lock',
'DX','Distributed transaction entry lock',
'FI','SGA open-file information lock',
'FS','File set lock',
'HW','Space management operations on a specific segment lock',
'IN','Instance number lock',
'IR','Instance recovery serialization global enqueue lock',
'IS','Instance state lock',
'IV','Library cache invalidation instance lock',
'JQ','Job queue lock',
'KK','Thread kick lock',
'MB','Master buffer hash table instance lock',
'MM','Mount definition gloabal enqueue lock',
'MR','Media recovery lock',
'PF','Password file lock',
'PI','Parallel operation lock',
'PR','Process startup lock',
'PS','Parallel operation lock',
'RE','USE_ROW_ENQUEUE enforcement lock',
'RT','Redo thread global enqueue lock',
'RW','Row wait enqueue lock',
'SC','System commit number instance lock',
'SH','System commit number high water mark enqueue lock',
'SM','SMON lock',
'SN','Sequence number instance lock',
'SQ','Sequence number enqueue lock',
'SS','Sort segment lock',
'ST','Space transaction enqueue lock',
'SV','Sequence number value lock',
'TA','Generic enqueue lock',
'TD','DDL enqueue lock',
'TE','Extend-segment enqueue lock',
'TM','DML enqueue lock',
'TO','Temporary Table Object Enqueue',
'TT','Temporary table enqueue lock',
'TX','Transaction enqueue lock',
'UL','User supplied lock',
'UN','User name lock',
'US','Undo segment DDL lock',
'WL','Being-written redo log instance lock',
'WS','Write-atomic-log-switch global enqueue lock',
'TS',decode(l.id2,0,'Temporary segment enqueue lock (ID2=0)',
'New block allocation enqueue lock (ID2=1)'),
'LA','Library cache lock instance lock (A=namespace)',
'LB','Library cache lock instance lock (B=namespace)',
'LC','Library cache lock instance lock (C=namespace)',
'LD','Library cache lock instance lock (D=namespace)',
'LE','Library cache lock instance lock (E=namespace)',
'LF','Library cache lock instance lock (F=namespace)',
'LG','Library cache lock instance lock (G=namespace)',
'LH','Library cache lock instance lock (H=namespace)',
'LI','Library cache lock instance lock (I=namespace)',
'LJ','Library cache lock instance lock (J=namespace)',
'LK','Library cache lock instance lock (K=namespace)',
'LL','Library cache lock instance lock (L=namespace)',
'LM','Library cache lock instance lock (M=namespace)',
'LN','Library cache lock instance lock (N=namespace)',
'LO','Library cache lock instance lock (O=namespace)',
'LP','Library cache lock instance lock (P=namespace)',
'LS','Log start/log switch enqueue lock',
'PA','Library cache pin instance lock (A=namespace)',
'PB','Library cache pin instance lock (B=namespace)',
'PC','Library cache pin instance lock (C=namespace)',
'PD','Library cache pin instance lock (D=namespace)',
'PE','Library cache pin instance lock (E=namespace)',
'PF','Library cache pin instance lock (F=namespace)',
'PG','Library cache pin instance lock (G=namespace)',
'PH','Library cache pin instance lock (H=namespace)',
'PI','Library cache pin instance lock (I=namespace)',
'PJ','Library cache pin instance lock (J=namespace)',
'PL','Library cache pin instance lock (K=namespace)',
'PK','Library cache pin instance lock (L=namespace)',
'PM','Library cache pin instance lock (M=namespace)',
'PN','Library cache pin instance lock (N=namespace)',
'PO','Library cache pin instance lock (O=namespace)',
'PP','Library cache pin instance lock (P=namespace)',
'PQ','Library cache pin instance lock (Q=namespace)',
'PR','Library cache pin instance lock (R=namespace)',
'PS','Library cache pin instance lock (S=namespace)',
'PT','Library cache pin instance lock (T=namespace)',
'PU','Library cache pin instance lock (U=namespace)',
'PV','Library cache pin instance lock (V=namespace)',
'PW','Library cache pin instance lock (W=namespace)',
'PX','Library cache pin instance lock (X=namespace)',
'PY','Library cache pin instance lock (Y=namespace)',
'PZ','Library cache pin instance lock (Z=namespace)',
'QA','Row cache instance lock (A=cache)',
'QB','Row cache instance lock (B=cache)',
'QC','Row cache instance lock (C=cache)',
'QD','Row cache instance lock (D=cache)',
'QE','Row cache instance lock (E=cache)',
'QF','Row cache instance lock (F=cache)',
'QG','Row cache instance lock (G=cache)',
'QH','Row cache instance lock (H=cache)',
'QI','Row cache instance lock (I=cache)',
'QJ','Row cache instance lock (J=cache)',
'QL','Row cache instance lock (K=cache)',
'QK','Row cache instance lock (L=cache)',
'QM','Row cache instance lock (M=cache)',
'QN','Row cache instance lock (N=cache)',
'QO','Row cache instance lock (O=cache)',
'QP','Row cache instance lock (P=cache)',
'QQ','Row cache instance lock (Q=cache)',
'QR','Row cache instance lock (R=cache)',
'QS','Row cache instance lock (S=cache)',
'QT','Row cache instance lock (T=cache)',
'QU','Row cache instance lock (U=cache)',
'QV','Row cache instance lock (V=cache)',
'QW','Row cache instance lock (W=cache)',
'QX','Row cache instance lock (X=cache)',
'QY','Row cache instance lock (Y=cache)',
'QZ','Row cache instance lock (Z=cache)','????') Lockt
from v$lock l
/
longops.sql
---longops.sql listing of v$SESSION_LONGOPS set pages 1000 lines 160 col SID format 9999 col USERNAME format a12 col SERIAL# format 999999 col OSUSER format a12 col PROGRAM format a30 col MACHINE format a20 col LOGON_TIME format a15 col command format 999 col CMD format 999 col LAST_UPDATE format a12 col OPNAME format a20 col TARGET format a30 col UNITS format a12 col SOFAR format 999999 col REMAIN format 999999 col ELAPS format 999999 col HASH_VALUE format 9999999999 set termout off serveroutput off heading off timing off col title_name new_value title_name select '['||to_char(sysdate,'HH24:MI DD-MON-RR')||' '||INSTANCE_NAME||'@'||HOST_NAME||' '||VERSION||'] ' title_name from v$instance; set termout on serveroutput on heading on TTitle Left '### v$SESSION_LONGOPS Details ordered by LAST_UPDATE_TIME - ' title_name skip 2 select SID,OPNAME,TARGET,SQL_HASH_VALUE HASH_VALUE, SOFAR,TOTALWORK,UNITS,START_TIME,LAST_UPDATE_TIME,TIME_REMAINING remain,ELAPSED_SECONDS ELAPs from v$SESSION_LONGOPS order by LAST_UPDATE_TIME; TTitle Left '### v$SESSION_LONGOPS summary ordered by HASH_VALUE - ' title_name skip 2 select SQL_HASH_VALUE HASH_VALUE, to_char(LAST_UPDATE_TIME,'DD-MON-RR') LAST_UPDATE, OPNAME,TARGET, min(ELAPSED_SECONDS) MIN_ELAPS, max(ELAPSED_SECONDS) MAX_ELAPS, round(avg(ELAPSED_SECONDS))AVG_ELAPS, count(*) from v$SESSION_LONGOPS group by SQL_HASH_VALUE, to_char(LAST_UPDATE_TIME,'DD-MON-RR'),OPNAME,TARGET order by LAST_UPDATE,HASH_VALUE; TTitle off
privs.sql
--- privs.sql list of privileges for user or role set pages 1000 lines 160 col grantee format a20 col privilege format a30 col OWNER format a20 col lvl format a12 col table_name format a20 col column_name format a20 set termout off serveroutput off heading off timing off col title_name new_value title_name select '['||to_char(sysdate,'HH24:MI DD-MON-RR')||' '||INSTANCE_NAME||'@'||HOST_NAME||' '||VERSION||'] ' title_name from v$instance; set termout on serveroutput on heading on TTitle Left '### Users directly granted privs from: dba_col_privs, dba_role_privs, dba_sys_privs, dba_tab_privs - ' title_name skip 2 --- users from dba_users select grantee,lvl,privilege,grantable,owner,table_name,column_name from ( select grantee,'Column' lvl,privilege,grantable,owner,table_name,column_name from dba_col_privs union select grantee,'Role' GrType,granted_role obj,admin_option a, null,null,null from dba_role_privs union select grantee,'Sys Priv',privilege,admin_option,null,null,null from dba_sys_privs union select grantee,'table',privilege,grantable,owner,table_name,null from dba_tab_privs where privilege !='EXECUTE' union select grantee,'Program', privilege,grantable,owner,table_name,null from dba_tab_privs where privilege ='EXECUTE' ) where grantee like '&GRANTEE%%'; TTitle off
schema.sql
--- users.sql list of users from dba_users set pages 1000 lines 160 col OWNER format a20 col SEGMENT_TYPE format a20 col TABLESPACE_NAME format a30 col TYPE format a8 col NAME format a34 col TABLESPACE format a20 col BLOCKS format 999999999 col MBS format 99999 set termout off serveroutput off heading off timing off col title_name new_value title_name select '['||to_char(sysdate,'HH24:MI DD-MON-RR')||' '||INSTANCE_NAME||'@'||HOST_NAME||' '||VERSION||'] ' title_name from v$instance; set termout on serveroutput on heading on TTitle Left '### Schema Summary - ' title_name skip 2 select OWNER, SEGMENT_TYPE, TABLESPACE_NAME , round(sum(BYTES)/(1024*1024),2) USED_MB, count(*) NUM_OBJECTS from dba_segments group by OWNER, SEGMENT_TYPE, TABLESPACE_NAME order by OWNER, SEGMENT_TYPE, TABLESPACE_NAME; TTitle Left '### Schema Detail for &&SCHEMA - ' title_name skip 2 select decode(TYPE, 'TABLE','TABLE', 'INDEX','- INDEX',TYPE )TYPE, decode(TYPE, 'TABLE', TABOWNER, 'INDEX', '- '||INDOWNER,INDOWNER) OWNER, decode(TYPE, 'TABLE', TABNAME, 'INDEX', '- '||INDNAME,INDNAME) NAME, TABLESPACE_NAME,NUM_ROWS, DISTINCT_KEYS DISKEYS,BLOCKS , MBS,LAST_ANALYZED from (select 'A' DUMMY, s.SEGMENT_TYPE TYPE, t.OWNER TABOWNER,t.TABLE_NAME TABNAME,t.TABLESPACE_NAME , null INDOWNER, null INDNAME, t.NUM_ROWS,null DISTINCT_KEYS ,t.BLOCKS , round(s.BYTES/(1024*1024)) MBS,t.LAST_ANALYZED from dba_tables t, dba_segments s where t.OWNER='&&SCHEMA' and t.OWNER = s.OWNER and t.TABLE_NAME = s.SEGMENT_NAME and s.SEGMENT_TYPE like '%TABLE%' union select 'B' DUMMY,s.SEGMENT_TYPE TYPE, TABLE_OWNER as TABOWNER, TABLE_NAME TABNAME, i.TABLESPACE_NAME, i.OWNER INDOWNER, i.INDEX_NAME INDNAME, i.NUM_ROWS,i.DISTINCT_KEYS,s.BLOCKS, round(s.BYTES/(1024*1024)) MBS,i.LAST_ANALYZED from dba_indexes i, dba_segments s where i.OWNER='&&SCHEMA' and i.OWNER = s.OWNER and i.INDEX_NAME = s.SEGMENT_NAME and s.SEGMENT_TYPE like '%INDEX%') order by TABOWNER,TABNAME, DUMMY,INDNAME; TTitle off