act_wait.sql
col SCHEMANAME for a15
select gvs.INST_ID, gvs.SCHEMANAME, gvs.SID, gvp.SPID, gvs.PROCESS, gvs.SQL_ID, substr(gvs.EVENT,1,25) EVENT, substr(gvs.SECONDS_IN_WAIT/60/60,1,3) HRS_WAIT, gvs.MODULE from gv$session gvs, gv$process gvp where gvs.PADDR=gvp.ADDR and gvs.TYPE != 'BACKGROUND' and gvs.USERNAME != 'SYS' and gvs.STATUS = 'ACTIVE' and gvs.SECONDS_IN_WAIT/60/60 > 0.5 order by gvs.INST_ID, gvs.SECONDS_IN_WAIT
/
===================================================================
act_wait_user.sql
select OSUSER, USERNAME, SID, PROCESS, SQL_ID, substr(EVENT,1,25) EVENT, SECONDS_IN_WAIT, MODULE from v$session where TYPE != 'BACKGROUND' and USERNAME not in('SYS','SYSTEM') and STATUS = 'ACTIVE' and SECONDS_IN_WAIT > &WAIT_TIME_IN_SECONDS order by SECONDS_IN_WAIT
/
===================================================================
advisor.sql
select af.task_name, ao.attr2 segname, ao.attr3 partition, ao.type, af.message
from dba_advisor_findings af, dba_advisor_objects ao
where ao.task_id = af.task_id
and ao.object_id = af.object_id
and ao.OWNER = 'SYS';
===================================================================
arch_hour.sql
select
to_char(a.first_time,'YYYY-MM-DD') "Date ",
to_char(sum(decode(substr(to_char(a.first_time,'HH24'),1,3),'00',(BLOCKS*BLOCK_SIZE/1024/1024/1024),0)),'9999') "00 ",
to_char(sum(decode(substr(to_char(a.first_time,'HH24'),1,3),'01',(BLOCKS*BLOCK_SIZE/1024/1024/1024),0)),'9999') "01 ",
to_char(sum(decode(substr(to_char(a.first_time,'HH24'),1,3),'02',(BLOCKS*BLOCK_SIZE/1024/1024/1024),0)),'9999') "02 ",
to_char(sum(decode(substr(to_char(a.first_time,'HH24'),1,3),'03',(BLOCKS*BLOCK_SIZE/1024/1024/1024),0)),'9999') "03 ",
to_char(sum(decode(substr(to_char(a.first_time,'HH24'),1,3),'04',BLOCKS*BLOCK_SIZE/1024/1024/1024,0)),'9999') "04 ",
to_char(sum(decode(substr(to_char(a.first_time,'HH24'),1,3),'05',BLOCKS*BLOCK_SIZE/1024/1024/1024,0)),'9999') "05 ",
to_char(sum(decode(substr(to_char(a.first_time,'HH24'),1,3),'06',BLOCKS*BLOCK_SIZE/1024/1024/1024,0)),'9999') "06 ",
to_char(sum(decode(substr(to_char(a.first_time,'HH24'),1,3),'07',BLOCKS*BLOCK_SIZE/1024/1024/1024,0)),'9999') "07 ",
to_char(sum(decode(substr(to_char(a.first_time,'HH24'),1,3),'08',BLOCKS*BLOCK_SIZE/1024/1024/1024,0)),'9999') "08 ",
to_char(sum(decode(substr(to_char(a.first_time,'HH24'),1,3),'09',BLOCKS*BLOCK_SIZE/1024/1024/1024,0)),'9999') "09 ",
to_char(sum(decode(substr(to_char(a.first_time,'HH24'),1,3),'10',BLOCKS*BLOCK_SIZE/1024/1024/1024,0)),'9999') "10 ",
to_char(sum(decode(substr(to_char(a.first_time,'HH24'),1,3),'11',BLOCKS*BLOCK_SIZE/1024/1024/1024,0)),'9999') "11 ",
to_char(sum(decode(substr(to_char(a.first_time,'HH24'),1,3),'12',BLOCKS*BLOCK_SIZE/1024/1024/1024,0)),'9999') "12 ",
to_char(sum(decode(substr(to_char(a.first_time,'HH24'),1,3),'13',BLOCKS*BLOCK_SIZE/1024/1024/1024,0)),'9999') "13 ",
to_char(sum(decode(substr(to_char(a.first_time,'HH24'),1,3),'14',BLOCKS*BLOCK_SIZE/1024/1024/1024,0)),'9999') "14 ",
to_char(sum(decode(substr(to_char(a.first_time,'HH24'),1,3),'15',BLOCKS*BLOCK_SIZE/1024/1024/1024,0)),'9999') "15 ",
to_char(sum(decode(substr(to_char(a.first_time,'HH24'),1,3),'16',BLOCKS*BLOCK_SIZE/1024/1024/1024,0)),'9999') "16 ",
to_char(sum(decode(substr(to_char(a.first_time,'HH24'),1,3),'17',BLOCKS*BLOCK_SIZE/1024/1024/1024,0)),'9999') "17 ",
to_char(sum(decode(substr(to_char(a.first_time,'HH24'),1,3),'18',BLOCKS*BLOCK_SIZE/1024/1024/1024,0)),'9999') "18 ",
to_char(sum(decode(substr(to_char(a.first_time,'HH24'),1,3),'19',BLOCKS*BLOCK_SIZE/1024/1024/1024,0)),'9999') "19 ",
to_char(sum(decode(substr(to_char(a.first_time,'HH24'),1,3),'20',BLOCKS*BLOCK_SIZE/1024/1024/1024,0)),'9999') "20 ",
to_char(sum(decode(substr(to_char(a.first_time,'HH24'),1,3),'21',BLOCKS*BLOCK_SIZE/1024/1024/1024,0)),'9999') "21 ",
to_char(sum(decode(substr(to_char(a.first_time,'HH24'),1,3),'22',BLOCKS*BLOCK_SIZE/1024/1024/1024,0)),'9999') "22 ",
to_char(sum(decode(substr(to_char(a.first_time,'HH24'),1,3),'23',BLOCKS*BLOCK_SIZE/1024/1024/1024,0)),'9999') "23 ",
to_char(sum(BLOCKS*BLOCK_SIZE/1024/1024/1024),'9999999.99') "TOTAL"
from v$archived_log a
where dest_id=1 and
trunc(a.first_time) >= trunc(sysdate-19)
group by to_char(a.first_time,'YYYY-MM-DD')
order by to_char(a.first_time,'YYYY-MM-DD');
===================================================================
asw.sql
SET LINES 188 PAGES 1000
COL EVENT FOR A33
COL P2TEXT FOR A14
COL P1TEXT FOR A25
COL P3TEXT FOR a25
COL SID FOR 999999
COL STATE FOR A10 TRUNC
COL USERNAME FOR A15
SELECT A.USERNAME, A.SQL_ID, EVENT, SECONDS_IN_WAIT SECS_WAIT, STATE, WAIT_TIME, P1, P2, P1TEXT, P2TEXT
FROM V$SESSION A, V$PROCESS B WHERE A.PADDR = B.ADDR
AND A.STATUS = 'ACTIVE' AND A.USERNAME IS NOT NULL
AND A.TYPE != 'BACKGROUND'
ORDER BY 3;
===================================================================
asw_history.sql
COL TOTAL_WAIT FOR 999999999999999;
SELECT EN.NAME WAIT_EVENT, SUM(ASH.WAIT_TIME + ASH.TIME_WAITED) TOTAL_WAIT FROM V$ACTIVE_SESSION_HISTORY ASH, V$EVENT_NAME EN WHERE ASH.EVENT_ID = EN.EVENT_ID AND EN.WAIT_CLASS <> 'IDLE' GROUP BY EN.NAME ORDER BY 2 DESC;
===================================================================
bind_cap.sql
select name||'|'|| value_string
from v$sql_bind_capture
where sql_id='&sqlid'
/
===================================================================
bind_history.sql
SELECT snap_id, NAME,POSITION,DATATYPE_STRING,VALUE_STRING,LAST_CAPTURED cdate
FROM DBA_HIST_SQLBIND WHERE SQL_ID='&sqlid'
-- and SNAP_ID='&sqlid'
order by snap_id desc
/
===================================================================
blocking.sql
select s1.username || '@' || s1.machine || ' ( SID=' || s1.sid || ' ) on Node ' ||s1.inst_id||' is blocking ' || s2.username || '@' || s2.machine || ' ( SID=' || s2.sid || ' ) on Node'||s2.inst_id AS blocking_status from gv$lock l1, gv$session s1, gv$lock l2, gv$session s2 where s1.sid=l1.sid and s2.sid=l2.sid and l2.request > 0 and l1.BLOCK <>0 and l1.id1 = l2.id1 and l2.id2 = l2.id2 and s2.username is not null and s1.username is not null
/
===================================================================
cf_en.sql
--holder of the CF enqueue
select l.sid, p.program, p.pid, p.spid, s.username, s.terminal, s.module, s.action, s.event, s.wait_time, s.seconds_in_wait, s.state
from gv$lock l, gv$session s, gv$process p
where l.sid = s.sid
and s.paddr = p.addr
and l.type='CF'
and l.lmode >= 5;
--session waiting to get the CF enqueue
select l.sid, p.program, p.pid, p.spid, s.username, s.terminal, s.module, s.action, s.event, s.wait_time, s.seconds_in_wait, s.state
from gv$lock l, gv$session s, gv$process p
where l.sid = s.sid
and s.paddr = p.addr
and l.type='CF'
and l.request >= 5;
===================================================================
cgo.sql
select owner,object_type,count(1) NO_OF_OBJECTS from dba_objects where owner in ('AUDITLOG','CONTENTSTORE','METRICSTORE','NOTIFICATION') group by object_type,owner order by owner
/
===================================================================
cgs.sql
select owner,segment_type,tablespace_name,sum(bytes)/1024/1024/1024 SIZE_IN_GB from dba_segments where owner in ('AUDITLOG','CONTENTSTORE','METRICSTORE','NOTIFICATION') group by owner,segment_type,tablespace_name order by 4
/
===================================================================
da.sql
select * from table ( dbms_xplan.display_awr('&sql_id'))
/
===================================================================
datafiles.sql
COL FILE_NAME FOR A90
SELECT FILE_ID, FILE_NAME, TABLESPACE_NAME, BYTES/1024/1024 "SIZE_IN_MB", STATUS, AUTOEXTENSIBLE FROM DBA_DATA_FILES ORDER BY 1;
===================================================================
dc.sql
select * from table ( dbms_xplan.display_cursor('&sql_id'))
/
===================================================================
dg.sql
select GROUP_NUMBER,NAME,STATE,TOTAL_MB/1024 TOTAL_GB,FREE_MB/1024 FREE_GB,HOT_USED_MB/1024 HOT_USED_GB, COLD_USED_MB/1024 COLD_USED_GB,REQUIRED_MIRROR_FREE_MB/1024 REQUIRED_MIRROR_FREE_GB,USABLE_FILE_MB/1024 USABLE_FILE_GB from v$asm_diskgroup
/
===================================================================
dict.sql
col comments for a70
select * from dict
where table_name like upper('%&ENTER%');
===================================================================
dict_stats.sql
exec dbms_stats.gather_dictionary_stats;
===================================================================
eglc.sql
COL MODULE FOR A20 TRUNC
COL INST_ID FOR 9999999
COL USERNAME FOR A20 TRUNC
COL SID FOR 99999999
COL MACHINE FOR A30 TRUNC
COL LAST_CALL_ET FOR 99999999
COL OPTIMIZER_COST FOR 999999999999999999999
COL OSUSER FOR A15
SELECT A.INST_ID,A.SID,A.SQL_ID,B.OPTIMIZER_COST,(SELECT COUNT(1) FROM GV$SESSION WHERE SQL_ID=A.SQL_ID AND INST_ID=A.INST_ID) SQL_COUNT, A.USERNAME,A.MACHINE,A.OSUSER,A.MODULE,A.LAST_CALL_ET/60 MINS FROM GV$SESSION A , GV$SQLAREA B WHERE A.SQL_ID=B.SQL_ID AND A.INST_ID=B.INST_ID AND A.STATUS = 'ACTIVE' AND A.USERNAME IS NOT NULL AND A.TYPE != 'BACKGROUND' ORDER BY 4,LAST_CALL_ET;
===================================================================
eglc_d.sql
COL MODULE FOR A20 TRUNC;
COL INST_ID FOR 9999999;
COL USERNAME FOR A10;
COL OSUSER FOR A12;
COL MACHINE FOR A15 TRUNC;
COL LAST_CALL_ET FOR 99999999;
COL EVENT FOR A35 TRUNC;
SELECT A.INST_ID, A.SQL_ID, (SELECT COUNT(1) FROM GV$SESSION WHERE SQL_ID=A.SQL_ID) SQL_COUNT, A.OSUSER, A.USERNAME, A.MACHINE, A.MODULE, A.EVENT, A.LAST_CALL_ET/60 mINS, A.SECONDS_IN_WAIT/60 WAIT_MINS FROM GV$SESSION A , GV$SQLAREA B WHERE A.SQL_ID=B.SQL_ID AND A.STATUS = 'ACTIVE' AND A.USERNAME IS NOT NULL AND A.TYPE != 'BACKGROUND' AND A.SQL_ID = '&SQL_ID' ORDER BY 3,LAST_CALL_ET;
===================================================================
elc.sql
COL MODULE FOR A20 TRUNC
COL USERNAME FOR A20 TRUNC
COL SID FOR 99999999
COL MACHINE FOR A30 TRUNC
COL LAST_CALL_ET FOR 99999999
COL OPTIMIZER_COST FOR 999999999999999999999
COL OSUSER FOR A15
SELECT A.SID,A.SQL_ID,B.OPTIMIZER_COST,(SELECT COUNT(1) FROM V$SESSION WHERE SQL_ID=A.SQL_ID) SQL_COUNT, A.OSUSER, A.USERNAME,A.MACHINE,A.MODULE,A.LAST_CALL_ET/60 MINs FROM V$SESSION A , V$SQLAREA B WHERE A.SQL_ID=B.SQL_ID AND A.STATUS = 'ACTIVE' AND A.USERNAME IS NOT NULL AND A.TYPE != 'BACKGROUND' ORDER BY 3,LAST_CALL_ET;
===================================================================
elc_d.sql
col module for a20 trunc
col username for a20 trunc
col schemaname for a20 trunc
col sid for 99999999
col last_call_et for 99999999
col osuser for a15
select a.sid,a.sql_id,b.optimizer_cost,(select count(1) from v$session where sql_id=a.sql_id) sql_count, a.osuser, a.username,a.machine,a.module,a.last_call_et/60/60 Hours from v$session a , v$sqlarea b where a.sql_id=b.sql_id and a.status = 'ACTIVE' and a.username is not null and a.type != 'BACKGROUND' and a.sql_id = '&SQL_ID' order by 3,last_call_et;
===================================================================
event.sql
COL MODULE FOR A40
COL OSUSER FOR A10
COL USERNAME FOR A8
SET PAGES 10000
COL SID FOR 99999999
COL SQL_CNT FOR 9999999
COL MACHINE FOR A10
COL LAST_CALL_ET FOR 999999
COL SPID FOR A6
COL APP_ID FOR A6
BREAK ON SQL_ID dup skip page
SELECT /*SATVVIK*/ VP.SPID, VS.SQL_ID, (SELECT OPTIMIZER_COST FROM V$SQLAREA WHERE SQL_ID=VS.SQL_ID) SQL_COST, (SELECT COUNT(1) FROM V$SESSION WHERE SQL_HASH_VALUE=VS.SQL_HASH_VALUE) SQL_COUNT, VS.USERNAME, TO_CHAR(VS.LOGON_TIME,'DD-MM HH24:MI:SS') LOGON_TIME, VS.OSUSER, VS.MODULE, VS.MACHINE, VS.LAST_CALL_ET FROM V$SESSION VS, V$PROCESS VP WHERE VS.PADDR = VP.ADDR AND VS.STATUS = 'ACTIVE' AND VS.USERNAME IS NOT NULL AND VS.TYPE <> 'BACKGROUND' AND VS.EVENT='&EVENT' ORDER BY 3,8;
===================================================================
exp_progress.sql
SELECT sid, opname, context, sofar*8192/1024/1024/1024 sofar_GB, totalwork*8192/1024/1024/1024 totalwork_GB, (totalwork-sofar)*8192/1024/1024/1024 pending_gb,
TIME_REMAINING/60 time_min, ELAPSED_SECONDS/60 elps_min, ELAPSED_SECONDS elpse_time, round(sofar/totalwork*100, 2) compl ,
round(sofar*8192/1024/1024/ELAPSED_SECONDS, 2) speed_MB_per_sec
FROM gv$session_longops
WHERE opname LIKE 'SYS_EXPORT_SCHEMA_01'
/*AND opname NOT LIKE '%aggregate%'*/
AND totalwork != 0
AND sofar <> totalwork
/
===================================================================
finalhw.sql
select final_blocking_Session,final_blocking_instance ,count(1) from gv$session group by final_blocking_Session,final_blocking_instance;
===================================================================
ftext.sql
set head off;
set long 99999999
select SQL_FULLTEXT from v$sqlarea where SQL_ID = '&SQL_ID';
set head on;
===================================================================
gact_wait_usr.sql
select gvs.INST_ID, gvs.OSUSER, gvs.USERNAME, gvs.SID, gvs.PROCESS, gvs.SQL_ID, substr(gvs.EVENT,1,25) EVENT, gvs.SECONDS_IN_WAIT, gvs.MODULE from gv$session gvs where gvs.TYPE != 'BACKGROUND' and gvs.USERNAME not in ('SYS','SYSTEM') and gvs.STATUS = 'ACTIVE' and gvs.SECONDS_IN_WAIT > &WAIT_TIME_IN_SECONDS order by gvs.INST_ID, gvs.SECONDS_IN_WAIT
/
===================================================================
gasw.sql
SET LINES 188 PAGES 1000
COL EVENT FOR A33
COL P2TEXT FOR A14
COL P1TEXT FOR A25
COL P3TEXT FOR A25
COL SID FOR 999999
COL STATE FOR A10 TRUNC
COL USERNAME FOR A15
SELECT A.INST_ID, A.USERNAME, A.SQL_ID, EVENT, SECONDS_IN_WAIT SECS_WAIT, STATE, WAIT_TIME, P1, P2, P1TEXT, P2TEXT
FROM GV$SESSION A, GV$PROCESS B WHERE A.PADDR = B.ADDR
AND A.INST_ID=B.INST_ID
AND A.STATUS = 'ACTIVE' AND A.USERNAME IS NOT NULL
AND A.TYPE != 'BACKGROUND'
ORDER BY 3;
===================================================================
gasw_history.sql
col TOTAL_WAIT for 999999999999999;
select en.name WAIT_EVENT, sum(ash.wait_time + ash.time_waited) TOTAL_WAIT from gv$active_session_history ash, gv$event_name en where ash.event_id = en.event_id and en.wait_class <> 'Idle' group by en.name order by 2 desc
/
===================================================================
gather_index_stats.sql
select 'exec dbms_stats.gather_index_stats('''||OWNER||''','''||INDEX_NAME||''', granularity => ''AUTO'', DEGREE => 32);' from dba_indexes where owner = '&OWNER' and index_name = '&INDEX_NAME';
===================================================================
gather_table_stats.sql
select 'exec dbms_stats.gather_table_stats('''||OWNER||''','''||TABLE_NAME||''',granularity => ''AUTO'',cascade => TRUE, DEGREE => 32);' from dba_tables where owner = '&OWNER' and table_name = '&TABLE_NAME';
===================================================================
gcom.sql
SELECT sid, opname, context, sofar*8192/1024/1024/1024 sofar_GB, totalwork*8192/1024/1024/1024 totalwork_GB, (totalwork-sofar)*8192/1024/1024/1024 pending_gb,
TIME_REMAINING/60 time_min, ELAPSED_SECONDS/60 elps_min, ELAPSED_SECONDS elpse_time, round(sofar/totalwork*100, 2) compl ,
round(sofar*8192/1024/1024/ELAPSED_SECONDS, 2) speed_MB_per_sec
FROM gv$session_longops
WHERE opname = '&opname'
/*AND opname NOT LIKE '%aggregate%'*/
AND totalwork != 0
AND sofar <> totalwork
/
===================================================================
gevent.sql
COL MODULE FOR A40
COL OSUSER FOR A10
COL USERNAME FOR A8
SET PAGES 10000
COL SID FOR 99999999
COL SQL_CNT FOR 9999999
COL MACHINE FOR A10
COL LAST_CALL_ET FOR 999999
COL SPID FOR A6
COL APP_ID FOR A6
BREAK ON SQL_ID dup skip page
SELECT /*SATVVIK*/ VP.SPID, VS.SQL_ID, (SELECT OPTIMIZER_COST FROM V$SQLAREA WHERE SQL_ID=VS.SQL_ID) SQL_COST, (SELECT COUNT(1) FROM V$SESSION WHERE SQL_HASH_VALUE=VS.SQL_HASH_VALUE) SQL_COUNT, VS.PROCESS APP_ID, VS.USERNAME, TO_CHAR(VS.LOGON_TIME,'DD-MM HH24:MI:SS') LOGON_TIME, VS.OSUSER, VS.MODULE, VS.MACHINE, VS.LAST_CALL_ET FROM GV$SESSION VS, GV$PROCESS VP WHERE VS.PADDR = VP.ADDR AND VS.STATUS = 'ACTIVE' AND VS.USERNAME IS NOT NULL AND VS.TYPE <> 'BACKGROUND' AND VS.EVENT='&EVENT' ORDER BY 3,8;
===================================================================
gftext.sql
set head off;
set long 99999999
select SQL_FULLTEXT from gv$sqlarea where SQL_ID = '&SQL_ID';
set head on;
===================================================================
ghw.sql
col HOLDER_SQL_PREVSQL_PROCESS for a40
col MODULE for a30
col OSUSER for a10
col USERNAME for a10
select inst_id,sid WAITER_SID, blocking_session HOLDER_SID ,
(select sql_id||'|'||prev_sql_id ||'|'||process from gv$session where sid=sess.blocking_session and rownum <=1) holder_sql_prevsql_process ,
sql_id WAITER_sqlid,seconds_in_wait secw, username,module,osuser,last_call_et/60 wait_mins
from gv$session sess
WHERE blocking_session IS NOT NULL
order by 2;
===================================================================
gkill_id.sql
SELECT 'ALTER SYSTEM KILL SESSION '''||SID||','||SERIAL#||',@'||INST_ID||''' IMMEDIATE;' FROM GV$SESSION WHERE SQL_ID='&SQL_ID';
===================================================================
gkill_sid.sql
SELECT 'ALTER SYSTEM KILL SESSION '''||SID||','||SERIAL#||',@'||inst_id||''' IMMEDIATE;' FROM GV$SESSION where (sid,inst_id) in (select sid,inst_id from gv$access where object='&obj' and owner='&owner');
===================================================================
glc.sql
COL MODULE FOR A40
COL OSUSER FOR A10
COL USERNAME FOR A8
SET PAGES 10000
COL SID FOR 99999999
COL SQL_CNT FOR 9999999
COL MACHINE FOR A10
COL LAST_CALL_ET FOR 999999
COL SPID FOR A6
COL APP_ID FOR A6
SELECT /*SATVVIK*/ VS.INST_ID, VP.SPID, VS.SQL_ID, (SELECT OPTIMIZER_COST FROM V$SQLAREA WHERE SQL_ID=VS.SQL_ID) SQL_COST, (SELECT COUNT(1) FROM V$SESSION WHERE SQL_HASH_VALUE=VS.SQL_HASH_VALUE) SQL_COUNT, VS.PROCESS APP_ID, VS.USERNAME, TO_CHAR(VS.LOGON_TIME,'DD-MM HH24:MI:SS') LOGON_TIME, VS.OSUSER, VS.MODULE, VS.MACHINE, VS.LAST_CALL_ET FROM GV$SESSION VS, GV$PROCESS VP WHERE VS.PADDR = VP.ADDR AND VS.STATUS = 'ACTIVE' AND VS.USERNAME IS NOT NULL AND VS.TYPE <> 'BACKGROUND' AND VS.SQL_ID IS NOT NULL ORDER BY 4,3,11;
===================================================================
glc_1.sql
SELECT /*SATVVIK*/ VS.INST_ID, VP.SPID, VS.SQL_ID, (SELECT OPTIMIZER_COST FROM V$SQLAREA WHERE SQL_ID=VS.SQL_ID) SQL_COST, (SELECT COUNT(1) FROM V$SESSION WHERE SQL_HASH_VALUE=VS.SQL_HASH_VALUE) SQL_COUNT, VS.PROCESS APP_ID, VS.USERNAME, TO_CHAR(VS.LOGON_TIME,'DD-MM HH24:MI:SS') LOGON_TIME, VS.OSUSER, VS.MODULE, VS.MACHINE, VS.LAST_CALL_ET FROM GV$SESSION VS, GV$PROCESS VP WHERE VS.PADDR = VP.ADDR AND VS.STATUS = 'ACTIVE' AND VS.USERNAME IS NOT NULL AND VS.TYPE <> 'BACKGROUND' AND VS.SQL_ID IS NOT NULL and vs.inst_id=1 ORDER BY 4,3,11
/
===================================================================
glc_d.sql
COL MODULE FOR A40
COL OSUSER FOR A10
COL USERNAME FOR A8
SET PAGES 10000
COL SID FOR 99999999
COL SQL_CNT FOR 9999999
COL MACHINE FOR A10
COL LAST_CALL_ET FOR 999999
COL SPID FOR A6
COL APP_ID FOR A6
SELECT /*SATVVIK*/ VP.SPID, VS.SQL_ID, (SELECT OPTIMIZER_COST FROM V$SQLAREA WHERE SQL_ID=VS.SQL_ID) SQL_COST, (SELECT COUNT(1) FROM V$SESSION WHERE SQL_HASH_VALUE=VS.SQL_HASH_VALUE) SQL_COUNT, VS.PROCESS APP_ID, VS.USERNAME, TO_CHAR(VS.LOGON_TIME,'DD-MM HH24:MI:SS') LOGON_TIME, VS.OSUSER, VS.MODULE, VS.MACHINE, VS.LAST_CALL_ET FROM GV$SESSION VS, GV$PROCESS VP WHERE VS.PADDR = VP.ADDR AND VS.STATUS = 'ACTIVE' AND VS.USERNAME IS NOT NULL AND VS.TYPE <> 'BACKGROUND' AND VS.SQL_ID = '&SQL_ID' ORDER BY 10,3,11;
===================================================================
glc_mach.sql
COL MODULE FOR A40
COL OSUSER FOR A10
COL USERNAME FOR A8
SET PAGES 10000
COL SID FOR 99999999
COL SQL_CNT FOR 9999999
COL MACHINE FOR A10
COL LAST_CALL_ET FOR 999999
COL SPID FOR A6
COL APP_ID FOR A6
BREAK ON MACHINE dup skip page
SELECT /*SATVVIK*/ VS.INST_ID, VP.SPID, VS.SQL_ID, (SELECT OPTIMIZER_COST FROM V$SQLAREA WHERE SQL_ID=VS.SQL_ID) SQL_COST, (SELECT COUNT(1) FROM V$SESSION WHERE SQL_HASH_VALUE=VS.SQL_HASH_VALUE) SQL_COUNT, VS.PROCESS APP_ID, VS.USERNAME, TO_CHAR(VS.LOGON_TIME,'DD-MM HH24:MI:SS') LOGON_TIME, VS.OSUSER, VS.MODULE, VS.MACHINE, VS.LAST_CALL_ET FROM GV$SESSION VS, GV$PROCESS VP WHERE VS.PADDR = VP.ADDR AND VS.STATUS = 'ACTIVE' AND VS.USERNAME IS NOT NULL AND VS.TYPE <> 'BACKGROUND' AND VS.SQL_ID IS NOT NULL ORDER BY 10,3,11;
===================================================================
gsql.sql
select distinct gv.sql_id from gv$session gv, gv$sql gs where gv.sql_id=gs.sql_id and gs.sql_text like '%&SAMPLE_SQL_TEXT%' and gv.status= 'ACTIVE' and gv.username <> 'SYS'
/
===================================================================
gvlock_1.sql
select
c.owner,
c.object_name,
c.object_type,
b.sid,
b.serial#,
b.status,
b.osuser,
b.machine
from
gv$locked_object a ,
gv$session b,
dba_objects c
where
b.sid = a.session_id
and
a.object_id = c.object_id
/
===================================================================
gvsk.sql
select 'alter system kill session '''||SID||','||SERIAL#||',@'||INST_ID||''' immediate;' from gv$session where (sid,inst_id) in (select sid,inst_id from gv$access where object='&obj' and owner='&owner')
/
===================================================================
gv_lock.sql
alter session set "_hash_join_enabled"=TRUE;
select object_id, object_name from dba_objects where object_id in (select object_id from gv$locked_object);
===================================================================
gv_sess_access.sql
col machine for a40
col username for a15
col osuser for a20
set lines 1000 pages 200
alter session set nls_date_format='DD-MM-YYYY hh24:mi:ss';
select inst_id,sid,serial#,sql_id,username,osuser,machine,module,status,logon_time from gv$session where (sid,inst_id) in (select sid,inst_id from gv$access where object='&obj' and owner='&owner')
/
===================================================================
high_cpu.sql
-- sessions with highest CPU consumption
SELECT s.sid, s.serial#, p.spid as "OS PID",s.username, s.module, st.value/100 as "CPU sec"
FROM v$sesstat st, v$statname sn, v$session s, v$process p
WHERE sn.name = 'CPU used by this session' -- CPU
AND st.statistic# = sn.statistic#
AND st.sid = s.sid
AND s.paddr = p.addr
AND s.last_call_et < 1800 -- active within last 1/2 hour
AND s.logon_time > (SYSDATE - 240/1440) -- sessions logged on within 4 hours
ORDER BY st.value;
===================================================================
hotblock.sql
COL OBJECT_NAME FOR A60;
SELECT INST_ID, COUNT(*), SQL_ID, NVL(O.OBJECT_NAME, ASH.CURRENT_OBJ#) OBJECT_NAME, SUBSTR(O.OBJECT_TYPE, 0,10) OBJECT_TYPE, CURRENT_FILE# FILE_NUMBER, CURRENT_BLOCK# BLOCK_NUMBER FROM GV$ACTIVE_SESSION_HISTORY ASH, DBA_OBJECTS O WHERE EVENT LIKE '%&EVENT%' AND O.OBJECT_ID (+) = ASH.CURRENT_OBJ# GROUP BY INST_ID, SQL_ID, CURRENT_OBJ#, CURRENT_FILE#, CURRENT_BLOCK#, O.OBJECT_NAME, O.OBJECT_TYPE ORDER BY 1,2,4;
===================================================================
hw.sql
col HOLDER_SQL_PREVSQL_PROCESS for a40
col MODULE for a30
col OSUSER for a10
col USERNAME for a10
select sid WAITER_SID, blocking_session HOLDER_SID,
(select sql_id||'|'||prev_sql_id ||'|'||process from v$session where sid=sess.blocking_session and rownum <=1) holder_sql_prevsql_process ,
sql_id WAITER_sqlid,seconds_in_wait secw, username,module,osuser,last_call_et/60 wait_mins
from v$session sess
WHERE blocking_session IS NOT NULL
order by 9;
===================================================================
imp_progress.sql
SELECT sid, opname, context, sofar*8192/1024/1024/1024 sofar_GB, totalwork*8192/1024/1024/1024 totalwork_GB, (totalwork-sofar)*8192/1024/1024/1024 pending_gb,
TIME_REMAINING/60 time_min, ELAPSED_SECONDS/60 elps_min, ELAPSED_SECONDS elpse_time, round(sofar/totalwork*100, 2) compl ,
round(sofar*8192/1024/1024/ELAPSED_SECONDS, 2) speed_MB_per_sec
FROM gv$session_longops
WHERE opname LIKE 'SYS_IMPORT_FULL_01'
/*AND opname NOT LIKE '%aggregate%'*/
AND totalwork != 0
AND sofar <> totalwork
/
===================================================================
ind.sql
select b.sid,a.sql_id,p.spid,round(sum(c.sofar)/sum(c.totalwork),3)*100 percentage from gv$sqlarea a,gv$session b,gv$session_longops c,gv$process p where /*c.totalwork <> c.sofar and */ c.sid =b.sid and b.paddr = p.addr and b.sql_hash_value=a.hash_value group by a.sql_id,p.spid,b.sid order by 4
/
===================================================================
indinfo.sql
COL OWNER FOR A20;
COL INDEX_NAME FOR A30;
COL INDEX_TYPE FOR A10;
COL TABLESPACE_NAME FOR A25;
COL DEGREE FOR A6
COL NUM_ROWS FOR 9999999999999;
SELECT OWNER, INDEX_NAME, INDEX_TYPE, PARTITIONED, COMPRESSION, TABLESPACE_NAME, NUM_ROWS, LAST_ANALYZED, DEGREE, STATUS FROM DBA_INDEXES WHERE TABLE_NAME = UPPER('&TABLE_NAME');
===================================================================
ind_column_info.sql
select
b.uniqueness, a.index_name,b.index_type, a.table_name, a.column_name
from all_ind_columns a, all_indexes b
where a.index_name=b.index_name
and a.table_name = upper('&table_name')
order by a.table_name, a.index_name, a.column_position
/
===================================================================
ind_progress.sql
SELECT sid, opname, context, sofar*8192/1024/1024/1024 sofar_GB, totalwork*8192/1024/1024/1024 totalwork_GB, (totalwork-sofar)*8192/1024/1024/1024 pending_gb,
TIME_REMAINING/60 time_min, ELAPSED_SECONDS/60 elps_min, ELAPSED_SECONDS elpse_time, round(sofar/totalwork*100, 2) compl ,
round(sofar*8192/1024/1024/ELAPSED_SECONDS, 2) speed_MB_per_sec
FROM gv$session_longops
WHERE opname = 'Sort Output'
/*AND opname NOT LIKE '%aggregate%'*/
AND totalwork != 0
AND sofar <> totalwork
/
===================================================================
ind_sid.sql
select b.sid,a.sql_id,p.spid,round(sum(c.sofar)/sum(c.totalwork),3)*100 percentage from gv$sqlarea a,gv$session b,gv$session_longops c,gv$process p where /*c.totalwork <> c.sofar and */ c.sid =b.sid and b.paddr = p.addr and b.sql_hash_value=a.hash_value and b.sid='&sid' group by a.sql_id,p.spid,b.sid order by 4
/
===================================================================
ind_sql.sql
select b.sid,a.sql_id,p.spid,round(sum(c.sofar)/sum(c.totalwork),3)*100 percentage from gv$sqlarea a,gv$session b,gv$session_longops c,gv$process p where /*c.totalwork <> c.sofar and */ c.sid =b.sid and b.paddr = p.addr and b.sql_hash_value=a.hash_value and a.sql_id='&sql_id' group by a.sql_id,p.spid,b.sid order by 4
/
===================================================================
killi_lock.sql
SELECT 'ALTER SYSTEM KILL SESSION ' ||''''||SID||','||' '||SERIAL#||''''||' '||'IMMEDIATE'||';' AS SESSION_KILL, LOGON_TIME, LAST_CALL_ET, SQL_ID, PREV_SQL_ID FROM V$SESSION WHERE SID IN (SELECT SESSION_ID FROM GV$LOCKED_OBJECT WHERE OBJECT_ID IN (SELECT OBJECT_ID FROM DBA_OBJECTS WHERE OBJECT_NAME='&OBJECT_NAME')) AND STATUS ='INACTIVE' AND LOGON_TIME < (SYSDATE - &TIME_IN_HRS/24) ORDER BY LOGON_TIME;
===================================================================
kill_id.sql
SELECT 'ALTER SYSTEM KILL SESSION '''||SID||','||SERIAL#||',@'||inst_id||''' IMMEDIATE;' FROM GV$SESSION WHERE username='&username';
===================================================================
kill_sql.sql
SELECT 'ALTER SYSTEM KILL SESSION '''||SID||','||SERIAL#||',@'||inst_id||''' IMMEDIATE;' FROM GV$SESSION WHERE sql_id='&sql_id';
===================================================================
pid.sql
select s.sid || ',' || s.serial# "SID/SERIAL"
, s.username
, s.osuser
, p.spid "OS PID"
, s.program
from gv$session s
, gv$process p
Where s.paddr = p.addr
and s.sid=1471
order by to_number(p.spid)
/
===================================================================
planc.sql
select ss.snap_id, ss.instance_number node, begin_interval_time, s.sql_id, s.plan_hash_value,
nvl(executions_delta,0) execs,
(elapsed_time_delta/decode(nvl(executions_delta,0),0,1,executions_delta))/1000000 avg_etime,
(buffer_gets_delta/decode(nvl(buffer_gets_delta,0),0,1,executions_delta)) avg_lio
from DBA_HIST_SQLSTAT S,
DBA_HIST_SNAPSHOT SS
where s.sql_id = nvl('&sql_id','4dqs2k5tynk61')
and ss.snap_id = S.snap_id
and ss.instance_number = S.instance_number
and executions_delta > 0 --and plan_hash_value=3099046451
order by 1, 2, 3;
===================================================================
resume.sql
select user_id, instance_id, status, name, error_msg, suspend_time, resume_time from dba_resumable;
===================================================================
rman_progress.sql
col opname for a35
break on CONTEXT on report
compute SUM of SOFAR_GB TOTALWORK_GB TIME_MIN on report
compute max of TIME_MIN on report
compute sum of PENDING_GB on report
SELECT sid, opname, context, sofar*8192/1024/1024/1024 sofar_GB, totalwork*8192/1024/1024/1024 totalwork_GB, (totalwork-sofar)*8192/1024/1024/1024 pending_gb,
TIME_REMAINING/60 time_min, ELAPSED_SECONDS/60 elps_min, ELAPSED_SECONDS elpse_time, round(sofar/totalwork*100, 2) compl ,
round(sofar*8192/1024/1024/ELAPSED_SECONDS, 2) speed_MB_per_sec
FROM gv$session_longops
WHERE opname LIKE '%RMAN%'
/*AND opname NOT LIKE '%aggregate%'*/
AND totalwork != 0
AND sofar <> totalwork;
===================================================================
rollback_status.sql
select state,
undoblocksdone,
undoblockstotal,
undoblocksdone / undoblockstotal * 100
from gv$fast_start_transactions
/
===================================================================
rollback_status1.sql
SELECT usn, state, undoblockstotal "Total", undoblocksdone "Done",
undoblockstotal-undoblocksdone "ToDo",
DECODE(cputime,0,'unknown',SYSDATE+(((undoblockstotal-undoblocksdone) / (undoblocksdone / cputime)) / 86400))
"Finish at"
FROM gv$fast_start_transactions
/
===================================================================
spid.sql
select s.inst_id, s.sid || ',' || s.serial# "SID/SERIAL"
, s.username
, s.osuser
, p.spid "OS PID"
, s.program
from gv$session s
, gv$process p
Where s.paddr = p.addr
and s.sid=&sid
order by to_number(p.spid)
/
===================================================================
sqld.sql
select sql_id,disk_Reads,buffer_gets,buffer_gets/executions,executions from gv$sqlarea where sql_id = '&sqlid';
===================================================================
stale_stats.sql
-- - - - - - - - - - - - - - - - - - - - - - - - - Script begins here - - - - - - - - - - - - - - - - - - - - - - - - - -
-- NAME: CHECK_STALE_STATS.SQL
-- Execute as SYS as sysdba
-- ----------------------------------------------------------------------------------------------------------------
-- AUTHOR:
-- Raja Ganesh - Oracle Support Services - DataServer Group
-- Copyright 2008, Oracle Corporation
-- -----------------------------------------------------------------------------------------------------------------
-- PURPOSE:
-- This script is an automated way to deal with stale statistics
-- operations that are required to be done as part of manual
-- upgrade OR when reported by DBUA.
--
-- This script will work in both Windows and Unix platforms from database
-- version 9.2 or higher.
-- ------------------------------------------------------------------------------------------------------------------
-- DISCLAIMER:
-- This script is provided for educational purposes only. It is NOT
-- supported by Oracle World Wide Technical Support.
-- The script has been tested and appears to work as intended.
-- You should always run new scripts on a test instance initially.
-- -------------------------------------------------------------------------------------------------------------------
SET FEEDBACK OFF
SET LINESIZE 250
SET SERVEROUTPUT ON
DECLARE
-- Variables declared
P_OTAB DBMS_STATS.OBJECTTAB;
MCOUNT NUMBER := 0;
P_VERSION VARCHAR2(10);
-- Cursor defined
CURSOR c1
IS
SELECT distinct schema
FROM dba_registry
ORDER by 1;
-- Beginning of the anonymous block
BEGIN
-- Verifying version from v$instance
SELECT version INTO p_version FROM v$instance;
DBMS_OUTPUT.PUT_LINE(chr(13));
-- Defining Loop 1 for listing schema which have stale stats
FOR x in c1
LOOP
DBMS_STATS.GATHER_SCHEMA_STATS(OWNNAME=>x.schema,OPTIONS=>'LIST STALE',OBJLIST=>p_otab);
-- Defining Loop 2 to find number of objects containing stale stats
FOR i in 1 .. p_otab.count
LOOP
IF p_otab(i).objname NOT LIKE 'SYS_%'
AND p_otab(i).objname NOT IN ('CLU$','COL_USAGE$','FET$','INDPART$',
'MON_MODS$','TABPART$','HISTGRM$',
'MON_MODS_ALL$',
'HIST_HEAD$','IN $','TAB$',
'WRI$_OPTSTAT_OPR','PUIU$DATA',
'XDB$NLOCKS_CHILD_NAME_IDX',
'XDB$NLOCKS_PARENT_OID_IDX',
'XDB$NLOCKS_RAWTOKEN_IDX', 'XDB$SCHEMA_URL',
'XDBHI_IDX', 'XDB_PK_H_LINK')
THEN
-- Incrementing count for each object found with statle stats
mcount := mcount + 1;
END IF;
-- End of Loop 2
END LOOP;
-- Displays no stale statistics, if coun is 0
IF mcount!=0
THEN
-- Displays Schema with stale stats if count is greater than 0
DBMS_OUTPUT.PUT_LINE(chr(13));
DBMS_OUTPUT.PUT_LINE('-------------------------------------------------------------------------------------------------------');
DBMS_OUTPUT.PUT_LINE('-- '|| x.schema || ' schema contains stale statistics use the following to gather the statistics '||'--');
DBMS_OUTPUT.PUT_LINE('-------------------------------------------------------------------------------------------------------');
-- Displays Command to be executed if schema with stale statistics is found depending on the version.
IF SUBSTR(p_version,1,5) in ('8.1.7','9.0.1','9.2.0')
THEN
DBMS_OUTPUT.PUT_LINE(chr(13));
DBMS_OUTPUT.PUT_LINE('EXEC DBMS_STATS.GATHER_SCHEMA_STATS('''||x.schema||''',OPTIONS=>'''||'GATHER STALE'||''', ESTIMATE_PERCENT => DBMS_STATS.AUTO_SAMPLE_SIZE, METHOD_OPT => '''||'FOR ALL COLUMNS SIZE AUTO'||''', CASCADE => TRUE);');
ELSIF SUBSTR(p_version,1,6) in ('10.1.0','10.2.0','11.1.0','11.2.0')
THEN
DBMS_OUTPUT.PUT_LINE(chr(13));
DBMS_OUTPUT.PUT_LINE('EXEC DBMS_STATS.GATHER_DICTIONARY_STATS('''||x.schema||''',OPTIONS=>'''||'GATHER STALE'||''', ESTIMATE_PERCENT => DBMS_STATS.AUTO_SAMPLE_SIZE, METHOD_OPT => '''||'FOR ALL COLUMNS SIZE AUTO'||''', CASCADE => TRUE);');
ELSE
DBMS_OUTPUT.PUT_LINE(chr(13));
DBMS_OUTPUT.PUT_LINE('Version is '||p_version);
END IF;
ELSE
DBMS_OUTPUT.PUT_LINE('-- There are no stale statistics in '|| x.schema || ' schema.');
DBMS_OUTPUT.PUT_LINE(chr(13));
END IF;
-- Reset count to 0.
mcount := 0;
-- End of Loop 1
END LOOP;
END;
/
SET FEEDBACK ON
-- - - - - - - - - - - - - - - - - - - - - - - - - Script ends here - - - - - - - - - - - - - - - - - - - - - - - - - -
===================================================================
sync_dc.sql
select THREAD#, max(SEQUENCE#), ARCHIVED, APPLIED, DEST_ID from v$archived_log where DEST_ID = 1 group by THREAD#, ARCHIVED, APPLIED, DEST_ID order by 1;
===================================================================
sync_dr.sql
select THREAD#, max(SEQUENCE#), ARCHIVED, APPLIED, DEST_ID from v$archived_log where DEST_ID = 2 and APPLIED = 'YES' group by THREAD#, ARCHIVED, APPLIED, DEST_ID order by 1;
===================================================================
syninfo.sql
SELECT OWNER, TABLE_OWNER, TABLE_NAME FROM DBA_SYNONYMS WHERE SYNONYM_NAME = '&SYNONYM_NAME';
===================================================================
tabinfo.sql
COL DEGREE FOR A10
SELECT OWNER, TABLE_NAME, PARTITIONED, COMPRESSION, TABLESPACE_NAME, NUM_ROWS, LAST_ANALYZED, DEGREE FROM DBA_TABLES WHERE TABLE_NAME = UPPER('&TABLE_NAME');
===================================================================
table_create.sql
SELECT sid, opname, context, sofar*8192/1024/1024/1024 sofar_GB, totalwork*8192/1024/1024/1024 totalwork_GB, (totalwork-sofar)*8192/1024/1024/1024 pending_gb,
TIME_REMAINING/60 time_min, ELAPSED_SECONDS/60 elps_min, ELAPSED_SECONDS elpse_time, round(sofar/totalwork*100, 2) compl ,
round(sofar*8192/1024/1024/ELAPSED_SECONDS, 2) speed_MB_per_sec
FROM gv$session_longops
WHERE opname in ('Table Scan','Hash Join')
/*AND opname NOT LIKE '%aggregate%'*/
AND totalwork != 0
AND sofar <> totalwork
/
===================================================================
tabpart.sql
SELECT TABLE_OWNER, TABLE_NAME, PARTITION_NAME, TABLESPACE_NAME, COMPRESSION, COMPRESS_FOR, SUM(BLOCKS*8192)/1024/1024/1024 "SIZE IN GB" FROM DBA_TAB_PARTITIONS WHERE TABLE_NAME = '&TABLE_NAME' GROUP BY TABLE_OWNER, TABLE_NAME, PARTITION_NAME, TABLESPACE_NAME, COMPRESSION, COMPRESS_FOR ORDER BY 3;
/
===================================================================
tabpart_d.sql
select table_owner, table_name, partition_name, compression, compress_for, (blocks*8192)/1024/1024/1024 GB from dba_tab_partitions where table_name = upper('&tabname') order by 3
/
===================================================================
tbs.sql
set line 160
set pagesize 50
col file_name for a80
col TABLESPACE_NAME for a25
select df.FILE_NAME,df.TABLESPACE_NAME,round(df.BYTES/1024/1024) Allocated,
round(nvl(dfs.BYTES/1024/1024,0)) FREE_SPACE, round(((df.BYTES/1024/1024)-nvl((dfs.BYTES/1024/1024),0))) USED_SPACE
from dba_data_files df,
(select file_id,tablespace_name,sum(bytes) bytes
from dba_free_space
group by file_id,tablespace_name) dfs
where df.FILE_ID=dfs.FILE_ID (+)
AND df.TABLESPACE_NAME=upper('&TABLESPACE_NAME')
order by free_space
--substr(df.FILE_NAME,-6,2)
/
===================================================================
tde_progress.sql
SELECT sid, opname, context, sofar*8192/1024/1024/1024 sofar_GB, totalwork*8192/1024/1024/1024 totalwork_GB, (totalwork-sofar)*8192/1024/1024/1024 pending_gb,
TIME_REMAINING/60 time_min, ELAPSED_SECONDS/60 elps_min, ELAPSED_SECONDS elpse_time, round(sofar/totalwork*100, 2) compl ,
round(sofar*8192/1024/1024/ELAPSED_SECONDS, 2) speed_MB_per_sec
FROM gv$session_longops
WHERE opname like 'TDE%'
/*AND opname NOT LIKE '%aggregate%'*/
AND totalwork != 0
AND sofar <> totalwork
/
===================================================================
tempfiles.sql
COL FILE_NAME FOR A60
SELECT FILE_ID, FILE_NAME, TABLESPACE_NAME, BYTES/1024/1024/1024 "SIZE IN GB" FROM DBA_TEMP_FILES ORDER BY 3;
===================================================================
tempfree.sql
SELECT TFS.TABLESPACE_NAME "TABLESPACE NAME", ROUND(TFS.TABLESPACE_SIZE/1024/1024/1024) "TABLESPACE SIZE", ROUND(TFS.ALLOCATED_SPACE/1024/1024/1024) "ALLOCATED SPACE", ROUND(SUM(TSH.BYTES_FREE/1024/1024/1024)) "FREE SPACE", ROUND(TFS.FREE_SPACE/1024/1024/1024) "ACTUAL FREE SPACE" FROM DBA_TEMP_FREE_SPACE TFS INNER JOIN V$TEMP_SPACE_HEADER TSH
ON TFS.TABLESPACE_NAME = TSH.TABLESPACE_NAME GROUP BY TFS.TABLESPACE_NAME,TFS.TABLESPACE_SIZE,TFS.ALLOCATED_SPACE,TFS.FREE_SPACE ORDER BY 1;
===================================================================
tempfree1.sql
select tablespace_name,round(sum(bytes_used/1024/1024/1024)) "Used_Space(MB)",round(sum(bytes_free/1024/1024/1024)) "Free_Space(GB)" from v$temp_space_header group by tablespace_name order by 1;******************************
===================================================================
temp_tbs.sql
select df.FILE_NAME,df.TABLESPACE_NAME,round(df.BYTES/1024/1024) Allocated,
round(nvl(dfs.BYTES/1024/1024,0)) FREE_SPACE, round(((df.BYTES/1024/1024)-nvl((dfs.BYTES/1024/1024),0))) USED_SPACE
from dba_temp_files df,
(select file_id,tablespace_name,sum(bytes) bytes
from dba_free_space
group by file_id,tablespace_name) dfs
where df.FILE_ID=dfs.FILE_ID (+)
AND df.TABLESPACE_NAME=upper('&TABLESPACE_NAME')
order by free_space
--substr(df.FILE_NAME,-6,2)
/
===================================================================
temp_usage.sql
col tablespace for a25
col EVENT for a35
col USERNAME a15
SELECT s.inst_id,s.sid,s.sql_id,t.TABLESPACE,s.event,s.username,SUM (T.blocks) * TBS.block_size / 1024 / 1024 mb_used, COUNT(*) statements FROM gv$sort_usage T, gv$session S, dba_tablespaces TBS WHERE T.session_addr = S.saddr AND T.tablespace = TBS.tablespace_name and T.inst_id=s.inst_id GROUP BY s.inst_id,s.sid,s.sql_id,s.event,s.username,T.blocks,TBS.block_size,t.tablespace order by 7
/
===================================================================
temp_usage1.sql
SELECT
A.tablespace_name tablespace,
D.mb_total,
SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_used,
D.mb_total - SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_free
FROM
gv$sort_segment A,
(
SELECT
B.name,
C.block_size,
SUM (C.bytes) / 1024 / 1024 mb_total
FROM
gv$tablespace B,
gv$tempfile C
WHERE
B.ts#= C.ts#
GROUP BY
B.name,
C.block_size
) D
WHERE
A.tablespace_name = D.name
GROUP by
A.tablespace_name,
D.mb_total
/
===================================================================
temp_util.sql
SELECT SUB.TABLESPACE_NAME, SUB.STATUS, SUB.EXTENT_MANAGEMENT, SUB.SEGMENT_SPACE_MANAGEMENT, SUB.TOTAL_SPACE_MB, SUB.USED_SPACE_MB, SUB.FREE_SPACE_MB, SUB.PERCENT_FREE_SPACE, SUB.CONTENTS FROM ( SELECT D.TABLESPACE_NAME,D.STATUS,D.EXTENT_MANAGEMENT,D.SEGMENT_SPACE_MANAGEMENT,ROUND((A.BYTES/1024/1024),2) TOTAL_SPACE_MB,ROUND(((A.BYTES-DECODE(F.BYTES,NULL,0,F.BYTES))/1024/1024),2) USED_SPACE_MB, ROUND(((A.BYTES/1024/1024)-(A.BYTES-DECODE(F.BYTES,NULL,0,F.BYTES))/1024/1024),2) FREE_SPACE_MB, DECODE(A.BYTES, 0, TO_NUMBER(NULL), ROUND((((A.BYTES/1024/1024)-(A.BYTES-DECODE(F.BYTES,NULL,0,F.BYTES))/1024/1024)/(A.BYTES/1024/1024))*100.0,2)) PERCENT_FREE_SPACE, D.CONTENTS FROM SYS.DBA_TABLESPACES D,(SELECT TABLESPACE_NAME, SUM(NVL(BYTES, 0)) BYTES FROM SYS.DBA_DATA_FILES GROUP BY TABLESPACE_NAME) A,SYS.SM$TS_FREE F WHERE D.TABLESPACE_NAME = A.TABLESPACE_NAME AND F.TABLESPACE_NAME(+)=D.TABLESPACE_NAME UNION ALL SELECT D.TABLESPACE_NAME,D.STATUS, D.EXTENT_MANAGEMENT,D.SEGMENT_SPACE_MANAGEMENT,ROUND((A.BYTES / 1024 / 1024),2) TOTAL_SPACE_MB, ROUND(NVL(T.BYTES, 0)/1024/1024,2) USED_SPACE_MB,ROUND((A.BYTES / 1024 / 1024) - (NVL(T.BYTES, 0)/1024/1024),2) FREE_SPACE_MB, DECODE(A.BYTES, 0, TO_NUMBER(NULL), ROUND(100 - (NVL(T.BYTES /A.BYTES * 100, 0)),2)) PERCENT_FREE_SPACE, D.CONTENTS FROM SYS.DBA_TABLESPACES D, (SELECT TABLESPACE_NAME, SUM(DECODE(BYTES, NULL, 0, BYTES)) BYTES FROM SYS.DBA_TEMP_FILES GROUP BY TABLESPACE_NAME) A, (SELECT TABLESPACE_NAME, SUM(DECODE(BYTES_CACHED, NULL, 0, BYTES_CACHED)) BYTES FROM SYS.V_$TEMP_EXTENT_POOL GROUP BY TABLESPACE_NAME) T WHERE D.TABLESPACE_NAME = A.TABLESPACE_NAME(+) AND D.TABLESPACE_NAME = T.TABLESPACE_NAME(+) AND D.EXTENT_MANAGEMENT LIKE 'LOCAL' AND D.CONTENTS LIKE 'TEMPORARY' ) SUB ORDER BY SUB.TABLESPACE_NAME
/
===================================================================
total_temp_usage.sql
select SUM (T.blocks) * TBS.block_size / 1024 / 1024 / 1024 "TEMP USAGE(GB)" FROM gv$sort_usage T, gv$session S, dba_tablespaces TBS WHERE T.session_addr = S.saddr AND T.tablespace = TBS.tablespace_name and T.inst_id=s.inst_id GROUP BY TBS.block_size;
===================================================================
try.sql
col TABLESPACE_NAME format a30
col TOTAL_SIZE_GB format 999999999.99
col FREE_SIZE_GB format 9999999999.99
col USED_SIZE_GB format 999999999.99
select df.tablespace_name TABLESPACE_NAME, df.bytes/(1024*1024*1024) TOTAL_SIZE_GB, (df.bytes/(1024*1024*1024) -sum(fs.bytes)/(1024*1024*1024)) USED_SIZE_GB, sum(fs.bytes)/(1024*1024*1024) FREE_SIZE_GB, round(sum(fs.bytes)*100/df.bytes) FREE_PERCENT, round((df.bytes-sum(fs.bytes))*100/df.bytes) USED_PERCENT from dba_free_space fs, (select tablespace_name, sum(bytes) bytes from dba_data_files group by tablespace_name ) df where fs.tablespace_name = df.tablespace_name group by df.tablespace_name, df.bytes order by USED_PERCENT;
===================================================================
ts_frag.sql
select sum(dfs.bytes)/1024/1024/1024 BYTES_GB from dba_free_space dfs where dfs.tablespace_name = '&&TABLESPACE_NAME' and dfs.bytes < (select min(ds.next_extent) from dba_segments ds where ds.tablespace_name = '&&TABLESPACE_NAME') order by dfs.block_id;
===================================================================
undo_seg.sql
select segment_name,
round(nvl(sum(act),0)/(1024*1024*1024),3 ) "ACT GB BYTES",
round(nvl(sum(unexp),0)/(1024*1024*1024),3) "UNEXP GB BYTES",
round(nvl(sum(exp),0)/(1024*1024*1024),3) "EXP GB BYTES",
NO_OF_EXTENTS
from ( select segment_name, nvl(sum(bytes),0) act,00 unexp, 00 exp, count(*) NO_OF_EXTENTS
from DBA_UNDO_EXTENTS
where status='ACTIVE' and tablespace_name = '&UNDO_TBS_NAME'
group by segment_name
union
select segment_name,00 act, nvl(sum(bytes),0) unexp, 00 exp , count(*) NO_OF_EXTENTS
from DBA_UNDO_EXTENTS
where status='UNEXPIRED' and tablespace_name = '&UNDO_TBS_NAME'
group by segment_name
union
select segment_name, 00 act, 00 unexp, nvl(sum(bytes),0) exp, count(*) NO_OF_EXTENTS
from DBA_UNDO_EXTENTS
where status='EXPIRED' and tablespace_name = '&UNDO_TBS_NAME'
group by segment_name
) group by segment_name, NO_OF_EXTENTS order by 5 desc
/
===================================================================
undo_stat.sql
select status, sum(bytes)/1024/1024/1024 GB
from dba_undo_extents
group by status
/
===================================================================
waitstat.sql
select inst_id,METRIC_NAME,VALUE from gv$sysmetric where METRIC_NAME like 'Database Wait Time%' order by inst_id
/
===================================================================
ws.sql
col event form a45
col wait_class form a15
col no_of_sessions form 999999
col avg_wait_time form 999999
col wait_time form 999999
select inst_id,event, wait_class, sum(SECONDS_IN_WAIT), sum(WAIT_TIME), count(1) no_of_sessions from gv$session_wait where wait_class <> 'Idle'
group by inst_id,event, wait_class
order by 1;
===================================================================
xplan.sql
select * from table (dbms_xplan.display);
===================================================================
