asmdisk_info.sql
-- V$ASM_DISK
-- Lists each disk discovered by the ASM instance, including disks
-- that are not part of any ASM disk group
SET LINESIZE 179
SET PAGESIZE 60
TTITLE 'ASM Disks - General Information (From V$ASM_DISK)'
COL group_number FORMAT 99999 HEADING 'ASM|Disk|Grp #'
COL disk_number FORMAT 99999 HEADING 'ASM|Disk|#'
COL name FORMAT A32 HEADING 'ASM Disk Name' WRAP
COL total_mb FORMAT 99999999 HEADING 'Total|Disk|Space(MB)'
COL compound_index FORMAT 99999999999 HEADING 'Compound|Idx #'
COL incarnation FORMAT 99999999999 HEADING 'Incarnation #' WRAP
COL mount_status FORMAT A07 HEADING 'Mount|Status'
COL header_status FORMAT A12 HEADING 'Header|Status'
COL mode_status FORMAT A08 HEADING 'Mode|Status'
COL state FORMAT A08 HEADING 'Disk|State'
COL redundancy FORMAT A07 HEADING 'Redun-|dancy'
COL path FORMAT A32 HEADING 'OS Disk Path Name' WRAP
SELECT
group_number
,disk_number
,name
,total_mb
,compound_index
,incarnation
,mount_status
,header_status
,mode_status
,state
,redundancy
,path
FROM
v$asm_disk
ORDER BY
name
,group_number
,disk_number
;
TTITLE OFF
---------------------------------------------------------
waits.sql
col username format a15
col osuser format a10
col event format a30
break on event
col p1text format a13
col p2text format a13
set linesize 135
set pages 66
select event,p1text,p1,p2text,p2,b.sid,process,b.username, b.osuser
from v$session_wait a,v$session b
where event not in ('client message',
'Null event',
'pipe get',
'rdbms ipc message',
'SQL*Net message from client',
'pmon timer',
'smon timer',
'slave wait')
and b.username not like 'ORACLE'
and a.sid=b.sid
/
------------------------------------------------------------
waitinfo.sql
set verify off
set pages 100
col event format a40
break on sid
set lines 132
SELECT SID,EVENT, TOTAL_WAITS,TIME_WAITED/100 "Time waited in Secs" ,AVERAGE_WAIT*10 "AVG wait in ms"
FROM v$session_event
WHERE sid=&SID
ORDER BY TIME_WAITED desc,TOTAL_WAITS desc
/
-----------------------------------------------------------
userprivs.sql
REM
REM This script lists a user's roles and privileges (explicit and via roles).
REM
prompt
select privilege, grantee, admin_option from dba_sys_privs
where grantee = upper('&&1')
or grantee in
(select granted_role from dba_role_privs where grantee = upper('&&1'))
order by privilege
/
select granted_role, grantee, admin_option from dba_role_privs
where grantee = upper('&&1')
or grantee in
(select granted_role from dba_role_privs where grantee = upper('&&1'))
order by granted_role
/
prompt
undefine 1
---------------------------------------------
user_check.sql
set echo off
set verify off
set pages 100 lines 132
set linesize 300
col owner for a12
col user_name for a20
accept USER_NAME char prompt 'User_Name:'
select username,account_status,to_char(lock_date,('DDMONYY-HH24:MI')) as lock_date,to_char(expiry_date,('DDMONYY-HH24:MI')) as EXPDATE,profile
from dba_users where username='&USER_NAME' or username like '%&USER_NAME%' order by username;
--decode('&USER_NAME','',user_name,upper('&USER_NAME))
Prompt Checking the roles granted to the user
select grantee as username,granted_role as role from dba_role_privs where grantee='&USER_NAME' or grantee like '%&USER_NAME%' order by username;
undefine USER_NAME
----------------------------------------------------
undostat.sql
set lines 150
set pages 50
col MAXQLEN format 9999999
col MAXCONC format 99999
col UNXPSTLCNT format 9999999
col UNRELCNT format 9999999
col UNREUCNT format 9999999
col EXSTLCNT format 9999999
col EXRELCNT format 9999999
col expreucnt format 9999999
col SSOLDERR format 99999
col NOSPCEERR format 99999
col tuneundoret format 9999999
col "UNDO MB" format 9999999
col BEGIN_TIME format a20
select
to_char(BEGIN_TIME,'DD-MON-YYYY HH24:MI:SS') BEGIN_TIME
,round(UNDOBLKS*8/1024,0) as "UNDO MB"
,MAXQUERYLEN MAXQLEN
,MAXCONCURRENCY MAXCONC
,UNXPSTEALCNT UNXPSTLCNT
,UNXPBLKRELCNT UNRELCNT
,UNXPBLKREUCNT UNREUCNT
,EXPSTEALCNT EXSTLCNT
,EXPBLKRELCNT EXRELCNT
,EXPBLKREUCNT expreucnt
,SSOLDERRCNT SSOLDERR
,NOSPACEERRCNT NOSPCEERR
,TUNED_UNDORETENTION tuneundoret
from v$undostat
where trunc(BEGIN_TIME)>trunc(sysdate-1) order by BEGIN_TIME;
--------------------------------------------
undo_usage_sid.sql
set pages 200
set lines 120
set verify off
column username format a10
column sid format 99999
column module format a25
column serial format 99999
column name format a15 HEAD "Undo|Segment"
column "Megs Used" format 999,999,999.99
column "Recs upd/del" format 999999999
col tablespace_name format a8 HEAD "Tablespc|Name"
PROMPT;
ACCEPT inp_sess PROMPT 'Enter the desired SESSION ID or <return>: ';
select
s.username
, s.sid
, s.serial# serial
, substr(s.module,1,25) module
, r.name
, z.tablespace_name
, a.used_ublk*b.value/1024/1024 "MB Used"
, a.used_urec "Recs upd/del"
from
v$lock l
, v$process p
, v$rollname r
, v$session s
, dba_rollback_segs z
, v$transaction a
, ( select value
from v$parameter
where name = 'db_block_size' ) b
where
l.sid = p.pid(+)
and a.ses_addr = s.SADDR
and s.sid = l.sid
and l.sid LIKE DECODE('&&inp_sess','', l.sid,'&&inp_sess')
and r.name = z.segment_name
and l.type(+) = 'TX'
and trunc(l.id1(+)/65536) = r.usn
and l.lmode in (6,3)
--and a.used_ublk>10000
order by
r.name;
undefine inp_sess;
------------------------------------------------------
undo_usage.sql
set pages 200
set lines 180
break on report
compute sum of "MB Used" on report
column username format a12
column osuser format a12
column module format a20
column sid format 99999
column serial format 99999
col event format a20 trunc
column name format a25 HEAD "Undo|Segment"
column "Megs Used" format 999,999,999.99
column "Recs upd/del" format 999999999
col tablespace_name format a12 HEAD "TSName"
col logon_time format a25
select
s.inst_id
, s.username
, osuser
, s.sid
, s.serial# serial
, substr(s.module,1,20) module
, to_char(logon_time,'YYYYMMDD HH24:MI:SS') logon_time
, s.sql_id
, event
-- , r.name
, a.used_ublk*b.value/1024/1024 "MB Used"
, a.used_urec "Recs upd/del"
, z.tablespace_name
from
gv$lock l
, gv$process p
, v$rollname r
, gv$session s
, dba_rollback_segs z
, gv$transaction a
, ( select value
from v$parameter
where name = 'db_block_size' ) b
where
l.sid = p.pid(+)
and a.ses_addr = s.SADDR
and s.sid = l.sid
and s.inst_id=l.inst_id
and r.name = z.segment_name
and l.type(+) = 'TX'
and trunc(l.id1(+)/65536) = r.usn
and l.lmode in (6,3)
--and a.used_ublk>10000
order by 10;
--r.name;
--------------------------------------------------------------------
undo_status.sql
set lines 132
set pages 100
break on tablespace_name
col status format a15
col tablespace_name format a25
select du.tablespace_name,du.status,"BYTES(MB)","FREE(MB)","TOTAL(MB)" from
(select tablespace_name,status,round(sum(bytes)/(1024*1024),0) as "BYTES(MB)" from dba_undo_extents group by tablespace_name,status) du,
(select tablespace_name,round(sum(bytes)/1024/1024,0) as "TOTAL(MB)" from dba_data_files group by tablespace_name) df,
(select /*+ parallel (a,4) */ a.tablespace_name,round(sum(a.bytes)/1024/1024,0) as "FREE(MB)" from dba_free_space a where tablespace_name in (select distinct tablespace_name from dba_undo_extents) group by a.tablespace_name) dfr
where du.tablespace_name=df.tablespace_name(+)
and du.tablespace_name=dfr.tablespace_name(+)
order by du.tablespace_name,du.status
/
----------------------------------------------------------
undo_peak.sql
set lines 132
set verify off
set pages 100
PROMPT;
ACCEPT threshold PROMPT 'Enter the desired UNDO peak usage in GB/hr or <return>: ';
PROMPT;
select to_char(BEGIN_TIME,'YYYY-MON-DD HH24') as "YYYY-MON-DD HH24",round(sum(UNDOBLKS*8192)/(1024*1024*1024)) as "UNDO GB" from v$undostat where trunc(BEGIN_TIME)>=trunc(sysdate-7) group by to_char(BEGIN_TIME,'YYYY-MON-DD HH24') having round(sum(UNDOBLKS*8192)/(1024*1024*1024)) >= &&threshold order by 1
/
-------------------------------------------
top20redoa.sql
set lines 180
column machine format a15
column sid format 9999
col username format a15
col osuser format a12
col program format a15
set pages 100
col username format a15
rem Query V$SESS_IO. This view contains the column BLOCK_CHANGES which indicates
rem how much blocks have been changed by the session. High values indicate a
rem session generating lots of redo.
select * from (select a.sid,a.serial#,a.status,a.machine,substr(a.program,1,15) "program",a.process,to_char(a.logon_time,'yyyymmdd hh24:mi:ss') logon_time, a.username,a.osuser,b.BLOCK_GETS BGETS,b.BLOCK_CHANGES BCHANGES from v$session a ,v$sess_io b
where a.sid = b.sid and a.status = 'ACTIVE' order by b.BLOCK_CHANGES desc) where rownum < 21
/
rem Query V$TRANSACTION. This view contains information about the amount of
rem undo blocks and undo records accessed by the transaction (as found in the
rem USED_UBLK and USED_UREC columns).
rem SELECT s.sid, s.serial#, s.username, substr(s.program,1,3),t.used_ublk, t.used_urec
rem FROM v$session s, v$transaction t
rem WHERE s.taddr = t.addr
rem ORDER BY 5 , 6 , 1, 2, 3, 4;
------------------------------------------------------
top20redo.sql
set lines 162
column machine format a14 trunc
col username format a12
col osuser format a10
col program format a15 trunc
set pages 100
col username format a15
col serial# format 999999
col sid format 99999
rem Query V$SESS_IO. This view contains the column BLOCK_CHANGES which indicates
rem how much blocks have been changed by the session. High values indicate a
rem session generating lots of redo.
select * from (select a.sid,a.serial#,a.status,a.machine,substr(a.program,1,15) "program",a.process,to_char(a.logon_time,'yyyymmdd hh24:mi:ss') logon_time, a.username,a.osuser,b.BLOCK_GETS,b.BLOCK_CHANGES from v$session a ,v$sess_io b
where a.sid = b.sid order by b.BLOCK_CHANGES desc) where rownum < 21
/
rem Query V$TRANSACTION. This view contains information about the amount of
rem undo blocks and undo records accessed by the transaction (as found in the
rem USED_UBLK and USED_UREC columns).
rem SELECT s.sid, s.serial#, s.username, substr(s.program,1,3),t.used_ublk, t.used_urec
rem FROM v$session s, v$transaction t
rem WHERE s.taddr = t.addr
rem ORDER BY 5 , 6 , 1, 2, 3, 4;
-----------------------------------------------------------------
top20pioa.sql
set lines 180
set pages 100
col username format a12
col sid format 99999
col osuser format a12
col program format a15
col machine format a15
select * from (select a.sid,a.serial#,a.status,substr(a.machine,1,15) "machine",substr(a.program,1,15) "program",
--a.process,
to_char(a.logon_time,'yyyy/mm/dd hh24:mi:ss') "logon time", a.username,a.osuser,b.BLOCK_GETS,b.CONSISTENT_GETS,b.PHYSICAL_READS,b.BLOCK_CHANGES,b.CONSISTENT_CHANGES from v$session a ,v$sess_io b where a.sid = b.sid and a.status = 'ACTIVE' order by b.physical_reads desc) where rownum < 21;
-----------------------------------------------
top20pio.sql
set lines 132
set pages 100
col username format a12
col osuser format a12
col program format a15
col machine format a15
select * from (select a.sid,a.serial#,a.status,substr(a.machine,1,15) "machine",substr(a.program,1,15) "program",a.process,to_char(a.logon_time,'yyyy/mm/dd hh24:mi:ss') "logon time", a.username,a.osuser,b.BLOCK_GETS,b.CONSISTENT_GETS,b.PHYSICAL_READS,b.BLOCK_CHANGES,b.CONSISTENT_CHANGES from v$session a ,v$sess_io b where a.sid = b.sid order by b.physical_reads desc) where rownum < 21;
---------------------------------------------------------
top20mem.sql
set lines 170
set pages 100
col PROC_TYPE format a10
col osuser format a12
col serial# format 99999
col sid format 99999
col program format a24
col machine format a12
select * from (select a.sid,a.serial#,a.status,substr(a.machine,1,12) "machine",substr(a.program,1,24) "program",a.process,to_char(a.logon_time,'yyyy/mm/dd hh24:mi:ss') "logon time", nvl(a.username,'BACKGRD') PROC_TYPE ,a.osuser,b.PGA_ALLOC_MEM from v$session a , v$process b where a.paddr = b.addr
order by b.PGA_ALLOC_MEM desc) where rownum < 21;
---------------------------------------------------------
top20ioa.sql
set lines 200
set pages 60
col sid format 9999
col username format a12
col osuser format a12
col program format a15
col process format a10
col machine format a15
select * from (select a.sid,a.serial#,a.status,sql_id,
-- substr(a.machine,1,15) "machine",
substr(a.program,1,15) "program",
-- a.process
to_char(a.logon_time,'yyyy/mm/dd hh24:mi:ss') "logon time", a.username,a.osuser,b.BLOCK_GETS,b.CONSISTENT_GETS,b.PHYSICAL_READS,b.BLOCK_CHANGES,b.CONSISTENT_CHANGES from v$session a ,v$sess_io b
where a.sid = b.sid and a.status = 'ACTIVE' order by b.consistent_gets+b.block_gets desc) where rownum < 21;
---------------------------------------------------
top20io.sql
set lines 132
set pages 100
col username format a12
col osuser format a12
col program format a15
col machine format a15
select * from (select a.sid,a.serial#,a.status,substr(a.machine,1,15) "machine",substr(a.program,1,15) "program",a.process,to_char(a.logon_time,'yyyy/mm/dd hh24:mi:ss') "logon time",a.username,a.osuser,b.BLOCK_GETS,b.CONSISTENT_GETS,b.PHYSICAL_READS,b.BLOCK_CHANGES,b.CONSISTENT_CHANGES from v$session a ,v$sess_io b
where a.sid = b.sid order by b.consistent_gets+b.block_gets desc) where rownum < 21;
--------------------------------------------------
top20cpua.sql
set lines 200
set pages 100
col username format a12
col osuser format a15
col program format a15
col machine format a15
Select * from ( select a.sid,a.serial#,a.status,substr(a.machine,1,15) "machine",substr(a.program,1,15) "program",a.process,to_char(a.logon_time,'yyyy/mm/dd hh24:mi:ss') "logon time",a.username,a.osuser,b.value
from v$session a ,v$sesstat b where a.status = 'ACTIVE' and a.sid = b.sid and b.statistic# = 12 order by b.value desc) where rownum < 21;
---------------------------------------------
top20cpu.sql
set lines 132
set pages 100
col username format a12
col osuser format a12
col program format a15
col machine format a15
Select * from ( select a.sid,a.serial#,a.status,substr(a.machine,1,15),substr(a.program,1,15),a.process,to_char(a.logon_time,'yyyy/mm/dd hh24:mi:ss'),a.username,a.osuser,b.value
from v$session a ,v$sesstat b where a.sid = b.sid and b.statistic# = 12 order by b.value desc) where rownum < 21;
-----------------------------------------------------
top20.sql
--Top 20 CPU consuming ACTIVE sessions
Select * from ( select a.sid,a.serial#,a.status,a.machine,a.program,a.process,to_char(a.logon_time,'yyyy/mm/dd hh24:mi:ss'),a.username,a.osuser,b.value
from v$session a ,v$sesstat b where a.status = 'ACTIVE' and a.sid = b.sid and b.statistic# = 12 order by b.value desc) where rownum < 21
/
--Top 20 CPU consuming sessions
Select * from ( select a.sid,a.serial#,a.status,a.machine,a.program,a.process,to_char(a.logon_time,'yyyy/mm/dd hh24:mi:ss'),a.username,a.osuser,b.value
from v$session a ,v$sesstat b where a.sid = b.sid and b.statistic# = 12 order by b.value desc) where rownum < 21
/
--Top 20 I/O generating ACTIVE sessions
select * from (select a.sid,a.serial#,a.status,a.machine,a.program,a.process,to_char(a.logon_time,'yyyy/mm/dd hh24:mi:ss'),
a.username,a.osuser,b.BLOCK_GETS,b.CONSISTENT_GETS,b.PHYSICAL_READS,b.BLOCK_CHANGES,b.CONSISTENT_CHANGES from v$session a ,v$sess_io b
where a.sid = b.sid and a.status = 'ACTIVE' order by b.consistent_gets+b.block_gets desc) where rownum < 21
/
--Top 20 I/O generating sessions
select * from (select a.sid,a.serial#,a.status,a.machine,a.program,a.process,to_char(a.logon_time,'yyyy/mm/dd hh24:mi:ss'),
a.username,a.osuser,b.BLOCK_GETS,b.CONSISTENT_GETS,b.PHYSICAL_READS,b.BLOCK_CHANGES,b.CONSISTENT_CHANGES from v$session a ,v$sess_io b
where a.sid = b.sid order by b.consistent_gets+b.block_gets desc) where rownum < 21
/
--Top 20 Physical I/O generating ACTIVE sessions
select * from (select a.sid,a.serial#,a.status,a.machine,a.program,a.process,to_char(a.logon_time,'yyyy/mm/dd hh24:mi:ss'),
a.username,a.osuser,b.BLOCK_GETS,b.CONSISTENT_GETS,b.PHYSICAL_READS,b.BLOCK_CHANGES,b.CONSISTENT_CHANGES from v$session a ,v$sess_io b
where a.sid = b.sid and a.status = 'ACTIVE' order by b.physical_reads desc) where rownum < 21
/
--Top 20 Physical I/O generating sessions
select * from (select a.sid,a.serial#,a.status,a.machine,a.program,a.process,to_char(a.logon_time,'yyyy/mm/dd hh24:mi:ss'),
a.username,a.osuser,b.BLOCK_GETS,b.CONSISTENT_GETS,b.PHYSICAL_READS,b.BLOCK_CHANGES,b.CONSISTENT_CHANGES from v$session a ,v$sess_io b
where a.sid = b.sid order by b.physical_reads desc) where rownum < 21
/
--Top 20 memory consuming sessions
select * from (select a.sid,a.serial#,a.status,a.machine,a.program,a.process,to_char(a.logon_time,'yyyy/mm/dd hh24:mi:ss'),
a.username,a.osuser,b.PGA_ALLOC_MEM from v$session a , v$process b where a.paddr = b.addr
order by b.PGA_ALLOC_MEM desc) where rownum < 21
/
--Sessions using parallel slaves
select count(SERVER#),max(SERVER_SET),REQ_DEGREE,DEGREE,QCSID from v$px_session where SERVER# is not null
group by QCSID,REQ_DEGREE,DEGREE order by 1
/
--------------------------------------------------------------
tempspace.sql
set feed on
PROMPT "FROM v$temp_SPACE_header"
select tablespace_name,sum(BYTES_USED)/1024/1024/1024 ALLOCATED,sum(BYTES_FREE)/1024/1024/1024 FREE,sum(BYTES_USED+BYTES_FREE)/1024/1024/1024 TOTAL
from v$temp_SPACE_header group by tablespace_name;
rem
rem
PROMPT "FROM v$SORT_SEGMENT"
rem
select TABLESPACE_NAME,round((USED_BLOCKS*value)/1024/1024,2) USED_MB,round((FREE_BLOCKS*value)/1024/1024/1024,2) FREE_GB ,
round((TOTAL_BLOCKS*value)/1024/1024/1024,2) TOTAL_GB
from v$sort_segment, ( select value from v$parameter where name = 'db_block_size' ) b;
------------------------------------------------------------
temp_usage.sql
col SID_SERIAL for a20;
col MODULE for a20;
col TABLESPACE for a20;
set line 200 pages 999;
compute sum on SUM (T.blocks) * TBS.block_size / 1024 / 1024;
SELECT p.inst_id, S.sid || ',' || S.serial# sid_serial, S.username, S.osuser, P.spid, S.module,
P.program, SUM (T.blocks) * TBS.block_size / 1024 / 1024 mb_used, T.tablespace,
COUNT(*) statements
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 p.inst_id, S.sid, S.serial#, S.username, S.osuser, P.spid, S.module,
P.program, TBS.block_size, T.tablespace
ORDER BY sid_serial;
-------------------------------------------------------------
tablespace_report.sql
set verify off
set linesize 160
set pagesize 400
set pause off
set echo off
set feedback off
set tab off
COL Tablespace_name FORMAT A20 HEADING "TABLESPACE NAME"
COL ALLOC_MB FORMAT 999999999 HEADING "ALLOC_MB"
COL FREE_MB FORMAT 9999999 HEADING "FREE_MB"
COL USED_MB FORMAT 9999999 HEADING "USED_MB"
COL MAXBYTES_MB FORMAT 999999 HEADING "MAXBYTES_MB"
COL pct_used FORMAT 9999999 HEADING "% USED"
COL pct_free FORMAT 99.9 HEADING "% FREE"
COL pct_used_auto FORMAT 999 HEADING "%USED|wrt auto"
COL pct_free_auto FORMAT 999 HEADING "%FREE|wrt auto"
COL #DF FORMAT 990 HEADING "#DF"
COL Extent_management FORMAT A15 HEADING "EXT MGMT"
COL "%FREE" FORMAT 999
COL "%USED" FORMAT 999
COL INITL_MB FORMAT 9999
COL NEXT_MB FORMAT 9999
PROMPT;
ACCEPT tsname PROMPT 'Enter the desired TABLESPACE_NAME or <return>: ';
BREAK ON report SKIP 1
compute sum of ALLOC_MB FREE_MB on report
select tbsp_allocated.tablespace_name
, tbsp_allocated.alloc_mb "ALLOC_MB"
, nvl(tbsp_free.free_mb, 0) "FREE_MB"
, (tbsp_allocated.alloc_mb - nvl(tbsp_free.free_mb, 0)) "USED_MB"
, round(tbsp_free.max_bytes,0) "MAXBYTES_MB"
, decode(tbsp_allocated.alloc_mb,0,0,100-round((nvl(tbsp_free.free_mb, 0) / tbsp_allocated.alloc_mb) * 100, 0)) "%USED"
, 100-round((allow_mb-alloc_mb+nvl(tbsp_free.free_mb, 0))*100/allow_mb,0) pct_used_auto
, (round((nvl(tbsp_free.free_mb, 0)/tbsp_allocated.alloc_mb)*100,0)) "%FREE"
, round((allow_mb-alloc_mb+nvl(tbsp_free.free_mb, 0))*100/allow_mb,0) pct_free_auto
, tbsp_allocated.num_df "#DF"
, round(INITIAL_EXTENT/1024/1024,3) INITL_MB
, round(NEXT_EXTENT/1024/1024,3) NEXT_MB
, tbsp.em "EXT MGMT"
, tbsp.SEGMENT_SPACE_MANAGEMENT "SEG_SPACE_GMT"
, tbsp.ALLOCATION_TYPE "ALLOC_TYPE"
from
( select /*+ parallel(a,2) */ tablespace_name
, round((sum(bytes)/(1024*1024)),0) alloc_mb,round(sum(greatest(MAXBYTES,bytes))/(1024*1024),0) allow_mb
, count(*) num_df
from dba_data_files a
group by tablespace_name
union all
select /*+ parallel(b,2) */ tablespace_name
, round((sum(bytes)/(1024*1024)),0) alloc_mb,round(sum(greatest(MAXBYTES,bytes))/(1024*1024),0) allow_mb
, count(*) num_df
from dba_temp_files b
group by tablespace_name
) tbsp_allocated
, ( select /*+ parallel(c,2) */ tablespace_name
, round(nvl((sum(bytes)/(1024*1024)),0), 0) free_mb,nvl((max(bytes)/(1024*1024)),0) max_bytes
from dba_free_space c
group by tablespace_name
) tbsp_free
, ( select tablespace_name,INITIAL_EXTENT,NEXT_EXTENT,extent_management em,SEGMENT_SPACE_MANAGEMENT,ALLOCATION_TYPE from dba_tablespaces
) tbsp
where tbsp_allocated.tablespace_name = tbsp_free.tablespace_name (+)
and tbsp_allocated.tablespace_name = tbsp.tablespace_name
and tbsp.tablespace_name LIKE DECODE('&&tsname','', tbsp.tablespace_name,upper('&&tsname'))
order by 1
/
----------------------------------------------------------
table_size_check.sql
SELECT
owner,
table_name,
TRUNC(sum(bytes)/1024/1024) Meg,
ROUND( ratio_to_report( sum(bytes) ) over () * 100) Percent
FROM
(SELECT segment_name table_name, owner, bytes
FROM dba_segments
WHERE segment_type IN ('TABLE', 'TABLE PARTITION', 'TABLE SUBPARTITION')
UNION ALL
SELECT i.table_name, i.owner, s.bytes
FROM dba_indexes i, dba_segments s
WHERE s.segment_name = i.index_name
AND s.owner = i.owner
AND s.segment_type IN ('INDEX', 'INDEX PARTITION', 'INDEX SUBPARTITION')
UNION ALL
SELECT l.table_name, l.owner, s.bytes
FROM dba_lobs l, dba_segments s
WHERE s.segment_name = l.segment_name
AND s.owner = l.owner
AND s.segment_type IN ('LOBSEGMENT', 'LOB PARTITION')
UNION ALL
SELECT l.table_name, l.owner, s.bytes
FROM dba_lobs l, dba_segments s
WHERE s.segment_name = l.index_name
AND s.owner = l.owner
AND s.segment_type = 'LOBINDEX')
WHERE owner in UPPER('&owner')
GROUP BY table_name, owner
HAVING SUM(bytes)/1024/1024 > 10 /* Ignore really small tables */
ORDER BY SUM(bytes) desc
;
------------------------------------------------------
stats_check_table_partition.sql
set lines 200
set pages 3000
set feedback on
set verify off
column COLUMN_POSITION heading 'COLUMN|POSITION' format 999
column SUBPARTITION_COUNT heading 'SUB_PART|COUNT' format 9999
column PARTITION_POSITION heading 'POSITION' format 999999
column index_name format a28
column COLUMN_NAME format a25
column TABLESPACE_NAME format a15
column size_mb format 99,999
variable DSPLY_OBJ varchar2(1)
accept TNAME prompt 'Table Name : '
accept TOWNER prompt 'Owner Name : '
/* Select Table Stats */
Select table_name,owner,c.size_mb, a.partitioned ,a.last_analyzed,a.num_rows,a.global_stats,b.part_count
from dba_tables a, (select count(*) part_count from dba_tab_partitions where table_name='&TNAME' and table_owner='&TOWNER') b , (select sum(bytes)/(1024*1024) size_mb from dba_segments where segment_name='&TNAME' and owner='&TOWNER' ) c
where a.table_name='&TNAME' and owner='&TOWNER' ;
/* Partitions Stats */
accept DSPLY_OBJ prompt 'Press Y to see Table Partition stats : '
select p.PARTITION_POSITION,
p.partition_name,
p.SUBPARTITION_COUNT,
p.tablespace_name,
s.bytes/(1024*1024) size_MB,
p.LAST_ANALYZED,
p.NUM_ROWS,
p.GLOBAL_STATS
from dba_tab_partitions p,
dba_segments s
where p.table_name=upper('&TNAME') and p.Table_owner=upper('&TOWNER') AND p.table_name=s.segment_name and p.partition_name=s.partition_name AND p.table_owner=s.owner and upper('&DSPLY_OBJ')='Y'
order by partition_position;
/* Select index stats and index column details */
BREAK ON INDEX_NAME skip 1 on uniqueness skip 0 on size_mb skip 0 on partitioned skip 0 on part_count skip 0 on last_analyzed skip 0 on num_rows skip 0 on global_stats
select a.index_name,
a.uniqueness,
d.size_mb,
a.partitioned,
b.part_count,
a.last_analyzed,
a.num_rows,
a.global_stats,
c.column_name,
c.column_position
from dba_indexes a,
(select count(*) part_count,index_name from dba_ind_partitions where index_name in (select index_name from dba_indexes where
table_name='&TNAME' and owner='&TOWNER') and index_owner='&TOWNER' group by index_name )b,
(select INDEX_NAME,COLUMN_NAME,COLUMN_POSITION from dba_ind_columns where table_name='&TNAME' and TABLE_OWNER='&TOWNER' ) c,
(select s.segment_name,sum(s.bytes)/1024/1024 size_mb from dba_segments s,(select index_name from dba_indexes where
table_name='&TNAME' and owner='&TOWNER') i where s.segment_name=i.index_name group by s.segment_name) d
where a.table_name='&TNAME' and
owner='&TOWNER' and
a.index_name=b.index_name(+) and
a.index_name=c.index_name and
a.index_name=d.segment_name
order by a.index_name,a.uniqueness,a.partitioned,b.part_count,a.last_analyzed,a.num_rows,a.global_stats ;
clear breaks;
accept DSPLY_OBJ prompt 'Press Y to see Index Partition stats : '
BREAK on index_name skip 1
select p.index_name,
p.PARTITION_POSITION,
p.PARTITION_NAME,
p.SUBPARTITION_COUNT,
p.tablespace_name,
s.bytes/(1024*1024) size_MB,
p.LAST_ANALYZED,
p.NUM_ROWS,
p.GLOBAL_STATS
from dba_ind_partitions p,
dba_segments s
where index_name in (select index_name from dba_indexes where table_name='&TNAME' and owner='&TOWNER' and partitioned='YES')
and index_owner='&TOWNER' and p.index_name=s.segment_name and p.partition_name=s.partition_name and p.index_owner=s.owner and upper('&DSPLY_OBJ')='Y'
order by index_name,partition_position;
undefine TNAME;
undefine TOWNER;
undefine DSPLY_OBJ;
----------------------------------------------------------------
sqlid_capture.sql
SET PAGESIZE 10000
SET LINESIZE 10000
SET TRIMSPOOL ON
SET VERIFY OFF
COLUMN spool_file_name NEW_VALUE xspool_file_name noprint format a1 trunc
SELECT sys_context('USERENV', 'DB_NAME') || '_'
|| 'SQLID_'
|| TO_CHAR(SYSDATE, 'YYYYMMDD_HH24MISS') spool_file_name
FROM dual;
SPOOL h:\scripts\&&xspool_file_name..txt
ALTER SESSION SET NLS_TIMESTAMP_FORMAT = 'DD-MON-YYYY HH24:MI';
ACCEPT num_days PROMPT 'Number of days : '
col INST_ID for 999
col btime head "Begin|Time"
col etime head "End|Time"
col a format 999,999.99 head "ET(tot)|Seconds"
col (elapsed_time_delta*.000001)/(executions_delta) format 999,999.99999 head "ET/exec|Seconds"
col b format 999,999.99 head "CPU Time|Seconds"
col rows_processed_delta format 999,999,999 head "Rows|Processed"
col disk_reads_delta format 999,999,999 head "Disk_Reads"
col iowait_delta format 999,999,999,999 head "IO_Wait"
col ccwait_delta format 999,999,999,999 head "Concurrency|Wait"
col buffer_gets_delta format 999,999,999,999 head "Buffer|Gets"
col sorts_delta format 999,999,999 head "Sorts"
col executions_delta format 999,999,999 head "Executions"
col fetches_delta format 999,999,999 head "Fetches"
select a.instance_number inst_id, sql_id, a.PARSING_SCHEMA_NAME,to_char(begin_interval_time, 'MON-DD HH24:MI') btime,
to_char(end_interval_time, 'MON-DD HH24:MI') etime, plan_hash_value, optimizer_cost,
executions_delta, rows_processed_delta, fetches_delta,
(elapsed_time_delta*.000001)/(executions_delta),
elapsed_time_delta*.000001 a, cpu_time_delta*.000001 b,
disk_reads_delta, iowait_delta, ccwait_delta, buffer_gets_delta,
sorts_delta
from dba_hist_sqlstat a, dba_hist_snapshot b
where a.instance_number = DECODE('&&instance','', a.instance_number,'&&instance')
and a.snap_id = b.snap_id
and a.instance_number=b.instance_number
AND b.begin_interval_time >= sysdate - &num_days
--and sql_id IN ('&sql_id')
and a.executions_delta > 0
and a.PARSING_SCHEMA_NAME='DLS'
order by sql_id, a.snap_id;
/*
-------------------------
PROMPT Total_IO_Activity:
-------------------------
col e format 999,999,999,999,999 head "Disk_Reads"
col f format 999,999,999,999,999 head "IO_Wait"
--------------------------------------------------
SELECT sum(disk_reads_delta) e, sum(iowait_delta) f
FROM dba_hist_sqlstat a, dba_hist_snapshot b
WHERE a.snap_id = b.snap_id
AND b.begin_interval_time >= '&btime'
AND b.begin_interval_time <= '&etime'
ORDER BY 1 desc,2 desc;
-----------------------------
PROMPT IO_Activity_over_Time:
-----------------------------
col a format a18 head "Snap_Time"
col e format 999,999,999,999,999 head "Disk_Reads"
col f format 999,999,999,999,999 head "IO_Wait"
--------------------------------------------------
SELECT b.begin_interval_time a, sum(disk_reads_delta) e, sum(iowait_delta) f
FROM dba_hist_sqlstat a, dba_hist_snapshot b
WHERE a.snap_id = b.snap_id
AND b.begin_interval_time >= '&btime'
AND b.begin_interval_time <= '&etime'
GROUP BY b.begin_interval_time
ORDER BY b.begin_interval_time;
-----------------
PROMPT By_SQL_Id:
-----------------
set pagesize 5000
col sql_id head "Sql_Id"
col e format 999,999,999,999,999 head "Disk_Reads"
col f format 999,999,999,999,999 head "IO_Wait"
--------------------------------------------------
SELECT sql_id, sum(disk_reads_delta) e, sum(iowait_delta) f
FROM dba_hist_sqlstat a, dba_hist_snapshot b
WHERE a.snap_id = b.snap_id
AND b.begin_interval_time >= '&btime'
AND b.begin_interval_time <= '&etime'
AND disk_reads_delta > 0
AND iowait_delta > 0
GROUP BY sql_id
ORDER BY 2 desc,3;
------------------
PROMPT By_Segment:
------------------
col pr format 999,999,999 head "Physical_Reads"
col pw format 999,999,999 head "Physical_Writes"
col ts format 999,999,999 head "Table_Scans"
col iw format 999,999,999 head "Itl_Waits"
col rl format 999,999,999 head "Row_Locks"
col bbw format 999,999,999 head "Buffer_Busy"
col a format a55 head "Object_Name"
------------------------------------------------
SELECT a.object_type||':'||a.owner||'.'||a.object_name a,
sum(b.PHYSICAL_READS_DELTA) pr,
sum(b.PHYSICAL_WRITES_DELTA) pw,
sum(TABLE_SCANS_DELTA) ts,
sum(b.ITL_WAITS_DELTA) iw,
sum(ROW_LOCK_WAITS_DELTA) rl,
sum(BUFFER_BUSY_WAITS_DELTA) bbw
FROM DBA_HIST_SEG_STAT b, dba_objects a, dba_hist_snapshot c
WHERE b.snap_id = c.snap_id
AND c.begin_interval_time >= '&btime'
AND c.begin_interval_time <= '&etime'
AND b.obj# = a.object_id
AND b.PHYSICAL_READS_DELTA > 0
AND b.PHYSICAL_WRITES_DELTA > 0
GROUP BY a.object_type||':'||a.owner||'.'||a.object_name
ORDER BY sum(b.PHYSICAL_READS_DELTA) desc, sum(b.PHYSICAL_WRITES_DELTA);
---------------------
PROMPT By_Filesystem:
---------------------
set numformat 999,999,999,999,999
col a format a10 head "Filesystem"
col b format 999,999,999,999,999 head "Physical_Reads"
col c format 999,999,999,999,999 head "Physical_Writes"
col d format 999,999,999,999,999 head "Read_Time"
col e format 999,999,999,999,999 head "Write_Time"
col f format 999,999,999,999,999 head "Wait_Time"
---------------------------------
SELECT substr(filename,0,4) a, sum(phyrds) b, sum(phywrts) c, sum(readtim) d, sum(writetim) e, sum(wait_count) f
FROM DBA_HIST_FILESTATXS a, dba_hist_snapshot b
WHERE a.snap_id = b.snap_id
AND b.begin_interval_time >= '&btime'
AND b.begin_interval_time <= '&etime'
GROUP BY substr(filename,0,4)
ORDER BY sum(phyrds) desc, sum(phywrts);
---------------------------------
*/
UNDEFINE num_days
UNDEFINE Instance
SPOOL OFF
-----------------------------------------------------------------
sort_usage.sql
set feedback off
set lines 220
set pages 100
break on report
compute sum of megs_used on report
col username for a15
col inst_id format 999
col sid for 99999
col serial# for 999999
col logon_time for a22
col tablespace for a12
col extents for 99999
col event format a25 trunc
col megs_used for 999999
col module format a20
col machine format a10 trunc
col osuser format a12
PROMPT ;
PROMPT **** CHECK TEMP USAHE ****;
PROMPT ;
ACCEPT username CHAR PROMPT 'Enter desired USERNAME or <return>: ';
select
a.inst_id
, a.username
, osuser
, a.sid
, a.serial#
, status
, to_char(a.logon_time,'MMDDYYYY HH24:MI:SS') logon_time
, b.tablespace
, a.SQL_ID
, substr(a.module,1,15) module
, machine
-- , process
, seconds_in_wait wait_s
,event
-- , sum(b.extents) extents
, sum(b.blocks*c.value)/1024/1024 megs_used
from
gv$sort_usage b
, gv$session a
,( select value
from v$parameter
where name = 'db_block_size' ) c
where
b.inst_id=a.inst_id
and b.SESSION_ADDR = a.saddr
and a.username LIKE DECODE ( '&&username', '', a.username, UPPER('&&username'))
group by
a.inst_id
,a.username
, osuser
, a.sid
, a.serial#
, status
, to_char(a.logon_time,'MMDDYYYY HH24:MI:SS')
, b.tablespace
, a.SQL_ID
, substr(a.module,1,15)
,event
, machine
-- ,process
, seconds_in_wait
order by
a.inst_id
, a.username
, b.tablespace
, megs_used
;
-----------------------------------------------------------
shared_pool_dtl.sql
set lines 132
select * from v$sgastat where pool='shared pool'
order by 3
/
------------------------------------------
shared_pool_calc.sql
set numwidth 15
column sum_obj_size format 999,999,999
column sum_sql_size format 999,999,999
column sum_user_size format 999,999,999
column min_shared_pool format 999,999,999
select to_number(value) shared_pool_size,
sum_obj_size,
sum_sql_size,
sum_user_size,
(sum_obj_size + sum_sql_size+sum_user_size)* 1.3 min_shared_pool
from (select sum(sharable_mem) sum_obj_size
from v$db_object_cache where type <> 'CURSOR'),
(select sum(sharable_mem) sum_sql_size
from v$sqlarea),
(select sum(250 * users_opening) sum_user_size
from v$sqlarea), v$parameter
where name = 'shared_pool_size';
---------------------------------------------
shared_pool_adv.sql
set lines 139
set pages 66
column SHARED_POOL_SIZE_FOR_ESTIMATE heading Pool_Size
column SHARED_POOL_SIZE_FACTOR heading Size_factor
column ESTD_LC_TIME_SAVED_FACTOR heading Time_Saved_factor
column ESTD_LC_MEMORY_OBJECT_HITS heading Mem_obj_hits
select * from v$shared_pool_advice;
----------------------------------------------------
shared_mem_usage.sql
select count(*),sum(SHARABLE_MEM),sum(PERSISTENT_MEM),sum(RUNTIME_MEM),
sum(SHARABLE_MEM+PERSISTENT_MEM+RUNTIME_MEM) total ,username
from v$sqlarea ,dba_users
where PARSING_USER_ID = user_id group by username order by total
/
------------------------------------------------
sgastat.sql
set lines 132 pages 45
set numw 20
select * from v$sgastat order by pool,bytes desc
/
-------------------------------------------------
sgafree.sql
rem -----------------------------------------------------------------------
rem Filename: sga_free.sql
rem Purpose: Reports free memory available in the SGA
rem Date: 09-Jun-1998
rem -----------------------------------------------------------------------
select inst_id,pool,name,
round(bytes/1024/1024,0) "Free (MB)"
from sys.gv_$sgastat f
where f.name = 'free memory'
order by pool,inst_id
/
--------------------------------------------------------------------------
sgadynamic.sql
set lines 160 pages 60
col COMPONENT format a25
col PARAMETER format a25
col CURRENT_SIZE_m format 99999999 heading "CURR|SIZE_MB"
col MIN_SIZE_m format 99999999 heading "MIN|SIZE_MB"
col MAX_SIZE_m format 99999999 heading "MAX|SIZE_MB"
col GRANULE_SIZE_m format 999999 heading "GRANL|SIZE_MB"
col OPER_COUNT format 99999999 heading "OPER|COUNT"
col USER_SPEC_SIZE_m format 99999999 heading "USER SPEC|SIZE_MB"
col end_time format a19
col LAST_OPER_TIME format a19 heading "LAST OPER TIME"
-- COLUMN created FORMAT A15 HEADING "TIME CREATED"
PROMPT "********** V$SGA_DYNAMIC_COMPONENTS ****************"
select COMPONENT,CURRENT_SIZE/1024/1024 CURRENT_SIZE_m,MIN_SIZE/1024/1024 MIN_SIZE_m,USER_SPECIFIED_SIZE/1024/1024 USER_SPEC_SIZE_m,MAX_SIZE/1024/1024 MAX_SIZE_m, OPER_COUNT,LAST_OPER_TYPE,LAST_OPER_MODE,to_char(LAST_OPER_TIME,'YYYYMMDD HH24:MI:SS') LAST_OPER_TIME,GRANULE_SIZE/1024/1024 GRANULE_SIZE_m from v$sga_dynamic_components;
PROMPT "********** V$SGA_DYNAMIC_FREE_MEMORY ***************"
select * from v$sga_dynamic_free_memory;
PROMPT "********** V$SGA_CURRENT_RESIZE_OPS ***************"
select * from v$sga_current_resize_ops;
PROMPT "********** V$SGA_RESIZE_OPS ***************"
PROMPT Press Enter to view history of V$SGA_RESIZE_OPS
accept x
--select * from v$sga_resize_ops;
select component, oper_type, oper_mode, initial_size/1024/1024 initial_size_m, TARGET_SIZE/1024/1024 TARGET_SIZE_m, FINAL_SIZE/1024/1024 FINAL_SIZE_m, status,
TO_char(end_time, 'YYYYMMDD HH24:MI:SS') end_time from v$sga_resize_ops
order by COMPONENT,end_time;
------------------------------------------------------------------------
sga_detail.sql
set serverout on
declare
dbname varchar2(15); -- Database Name
tsgasize number; -- Total SGA Size
bcsize number; -- Buffer Cache Size
spsize number; -- Shared Pool Size
jpsize number; -- Java Pool Size
lpsize number; -- Large Pool Size
fsize number; -- Fixed SGA Size
rbsize number; -- Redo Buffers
used number; -- Used SGA Memory
free number; -- Free SGA Memory
granule_size number; -- Granule Size
tvsize number; -- Total Variable Size
cursor c1 is
select name, value from sys.v$parameter where name in ('java_pool_size', 'large_pool_size');
cursor c2 is
select name, value from sys.v$sga;
begin
select name into dbname from sys.v$database;
select x.ksppstvl/(1024*1024) into granule_size from sys.x$ksppsv x, sys.x$ksppi y
where x.indx=y.indx and y.ksppinm='_ksmg_granule_size';
for cur1 in c1 loop
case cur1.name
when 'java_pool_size' then jpsize :=cur1.value;
when 'large_pool_size' then lpsize :=cur1.value;
end case;
end loop;
for cur2 in c2 loop
case cur2.name
when 'Fixed Size' then fsize := cur2.value;
when 'Variable Size' then tvsize := cur2.value;
when 'Database Buffers' then bcsize :=cur2.value;
when 'Redo Buffers' then rbsize :=cur2.value;
end case;
end loop;
/*Getting Shared Pool Size. Can not use shared_pool_size parameter value due
to bug 1673506*/
select cursiz_kghdsnew*granule_size into spsize
from sys.x$ksmsp_dsnew;
tsgasize := (fsize+tvsize+bcsize+rbsize);
free := (tvsize - ((spsize*1024*1024)+lpsize+jpsize));
used := tsgasize - free ;
dbms_output.put_line(' ');
dbms_output.new_line;
dbms_output.put_line('SGA Configuration for '||dbname);
dbms_output.put_line('-------------------------------');
dbms_output.put_line(' ');
dbms_output.new_line;
dbms_output.put_line('Current SGA Size : '||round(used/(1024*1024),2)||' MB');
dbms_output.put_line('Maximum SGA Size : '||round(tsgasize/(1024*1024),2)||' MB');
dbms_output.put_line('Memory Available for SGA Growth: '||round(free/(1024*1024),2)||' MB');
dbms_output.put_line('Buffer Cache Size : '|| round(bcsize/(1024*1024),2) ||' MB');
dbms_output.put_line('Shared Pool Size : '|| spsize ||' MB');
dbms_output.put_line('Large Pool Size : '|| round(lpsize/(1024*1024),2) ||' MB');
dbms_output.put_line('Java Pool Size : '|| round(jpsize/(1024*1024),2) ||' MB');
dbms_output.put_line('Fixed SGA : '||round(fsize/(1024*1024),2) ||' MB');
dbms_output.put_line('Redo Buffers : '|| round(rbsize/(1024*1024),2) ||' MB');
dbms_output.put_line('Granule Size : '||granule_size||' MB');
end;
/
set serverout off
set doc on
-----------------------------------------------------------------
sessions.sql
column status format a10
set feedback off
set serveroutput on
select username, sid, serial#, process, status
from v$session
where username is not null
/
column username format a20
column sql_text format a55 word_wrapped
set serveroutput on size 1000000
declare
x number;
begin
for x in
( select username||'('||sid||','||serial#||
') ospid = ' || process ||
' program = ' || program username,
to_char(LOGON_TIME,' Day HH24:MI') logon_time,
to_char(sysdate,' Day HH24:MI') current_time,
sql_address, LAST_CALL_ET
from v$session
where status = 'ACTIVE'
and rawtohex(sql_address) <> '00'
and username is not null order by last_call_et )
loop
for y in ( select max(decode(piece,0,sql_text,null)) ||
max(decode(piece,1,sql_text,null)) ||
max(decode(piece,2,sql_text,null)) ||
max(decode(piece,3,sql_text,null))
sql_text
from v$sqltext_with_newlines
where address = x.sql_address
and piece < 4)
loop
if ( y.sql_text not like '%listener.get_cmd%' and
y.sql_text not like '%RAWTOHEX(SQL_ADDRESS)%')
then
dbms_output.put_line( '--------------------' );
dbms_output.put_line( x.username );
dbms_output.put_line( x.logon_time || ' ' ||
x.current_time||
' last et = ' ||
x.LAST_CALL_ET);
dbms_output.put_line(
substr( y.sql_text, 1, 250 ) );
end if;
end loop;
end loop;
end;
/
/*
column username format a25
column module format a20
column action format a20
column client_info format a30
select username||'('||sid||','||serial#||')' username,
module,
action,
client_info
from v$session
where module||action||client_info is not null;
*/
------------------------------------------------------------------
session_count_for_user.sql
ACCEPT inp_user PROMPT 'Enter the desired USERNAME or <return>: ';
select inst_id,
username,
count(sid) Total_session,
count(case status when 'ACTIVE' THEN 1 ELSE NULL END) Active_session,
count(case status when 'INACTIVE' THEN 1 ELSE NULL END ) Inactive_Session
from gv$session
WHERE USERNAME LIKE DECODE('&&inp_user','', username,upper('&&inp_user'))
-- AND LOGON_TIME >= SYSDATE – INTERVAL '5’ MINUTE –> use this clause for more current information
group by inst_id,username order by 2,5 desc;
undefine inp_user;
---------------------------------------------------------------
rman_backup_check.sql
col command_id format a19
col input_bytes format a15
col output_bytes format a15
col MB_per_sec format a15
col elapsed_time format a15
col status format a22
col device_type format a11
set lines 160 pages 60
ALTER SESSION SET NLS_DATE_FORMAT='DD-MON-YYYY HH24:MI:SS';
SELECT
-- b.session_key, b.session_recid, b.session_stamp,
b.command_id, b.status, b.start_time, b.time_taken_display elapsed_time, b.input_type,
b.output_device_type device_type, b.input_bytes_display input_bytes , b.output_bytes_display output_bytes, b.output_bytes_per_sec_display MB_per_sec
FROM V$RMAN_BACKUP_JOB_DETAILS b WHERE ( b.start_time > (SYSDATE - 30) ) order by b.start_time desc;
----------------------------------------------------------------
redo_log_info.sql
-- $Header: cklog.sql,v 1.1 2005/02/03 17:55:08 oracle Exp $
--
REM ******************************************************
REM * ROUTINE: cklog.sql
REM * DESCRIPTION: Script obtains redo log information
REM *
REM * AUTHOR: John Burrows
REM *
REM *****************************************************
set linesize 160
set pagesize 50
set echo off;
COLUMN thread# FORMAT 99999999 HEADING "THRD#"
COLUMN sequence# FORMAT 99999999 HEADING "SEQ#"
COLUMN pid FORMAT 9999999999 HEADING "PID";
COLUMN group# FORMAT 99999 HEADING "GRP#";
COLUMN first_change# FORMAT 9999999999999999 HEADING "FIRSTCHG#/LOWSCN"
COLUMN last_change# FORMAT 9999999999999999 HEADING "LASTCHG#/HIGHSCN"
COLUMN first_time FORMAT A15 HEADING "FIRSTCHG# TIME"
COLUMN last_time FORMAT A15 HEADING "LASTCHG# TIME"
PROMPT ;
PROMPT **** CHECK DATABASE LOGS **** ;
PROMPT ;
SELECT
GROUP#,
THREAD#,
SEQUENCE#,
ROUND(((BYTES / 1024)/1024),2) "MBytes",
MEMBERS,
ARCHIVED,
STATUS,
FIRST_CHANGE#,
TO_CHAR( FIRST_TIME, 'DD-MON-YYYY HH24:MI:SS') "SWITCH TIME"
FROM V$LOG;
-------------------------------------------------------------
pga_advice.sql
/* Script to show pga information */
set pages 200
set lines 200
set numw 15
col name for a40
col PGA_TARGET_FOR_ESTIMATE for 999,999,999,999
PROMPT 'PGA Stat '
select * from v$pgastat;
PROMPT 'Target Advice '
select* from V$PGA_TARGET_ADVICE;
select low_optimal_size "low_kb",
high_optimal_size "high_kb",
optimal_executions "optimal",
round(optimal_executions*100/total_executions) "Opt Pct",
onepass_executions "onepass",
multipasses_executions "multi"
from v$sql_workarea_histogram
where total_executions != 0
/
-----------------------------------------------------------
partition_check_for_table.sql
set echo off
set verify off
set pages 200 lines 500
set linesize 700
col owner for a12
col table_name for a30
col column_name FORMAT A21 alias Partitioningkey HEADING "Partitioning key"
col DEF_TABLESPACE_NAME FORMAT A12 alias Tablespace HEADING "Tablespace"
--col PARTITION_COUNT alias partitionscount HEADING "partitions count"
col INDEX_name format A30
accept owner_name char prompt 'owner_name:'
accept table_name char prompt 'table_name:'
prompt check table is partitioned or not
select owner,table_name,partitioned
from dba_tables
where owner=UPPER('&owner_name') and table_name=upper('&table_name');
prompt check partitioning key for the table
select dpt.OWNER,
dpt.TABLE_NAME,
dpt.PARTITIONING_TYPE,
dpt.PARTITION_COUNT as "partitionscount",
--dpt.PARTITIONING_KEY_COUNT,
dpt.STATUS,
dpt.DEF_TABLESPACE_NAME as "Tablespace",
dpk.column_name as "Partitioningkey"
from dba_part_tables dpt inner join dba_part_key_columns dpk on dpt.table_name=dpk.name and dpt.owner=dpk.owner
where dpt.owner=UPPER('&owner_name') and dpt.table_name=upper('&table_name');
prompt check partitioning indexes for the table
select dpi.OWNER,
dpi.INDEX_NAME,
dpi.TABLE_NAME,
dpi.PARTITIONING_TYPE,
dpi.PARTITION_COUNT as "partitionscount",
--dpi.PARTITIONING_KEY_COUNT ,
dpi.DEF_TABLESPACE_NAME as "Tablespace",
dpk.column_name as "Partitioningkey"
from dba_part_indexes dpi inner join dba_part_key_columns dpk on dpi.table_name=dpk.name and dpi.owner=dpk.owner
where dpi.owner=UPPER('&owner_name') and dpi.table_name=upper('&table_name');
undefine owner
undefine table_name
---------------------------------------------------------
parallel_process_check.sql
-----------------------------------
-- Purpose: To show all active parallel processes and there parents
-- with the wait events and i/o they are incurring
-- Developed by : Jason Zormeier
-- 08/06/2004
-----------------------------------
set verify off
set lines 132 pages 100
col sid format 9999 head 'SID'
col logical_reads format 999,999,999 heading 'LOGICAL|READS'
col physical_reads format 999,999,999 heading 'PHYSICAL|READS'
col logical_writes format 999,999,999 heading 'LOGICAL|WRITES'
col username format a10 head 'USERNAME'
col osuser format a8 head 'OSUSER'
col process format 99999 head 'CLIENT'
col spid format 99999 head 'SHADOW'
col event format a29 heading 'EVENT'
col wait_time format a4 heading 'WAIT|TIME'
col seconds_in_wait format a6 heading 'SECONDS|IN WAIT'
col state format a5 heading 'STATE'
SELECT s.sid,
s.serial#,
s.audsid,
nvl(s.username,substr(p.program,-6)) username,
s.process process,
trunc(p.spid) spid,
s.status,
io.block_gets + io.consistent_gets logical_reads,
io.physical_reads,
io.block_changes + io.consistent_changes logical_writes,
event,
seq#,
trim(wait_time) wait_time ,
trim(seconds_in_wait) seconds_in_wait,
trim(DECODE(vw.state,'WAITED KNOWN TIME','WKT','WAITED SHORT TIME','WST','WAITING','W',vw.state)) state
FROM
(
SELECT
v.sid,
v.serial#
FROM
v$session v
WHERE
v.audsid in(
SELECT
distinct s.audsid
FROM
v$session s
WHERE
s.audsid IS NOT NULL AND
s.audsid != 0
GROUP BY
s.audsid
HAVING count(*) > 1
)
) pproc,
v$session s ,
v$process p,
v$sess_io io,
v$session_wait vw
where
pproc.sid = s.sid AND
pproc.serial# = s.serial# AND
s.paddr = p.addr AND
io.sid = pproc.sid AND
vw.sid = pproc.sid
order by
s.audsid,s.program desc
/
------------------------------------------------------------------
object_size_check.sql
undefine size_in_mb
set verify off
set lines 172
set pages 300
col segment_name format a32
col owner format a10
col PART_NAME format a29
col segment_type format a18 heading "TYPE"
col size_mb format 9999999
col tablespace_name format a18
col INIT_next_M format a11
col blk_ext format a15
break on report
compute sum of SIZE_MB on report
PROMPT;
ACCEPT owner PROMPT 'Enter the desired OWNER or <return>: ';
ACCEPT tablespace_name PROMPT 'Enter the desired TABLESPACE_NAME or <return>: ';
ACCEPT segname PROMPT 'Enter the desired SEGMENT_NAME or <return>: ';
select owner
,a.segment_name
,a.segment_type
,nvl(partition_name,'N/A') PART_NAME
,round(initial_extent/1024/1024,2)||'-'|| round(next_extent/1024/1024,2)||' M' INIT_next_M
,blocks||'-'|| EXTENTS blk_ext
,tablespace_name
,bytes/1024/1024 SIZE_MB
from dba_segments a
where
owner LIKE DECODE(upper('&&owner'),'', owner,upper('&&owner')) and
tablespace_name LIKE DECODE(upper('&&tablespace_name'),'', tablespace_name,upper('&&tablespace_name')) and
a.segment_name LIKE DECODE(upper('&&segname'),'',a.segment_name,upper('&&segname'))
-- and segment_type in ('TABLE', 'INDEX', 'CLUSTER','TABLE PARTITION','INDEX PARTITION')
order by 8
/
/*
with lobs as
(select segment_name from dba_lobs where table_name LIKE DECODE(upper('&&segname'),'', segment_name,upper('&&segname')))
select owner
,a.segment_name
,a.segment_type
,nvl(partition_name,'N/A') PART_NAME
,round(initial_extent/1024/1024,2)||'-'|| round(next_extent/1024/1024,2)||' M' INIT_next_M
,blocks||'-'|| EXTENTS blk_ext
,tablespace_name
,bytes/1024/1024 SIZE_MB
from dba_segments a, lobs b
where
owner LIKE DECODE(upper('&&owner'),'', owner,upper('&&owner')) and
tablespace_name LIKE DECODE(upper('&&tablespace_name'),'', tablespace_name,upper('&&tablespace_name')) and (
a.segment_name LIKE DECODE(upper('&&segname'),'',a.segment_name,upper('&&segname')) or
a.segment_name = b.segment_name)
-- and segment_type in ('TABLE', 'INDEX', 'CLUSTER','TABLE PARTITION','INDEX PARTITION')
order by 8
/
*/
undefine segname;
---------------------------------------------------------------
object_privs_check.sql
set pages 50 feed off veri off lines 132
col grantee format a20
col table_name format a60
col privilege format a12
col column_name format a50
break on grantee
PROMPT ;
PROMPT **** CHECK OBJECT PRIVS ****;
PROMPT ;
accept objnm prompt "Enter Object Name: "
accept objowner prompt "Enter Object Owner : "
Prompt
Prompt
select grantee, privilege,owner||'.'||table_name table_name
from sys.dba_tab_privs
where table_name LIKE DECODE ('&&objnm', '', table_name, UPPER ('&&objnm'))
and owner LIKE DECODE ( '&&objowner', '', owner, UPPER('&&objowner'))
order by grantee;
Prompt
Prompt Column Privs...
Prompt
select grantee, privilege,owner||'.'||table_name||'.'||column_name column_name
from sys.dba_col_privs
where table_name LIKE DECODE ('&&objnm', '', table_name, UPPER ('&&objnm'))
and owner LIKE DECODE ( '&&objowner', '', owner, UPPER('&&objowner'))
order by grantee;
----------------------------------------------------------------
mview_check.sql
-- $Header: ckobj.sql,v 1.1 2005/02/03 17:55:09 oracle Exp $
--
set linesize 162
set pagesize 5000
set long 1000000
REM set pause on
set echo off
set verify off
COLUMN owner FORMAT A12 HEADING "OWNER"
COLUMN NAME FORMAT A20 HEADING "NAME"
PROMPT ;
PROMPT **** CHECK SOURCE FOR GIVEN OBJECT ****;
PROMPT ;
ACCEPT objowner CHAR PROMPT 'Enter desired MVIEW OWNER or <return>: ';
ACCEPT trname CHAR PROMPT 'Enter desired MVIEW NAME or <return>: ';
select owner,MVIEW_NAME,REWRITE_ENABLED,REFRESH_MODE,REFRESH_METHOD,BUILD_MODE,
to_char(LAST_REFRESH_DATE,'YYYYMMDD HH24MISS') LAST_REFRESH_DATE ,
LAST_REFRESH_TYPE,QUERY
from dba_mviews
where
owner LIKE DECODE ( '&&objowner', '', owner, UPPER('&&objowner'))
AND MVIEW_NAME LIKE DECODE ('&&trname', '', MVIEW_NAME, UPPER ('&&trname')) ;
undefine objowner
undefine trname
----------------------------------------------------------------
mem_usage.sql
define sid = &sid
define inst_id=&inst_id
column name format a40
column value format 999,999,999,999
column category format a10
column allocated format 999,999,999,999
column used format 999,999,999,999
column max_allocated format 999,999,999,999
column pga_used_mem format 999,999,999,999
column pga_alloc_mem format 999,999,999,999
column pga_freeable_mem format 999,999,999,999
column pga_max_mem format 999,999,999,999
select
name, value
from
gv$sesstat ss,
gv$statname sn
where
sn.name like '%ga memory%'
and ss.statistic# = sn.statistic#
and ss.INST_ID=sn.INST_ID
and ss.inst_id=&&inst_id and ss.sid = &&sid
;
select
category,
allocated,
used,
max_allocated
from
gv$process_memory
where
pid = (
select pid
from gv$process
where
addr = (
select paddr
from gV$session
where sid = &&sid
and inst_id=&&inst_id
)
and inst_id=&&inst_id
) and inst_id=&&inst_id
;
select
pga_used_mem,
pga_alloc_mem,
pga_freeable_mem,
pga_max_mem
from
gv$process
where
addr = (
select paddr
from gV$session
where sid = &&sid
and inst_id=&&inst_id
) and inst_id=&&inst_id;
------------------------------------------------------------
longops.sql
set lines 200
set verify off
set pages 100
col OPNAME format a18 trunc
col sid format 9999
col serial# format 99999
col ElapSecs format 9999999
col SecsLeft format 9999999
col "% Done" format 99
col qcsid format 9999
col username format a10
col message format a65 trunc
break on qcsid
PROMPT;
ACCEPT inp_sess PROMPT 'Enter the desired SESSION ID or <return>: ';
ACCEPT inp_usr PROMPT 'Enter the desired USERNAME or <return>: ';
select username,SID,SERIAL#,OPNAME,ELAPSED_SECONDS "ElapSecs",TIME_REMAINING "SecsLeft",qcsid,trunc((sofar/totalwork)*100) "% Done",MESSAGE
from v$session_longops s
where sid LIKE DECODE('&&inp_sess','', s.sid,'&&inp_sess')
and username LIKE DECODE(upper('&&inp_usr'),'', s.username,upper('&&inp_usr'))
and TIME_REMAINING<>0
order by qcsid;
undefine inp_sess
----------------------------------------------------
locks.sql
set lines 200
set pages 100
col osuser format a8
col username format a13
col object format a32
col pid format a10
col lmode format a7
col lrequest format a7
col sid format 999
col machine format a18
col program format a25
select s.osuser,nvl(s.username, 'background') username,s.sid, s.serial#,s.program,s.machine,
p.spid PID,
decode(l.type,
-- Long locks
'TM', 'DML/DATA ENQ', 'TX', 'TRANSAC ENQ',
'UL', 'PLS USR LOCK',
-- Short locks
'BL', 'BUF HASH TBL', 'CF', 'CONTROL FILE',
'CI', 'CROSS INST F', 'DF', 'DATA FILE ',
'CU', 'CURSOR BIND ',
'DL', 'DIRECT LOAD ', 'DM', 'MOUNT/STRTUP',
'DR', 'RECO LOCK ', 'DX', 'DISTRIB TRAN',
'FS', 'FILE SET ', 'IN', 'INSTANCE NUM',
'FI', 'SGA OPN FILE',
'IR', 'INSTCE RECVR', 'IS', 'GET STATE ',
'IV', 'LIBCACHE INV', 'KK', 'LOG SW KICK ',
'LS', 'LOG SWITCH ',
'MM', 'MOUNT DEF ', 'MR', 'MEDIA RECVRY',
'PF', 'PWFILE ENQ ', 'PR', 'PROCESS STRT',
'RT', 'REDO THREAD ', 'SC', 'SCN ENQ ',
'RW', 'ROW WAIT ',
'SM', 'SMON LOCK ', 'SN', 'SEQNO INSTCE',
'SQ', 'SEQNO ENQ ', 'ST', 'SPACE TRANSC',
'SV', 'SEQNO VALUE ', 'TA', 'GENERIC ENQ ',
'TD', 'DLL ENQ ', 'TE', 'EXTEND SEG ',
'TS', 'TEMP SEGMENT', 'TT', 'TEMP TABLE ',
'UN', 'USER NAME ', 'WL', 'WRITE REDO ',
'TYPE='||l.type) type,
decode(l.lmode, 0, 'NONE', 1, 'NULL', 2, 'RS', 3, 'RX',
4, 'S', 5, 'RSX', 6, 'X',
to_char(l.lmode) ) lmode,
decode(l.request, 0, 'NONE', 1, 'NULL', 2, 'RS', 3, 'RX',
4, 'S', 5, 'RSX', 6, 'X',
to_char(l.request) ) lrequest,
decode(l.type, 'MR', decode(u.username, null,
'DICTIONARY OBJECT', u.username||'.'||o.object_name),
'TD', u.username||'.'||o.object_name,
'TM', u.username||'.'||o.object_name,
'RW', 'FILE#='||substr(l.id1,1,3)||
' BLOCK#='||substr(l.id1,4,5)||' ROW='||l.id2,
'TX', 'RS+SLOT#'||l.id1||' WRP#'||l.id2,
'WL', 'REDO LOG FILE#='||l.id1,
'RT', 'THREAD='||l.id1,
'TS', decode(l.id2, 0, 'ENQUEUE',
'NEW BLOCK ALLOCATION'),
'ID1='||l.id1||' ID2='||l.id2) object
from sys.v_$lock l, sys.v_$session s, dba_objects o, dba_users u,
sys.v_$process p
where s.paddr = p.addr(+)
and l.sid = s.sid
and l.id1 = o.object_id(+)
and o.owner = u.username(+)
and l.type <> 'MR'
UNION ALL /*** LATCH HOLDERS ***/
select s.osuser, s.username,s.sid, s.serial#,s.program, s.machine,p.spid PID,
'LATCH', 'X', 'NONE', h.name||' ADDR='||rawtohex(laddr)
from sys.v_$process p, sys.v_$session s, sys.v_$latchholder h
where h.pid = p.pid
and p.addr = s.paddr
UNION ALL /*** LATCH WAITERS ***/
select s.osuser, s.username,s.sid, s.serial#,s.program, s.machine,p.spid PID,
'LATCH', 'NONE', 'X', name||' LATCH='||p.latchwait
from sys.v_$session s, sys.v_$process p, sys.v_$latch l
where latchwait is not null
and p.addr = s.paddr
and p.latchwait = l.addr
order by osuser,username
/
---------------------------------------------------------------
locked_object_check.sql
col OBJECT_NAME for a30;
select lo.inst_id, do.owner, do.object_name, oracle_username, os_user_name, process, locked_mode from gv$locked_object lo , dba_objects do
where lo.object_id=do.OBJECT_ID;
--------------------------------------------------------
kill_idle_session.sql
set echo off
set verify off
set pages 100 lines 132
accept username char prompt 'USER_NAME:'
col edition format a10
COLUMN program FORMAT A15 trunc;
COLUMN module FORMAT A21
COLUMN type FORMAT A8;
col inst_id format 999
COLUMN COMMAND FORMAT A15;
COLUMN lockwait FORMAT A9 HEADING "LOCK WAIT";
COLUMN sid FORMAT 9999 HEADING SESS;
COLUMN status FORMAT A8 HEADING STATUS;
col service_name format a15 trunc
COLUMN machine FORMAT A15 trunc HEADING MACHINE;
COLUMN sid_serial# FORMAT a13
COLUMN osuser FORMAT A12 HEADING "OS USER";
COLUMN logon_time format a22
COLUMN "ORAPID" FORMAT 9999
COLUMN spid FORMAT a9 HEADING "SHAD PID";
COLUMN process FORMAT a13 HEADING "OS PID";
COL EVENT FORMAT A25 TRUNC
col seconds_in_wait format 9999999 heading 'SECONDS|IN WAIT'
col BLK_I format 99
col BLK_SESS format 99999
col obj_file_blk format a20
col LCE format 999999
col SQL_EXEC_START format a22
col action format a10
col client_identifier format a10
col edition_name format a12
col object_name format a35
select inst_id,sid,serial#,to_char(logon_time,'DD-MON-YYYY HH24:MI:SS') logon_time,username,osuser,service_name,machine,program,object_name edition_name
from gv$session a, dba_objects b
where a.session_edition_id=b.object_id and a.username=upper('&username') and a.status='INACTIVE' and last_call_et>=1800
order by object_name;
select 'ALTER SYSTEM KILL SESSION '''
|| sid
|| ','
|| serial#
|| ',@'
|| inst_id
|| ''' immediate;'
from gv$session
where status='INACTIVE' and last_call_et>=1800 and username=upper('&username');
----------------------------------------------------------------
invalid_compile_schema.sql
exec SYS.dbms_utility.compile_schema(SCHEMA=>upper('&owner'), compile_all=>FALSE);
--------------------------------------------------------------
invalid_check.sql
select owner, count(*) from dba_objects where status = 'INVALID' group by owner
/
-- $Header: ckobj.sql,v 1.1 2005/02/03 17:55:09 oracle Exp $
--
set linesize 200
set pagesize 100
REM set pause on
set echo off
set verify off
COLUMN owner FORMAT A12 HEADING "OWNER"
COLUMN object_name FORMAT A30 HEADING "NAME"
COLUMN SUBOBJECT_NAME FORMAT A26
COLUMN object_type FORMAT A17 HEADING "TYPE"
COLUMN created FORMAT A15 HEADING "TIME CREATED"
COLUMN status FORMAT A8 HEADING "STATUS"
COLUMN LAST_DDL_TIME FORMAT A17 ALIAS lastddl HEADING "LAST DDL"
-- SELECT object_name,SUBOBJECT_NAME, object_type, status,owner, object_id,
SELECT object_name, object_type, status,owner, object_id,
TO_CHAR(created, ('DDMONYY-HH24:MI')) created,
TO_CHAR(last_ddl_time, ('DDMONYY-HH24:MI:SS')) lastddl
FROM sys.DBA_OBJECTS
WHERE status LIKE 'INVALID'
ORDER BY last_ddl_time,owner,object_name, SUBOBJECT_NAME
-- ORDER BY object_type, object_name, owner
;
undefine objtype
undefine objowner
undefine objnm
undefine objid
undefine statid
------------------------------------------------------------------------
init_parameter_check.sql
set lines 180 pages 100
set verify off
col VALUE for a60
col name format a30
col DESCRIPTION format a30
PROMPT;
PROMPT **** SHOW INITILIZATION PARAMETERS ****;
PROMPT;
ACCEPT inp_name PROMPT 'Enter the desired parameter name or <return>: ';
select NAME,TYPE,ISDEFAULT,ISSES_MODIFIABLE,
ISSYS_MODIFIABLE,ISINSTANCE_MODIFIABLE,
ISMODIFIED,ISADJUSTED,ISDEPRECATED,value
-- substr(DESCRIPTION,1,30) DESCRIPTION
from v$parameter
where UPPER(name) LIKE DECODE('&&inp_name', '', upper(name), UPPER('&&inp_name'))
;
----------------------------------------------------------------------
index_check.sql
set echo off
set verify off
set pages 100 lines 132
col column_name for a32
col owner for a12
col table_name for a20
col name for a20
col column_name for a20
accept owner_name char prompt 'owner_name:'
accept table_name char prompt 'table_name:'
prompt FK and indexes of the given table
prompt =================================
select A.owner owner, A.table_name table_name, 'Constraint' Class, A.constraint_name Name, B.position position, B.column_name column_name
from dba_constraints A, dba_cons_columns B
where A.constraint_name = B.constraint_name
and A.constraint_type = 'R'
and A.owner = UPPER('&owner_name')
and A.table_name = UPPER('&table_name')
union all
select index_owner owner, table_name, 'Index' Class, index_name Name, column_position position, column_name
from dba_ind_columns
where index_owner = UPPER('&owner_name')
and table_name = UPPER('&table_name');
prompt FK and indexes of tables referencing PK/UK of the given table
prompt ==============================================================
with FK as
(select B.owner, B.table_name
from dba_constraints A, dba_constraints B
where A.owner=B.r_owner
and A.constraint_name=B.r_constraint_name
and A.constraint_type in ('P','U')
and B.constraint_type = 'R'
and A.owner = UPPER('&owner_name')
and A.table_name = UPPER('&table_name') )
select A.owner owner, A.table_name table_name, 'Constraint' Class, A.constraint_name Name, B.position position, B.column_name column_name
from dba_constraints A, dba_cons_columns B
where A.constraint_name = B.constraint_name
and A.constraint_type = 'R'
and (A.owner, A.table_name) in (select owner, table_name from FK)
union all
select index_owner owner, table_name, 'Index' Class, index_name Name, column_position position, column_name
from dba_ind_columns
where (index_owner, table_name) in (select owner, table_name from FK);
----------------------------------------------------------------
hidden_parameters.sql
set lines 132
set verify off
set pages 100
PROMPT;
col "Parameter" format a45
col "Session Value" format a10
col "Instance Value" format a10
ACCEPT hidden_parameter PROMPT 'Enter the desired hidden_parameter or <return>: ';
select a.ksppinm "Parameter", b.ksppstvl "Session Value", c.ksppstvl "Instance Value"
from x$ksppi a, x$ksppcv b, x$ksppsv c
where a.indx = b.indx and a.indx = c.indx
and substr(ksppinm,1,1)='_'
and a.ksppinm like DECODE(lower('%&&hidden_parameter%'),'',a.ksppinm,lower('%&&hidden_parameter%'))
order by a.ksppinm;
---------------------------------------------------------------------
grants_role_check.sql
set echo off
set verify off
set pages 100 lines 132
set linesize 300
col grantee for a12
col object_name for a20
ACCEPT objnm CHAR PROMPT 'Enter desired OBJECT NAME or <return>: ';
ACCEPT role CHAR PROMPT 'Enter desired ROLE or user NAME or <return>: ';
select grantee,table_name as object_name,privilege from dba_tab_privs where table_name LIKE DECODE ('&&objnm', '', object_name, UPPER ('&&objnm'))
and grantee LIKE DECODE ('&&role', '', grantee, UPPER ('&&role'));
undefine name
-------------------------------------------------------------
grants_check.sql
set verify off
set lines 200
set pages 300
col table_name format a32
col owner format a10
ACCEPT tabname PROMPT 'Enter the desired TABLE_NAME: ';
ACCEPT grante PROMPT 'Enter User/Role or <return>: ';
select *
from dba_tab_privs
where table_name='&&tabname'
and grantee like DECODE(upper('&&grante'),'', grantee,upper('&&grante'))
order by 1,5 ;
undefine tabname;
undefine grante;
--------------------------------------------------------
full_tablescan_check.sql
-- You want to monitor which tables in a database are being accessed by full table scan.
-- Solution Description -------------------- Perform following query:
set lines 132
set pages 100
col segment_name format a30
col owner format a15
SELECT s.segment_name,s.segment_type,s.owner
FROM dba_extents s,x$bh x WHERE s.segment_type='TABLE'
AND x.dbarfil=s.file_id AND
x.dbablk between s.block_id and s.block_id+s.blocks-1
AND standard.bitand(x.flag,524288)>0
AND owner<>'SYS';
-- If the above query is too slow, an alternative is
SELECT o.object_name,o.object_type,o.owner
FROM dba_objects o,x$bh x
WHERE x.obj=o.data_object_id
AND o.object_type='TABLE'
AND standard.bitand(x.flag,524288)>0
AND o.owner<>'SYS';
--------------------------------------------------------------
ddl_get.sql
set long 4000000
-- SELECT dbms_metadata.get_ddl('TABLE', table_name) FROM user_tables;
set lines 132
set pages 0
set long 90000
set verify off
accept v_schema_name PROMPT "Enter Schema name : "
accept v_tab_name PROMPT "Enter Table name : "
accept v_obj_type PROMPT "Enter Object type : "
spool get_ddl_&v_schema_name._&v_tab_name._&v_obj_type
execute DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'STORAGE',false);
--select SUBSTR(DBMS_METADATA.GET_DDL('QUEUE','&v_tab_name','&v_schema_name'),1,8000) from dual;
select SUBSTR(DBMS_METADATA.GET_DDL(upper('&v_obj_type'),upper('&v_tab_name'),upper('&v_schema_name')),1,18000) from dual;
spool off
---------------------------------------------------------
dbsize_check.sql
set linesize 200
set pagesize 100
REM set pause on
set echo off
set verify off
select
"Reserved_Space(GB)", "Reserved_Space(GB)" - "Free_Space(GB)" "Used_Space(GB)","Free_Space(GB)"
from(
select
(select round(sum(bytes/(1014*1024*1024)),2) from dba_data_files) "Reserved_Space(GB)",
(select round(sum(bytes/(1024*1024*1024)),2) from dba_free_space) "Free_Space(GB)"
from dual
);
---------------------------------------------------------
db_links_check.sql
-- -----------------------------------------------------------------------------------
-- File Name : https://oracle-base.com/dba/monitoring/db_links.sql
-- Author : Tim Hall
-- Description : Displays information on all database links.
-- Requirements : Access to the DBA views.
-- Call Syntax : @db_links
-- Last Modified: 11/05/2007
-- -----------------------------------------------------------------------------------
SET LINESIZE 150
COLUMN owner FORMAT A30
COLUMN db_link FORMAT A30
COLUMN username FORMAT A30
COLUMN host FORMAT A30
SELECT owner,
db_link,
username,
host
FROM dba_db_links
ORDER BY owner, db_link;
----------------------------------------------------------
datafiles_for_tablespace_check.sql
col file_name format a60
col file_id format 9999
col tablespace_name format a20
break on tablespace_name
set lines 162
set pages 100
set verify off
PROMPT ;
PROMPT **** CHECK DATAFILES ****;
PROMPT ;
ACCEPT tsname CHAR PROMPT 'Enter desired TABLESPACE NAME or <return>: ';
ACCEPT fname CHAR PROMPT 'Enter desired DB FILE NAME or <return>: ';
select
tablespace_name,
file_id,
file_name,
df.bytes/1024/1024 "BYTES_MB",
vd.status,
AUTOEXTENSIBLE,
maxbytes/1024/1024 "MAX_BYTES"
from dba_data_files df,v$datafile vd where
df.file_id=vd.file#
and upper(tablespace_name) LIKE DECODE ('&&tsname', '', upper(tablespace_name), UPPER ('&&tsname'))
and lower(file_name) LIKE DECODE ('&&fname', '', lower(file_name), lower ('&&fname'))
order by tablespace_name,file_name
/
undefine tsname;
---------------------------------------------
datafiles_check.sql
-- -----------------------------------------------------------------------------------
-- File Name : https://oracle-base.com/dba/monitoring/datafiles.sql
-- Author : Tim Hall
-- Description : Displays information about datafiles.
-- Requirements : Access to the V$ views.
-- Call Syntax : @datafiles
-- Last Modified: 17-AUG-2005
-- -----------------------------------------------------------------------------------
SET LINESIZE 200
COLUMN file_name FORMAT A70
SELECT file_id,
file_name,
ROUND(bytes/1024/1024/1024) AS size_gb,
ROUND(maxbytes/1024/1024/1024) AS max_size_gb,
autoextensible,
increment_by,
status
FROM dba_data_files
ORDER BY file_name;
------------------------------------------------
cursor_check_for_session.sql
select
o.sid, o.sql_text, o.address, o.hash_value, o.user_name, s.schemaname, o.sql_id
from v$open_cursor o, v$session s
where o.saddr = s.saddr
and o.sid = s.sid
and ( O.SID = &&SID);
---------------------------------------------
blocking_check.sql
set line 300;
set trimspool off;
col SID_SERAIL# for a15;
col ACTION for a40;
col modlue_program for a50;
col OBJ_FILE_BLK_ROW for a30;
col EVENT for a30;
col INST_ID for 999;
col USERNAME for a12;
col LCE for 999999;
col CLIENT_IDENTIFIER for a10;
col Machine for a15;
SELECT DECODE (a.request, 0, 'holder: ', ' waiter: ') || a.SID || ',' || a.inst_id || ',' || b.serial# SID_SERAIL# ,
username,
TO_CHAR (logon_time, 'YYYYMMDD HH24:MI:SS') logon_time
, sql_id
, event,
machine
-- , id1, id2
--, lmode
--, request, a.TYPE
, last_call_et LCE
--, ctime
, row_wait_obj#
|| '-'
|| row_wait_file#
|| '-'
|| row_wait_block#
|| '-'
|| row_wait_row# obj_file_blk_row
--, action
, module||'-'|| program modlue_program
--,action
,CLIENT_IDENTIFIER
--, BLOCKING_SESSION_STATUS
--, BLOCKING_INSTANCE
--, BLOCKING_SESSION
FROM gv$lock a, gv$session b
WHERE a.inst_id = b.inst_id
AND a.SID = b.SID
AND (a.id1, a.id2, a.TYPE) IN (SELECT id1, id2, TYPE
FROM gv$lock
WHERE request > 0);
set trimspool on;
col SID for a20;
col ACTION for a25;
col modlue_program for a20;
col EVENT for a25;
col INST_ID for 999;
col USERNAME for a10;
col CLIENT_IDENTIFIER for a10;
col Machine for a30;
col INST_SERAIL# for a14;
SELECT DECODE (a.request, 0, 'holder: ', ' waiter: ') || a.SID SID,
a.inst_id|| ',' || b.serial# INST_SERAIL#,
username,
logon_time
, sql_id
, event
, action
,CLIENT_IDENTIFIER
FROM gv$lock a, gv$session b
WHERE a.inst_id = b.inst_id
AND a.SID = b.SID
AND (a.id1, a.id2, a.TYPE) IN (SELECT id1, id2, TYPE
FROM gv$lock
WHERE request > 0);
-------------------------------------------------------------------
awrsqlhist.sql
SET LINES 235 PAGES 40 VERIFY OFF
COL INSTANCE_NUMBER FORMAT 99 head inst_id
COL SNAP_ID FORMAT 9999999
COL SQL_PROFILE FORMAT A11
COL MODULE FORMAT A20 TRUNC
COL BEGIN_INTERVAL_TIME FOR A19 TRUNC
COL END_INTERVAL_TIME FOR A19 TRUNC
COL Psch FORMAT A10
col EXE_TOT format 99999999
col EXE_delt format 99999999
col "ELAP_DELT/exec(s)" format 99999.99 head "ELAP_DELT|per_exec|(s)"
col "ELAP_TOT/exec(s)" format 99999.99 head "ELAP_TOT|per_exec|(s)"
SELECT SN.INSTANCE_NUMBER,SN.SNAP_ID
,to_char(BEGIN_INTERVAL_TIME,'DD-MON-YYYY HH24:MI') BEGIN_INTERVAL_TIME
,TO_char(END_INTERVAL_TIME,'DD-MON-YYYY HH24:MI') END_INTERVAL_TIME
--,SQL_ID
--,PARSING_SCHEMA_NAME psch
,PLAN_HASH_VALUE PL_HASH_VALUE
--,EXECUTIONS_TOTAL EXE_TOT
,EXECUTIONS_DELTA EXE_DELT
--,round(ELAPSED_TIME_TOTAL/(EXECUTIONS_TOTAL*1000000),2) "ELAP_TOT/exec(s)"
,nvl(round(ELAPSED_TIME_DELTA/(decode(EXECUTIONS_DELTA,0,EXECUTIONS_TOTAL,EXECUTIONS_DELTA)*1000000),2),0) "ELAP_DELT/exec(s)"
--,round((ELAPSED_TIME_TOTAL/1000000),2) "ELAP_TOT(s)"
,round((ELAPSED_TIME_DELTA/1000000),2) "ELAP_DELT(s)"
--,ROWS_PROCESSED_TOTAL ROWS_TOT
,ROWS_PROCESSED_DELTA ROWS_DELT
--,BUFFER_GETS_TOTAL BUF_TOT
,round(BUFFER_GETS_DELTA/EXECUTIONS_DELTA) "BUF_DELT/exec"
--,DISK_READS_TOTAL DISK_TOTAL
,round(DISK_READS_DELTA/EXECUTIONS_DELTA) "DISK_DELT/exec"
,round(CPU_TIME_DELTA/EXECUTIONS_DELTA) "CPU_DELT/exec"
,decode(SQL_PROFILE,'','NO','YES') sql_profile
,MODULE
FROM DBA_HIST_SQLSTAT SQ, DBA_HIST_SNAPSHOT SN
WHERE SQ.SNAP_ID=SN.SNAP_ID
AND SQ.DBID=SN.DBID
AND SQ.INSTANCE_NUMBER=SN.INSTANCE_NUMBER
AND SQL_ID='&&SQL_ID'
AND executions_delta>0
AND trunc(BEGIN_INTERVAL_TIME)>=trunc(sysdate) - &no_days
ORDER BY snap_id
/
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('&&sql_id',0));
undefine sql_id
---------------------------------------------------------------
awrplan.sql
PROMPT Show current and historical plans ...
set linesize 240
set pagesize 999
spool h:\scripts\&&1..txt
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('&&1',NULL));
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('&&1',0));
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_AWR('&&1'));
undefine 1
Spool off;
-----------------------------------------------------------------
awrexplain.sql
set lines 132
set pages 100
select * from table(dbms_xplan.display_awr('&sql_id'));
select snap_interval, retention, MOST_RECENT_PURGE_TIME from sys.wrm$_wr_control;
------------------------------------------------------------------
autotrace.sql
-- SET AUTOTRACE OFF No AUTOTRACE report is generated. This is the default.
-- SET AUTOTRACE ON EXPLAIN -- The AUTOTRACE report shows only the optimizer execution path.
-- SET AUTOTRACE ON STATISTICS - The AUTOTRACE report shows only the SQL statement execution statistics.
-- SET AUTOTRACE ON The AUTOTRACE report includes both the optimizer execution path and the SQL statement execution statistics.
-- SET AUTOTRACE TRACEONLY Similar to SET AUTOTRACE ON, but suppresses the printing of the user's query output, if any. If STATISTICS is enabled, query data is still fetched, but not printed.
-- To use this feature, the PLUSTRACE role must be granted to the user, such as HR. DBA privileges are required to grant the PLUSTRACE role
set autotrace traceonly explain
-----------------------------------------------------------------
asmdiskgrp_info.sql
-- V$ASM_DISKGROUP
-- Describes information about ASM disk groups mounted by the ASM instance
SET LINESIZE 100
SET PAGESIZE 60
TTITLE 'ASM Disk Groups (From V$ASM_DISKGROUP)'
COL group_number FORMAT 99999 HEADING 'ASM|Disk|Grp #'
COL name FORMAT A12 HEADING 'ASM Disk|Group Name' WRAP
COL sector_size FORMAT 99999999 HEADING 'Sector|Size'
COL block_size FORMAT 999999 HEADING 'Block|Size'
COL au_size FORMAT 99999999 HEADING 'Alloc|Unit|Size'
COL state FORMAT A11 HEADING 'Disk|Group|State'
COL type FORMAT A06 HEADING 'Disk|Group|Type'
COL total_mb FORMAT 999999999999 HEADING 'Total|Space(MB)'
COL free_mb FORMAT 999999999999 HEADING 'Free|Space(MB)'
SELECT
group_number
,name
,sector_size
,block_size
,allocation_unit_size au_size
,state
,type
,total_mb
,free_mb
FROM
v$asm_diskgroup
ORDER BY
name
;
TTITLE OFF
--------------------------------------------------------------
