1.obj_lock script to find in oracle 10g
SELECT a.type,
Substr(a.owner,1,30) owner,
a.sid,
Substr(a.object,1,30) object
FROM v$access a
WHERE a.owner NOT IN ('SYS','PUBLIC')
ORDER BY 1,2,3,4
/
Substr(a.owner,1,30) owner,
a.sid,
Substr(a.object,1,30) object
FROM v$access a
WHERE a.owner NOT IN ('SYS','PUBLIC')
ORDER BY 1,2,3,4
/
2.non_indexed_fks script to find in oracle 10g
SET SERVEROUTPUT ON
SET PAGESIZE 1000
SET LINESIZE 255
SET FEEDBACK OFF
SELECT t.table_name,
c.constraint_name,
c.table_name table2,
acc.column_name
FROM all_constraints t,
all_constraints c,
all_cons_columns acc
WHERE c.r_constraint_name = t.constraint_name
AND c.table_name = acc.table_name
AND c.constraint_name = acc.constraint_name
AND NOT EXISTS (SELECT '1'
FROM all_ind_columns aid
WHERE aid.table_name = acc.table_name
AND aid.column_name = acc.column_name)
ORDER BY c.table_name;
SET PAGESIZE 1000
SET LINESIZE 255
SET FEEDBACK OFF
SELECT t.table_name,
c.constraint_name,
c.table_name table2,
acc.column_name
FROM all_constraints t,
all_constraints c,
all_cons_columns acc
WHERE c.r_constraint_name = t.constraint_name
AND c.table_name = acc.table_name
AND c.constraint_name = acc.constraint_name
AND NOT EXISTS (SELECT '1'
FROM all_ind_columns aid
WHERE aid.table_name = acc.table_name
AND aid.column_name = acc.column_name)
ORDER BY c.table_name;
3.monitor_memory script to find in oracle 10g
SET LINESIZE 200
COLUMN username FORMAT A20
COLUMN module FORMAT A20
SELECT NVL(a.username,'(oracle)') AS username,
a.module,
a.program,
Trunc(b.value/1024) AS memory_kb
FROM v$session a,
v$sesstat b,
v$statname c
WHERE a.sid = b.sid
AND b.statistic# = c.statistic#
AND c.name = 'session pga memory'
AND a.program IS NOT NULL
ORDER BY b.value DESC;
COLUMN username FORMAT A20
COLUMN module FORMAT A20
SELECT NVL(a.username,'(oracle)') AS username,
a.module,
a.program,
Trunc(b.value/1024) AS memory_kb
FROM v$session a,
v$sesstat b,
v$statname c
WHERE a.sid = b.sid
AND b.statistic# = c.statistic#
AND c.name = 'session pga memory'
AND a.program IS NOT NULL
ORDER BY b.value DESC;
4.monitor script to find in oracle 10g
SET VERIFY OFF
SET LINESIZE 255
COL SID FORMAT 999
COL STATUS FORMAT A8
COL PROCESS FORMAT A10
COL SCHEMANAME FORMAT A16
COL OSUSER FORMAT A16
COL SQL_TEXT FORMAT A120 HEADING 'SQL QUERY'
COL PROGRAM FORMAT A30
SELECT s.sid,
s.status,
s.process,
s.schemaname,
s.osuser,
a.sql_text,
p.program
FROM v$session s,
v$sqlarea a,
v$process p
WHERE s.SQL_HASH_VALUE = a.HASH_VALUE
AND s.SQL_ADDRESS = a.ADDRESS
AND s.PADDR = p.ADDR
/
SET LINESIZE 255
COL SID FORMAT 999
COL STATUS FORMAT A8
COL PROCESS FORMAT A10
COL SCHEMANAME FORMAT A16
COL OSUSER FORMAT A16
COL SQL_TEXT FORMAT A120 HEADING 'SQL QUERY'
COL PROGRAM FORMAT A30
SELECT s.sid,
s.status,
s.process,
s.schemaname,
s.osuser,
a.sql_text,
p.program
FROM v$session s,
v$sqlarea a,
v$process p
WHERE s.SQL_HASH_VALUE = a.HASH_VALUE
AND s.SQL_ADDRESS = a.ADDRESS
AND s.PADDR = p.ADDR
/
5.max_extents script to find in oracle 10g
SET LINESIZE 500
SET PAGESIZE 1000
SET VERIFY OFF
PROMPT
PROMPT Tables and Indexes nearing MAX_EXTENTS
PROMPT **************************************
SELECT e.owner,
e.segment_type,
Substr(e.segment_name, 1, 30) segment_name,
Trunc(s.initial_extent/1024) "INITIAL K",
Trunc(s.next_extent/1024) "NEXT K",
s.max_extents,
Count(*) as extents
FROM dba_extents e,
dba_segments s
WHERE e.owner = s.owner
AND e.segment_name = s.segment_name
AND e.owner NOT IN ('SYS', 'SYSTEM')
GROUP BY e.owner, e.segment_type, e.segment_name, s.initial_extent, s.next_extent, s.max_extents
HAVING Count(*) > s.max_extents - 10
ORDER BY e.owner, e.segment_type, Count(*) DESC;
SET PAGESIZE 1000
SET VERIFY OFF
PROMPT
PROMPT Tables and Indexes nearing MAX_EXTENTS
PROMPT **************************************
SELECT e.owner,
e.segment_type,
Substr(e.segment_name, 1, 30) segment_name,
Trunc(s.initial_extent/1024) "INITIAL K",
Trunc(s.next_extent/1024) "NEXT K",
s.max_extents,
Count(*) as extents
FROM dba_extents e,
dba_segments s
WHERE e.owner = s.owner
AND e.segment_name = s.segment_name
AND e.owner NOT IN ('SYS', 'SYSTEM')
GROUP BY e.owner, e.segment_type, e.segment_name, s.initial_extent, s.next_extent, s.max_extents
HAVING Count(*) > s.max_extents - 10
ORDER BY e.owner, e.segment_type, Count(*) DESC;
6.lru_latch_ratio script to find in oracle 10g
SET LINESIZE 500
SET PAGESIZE 1000
COLUMN "Ratio %" FORMAT 990.00
PROMPT
PROMPT Values greater than 3% indicate contention.
SELECT a.child#,
(a.SLEEPS / a.GETS) * 100 "Ratio %"
FROM v$latch_children a
WHERE a.name = 'cache buffers lru chain'
ORDER BY 1;
SET PAGESIZE 1000
COLUMN "Ratio %" FORMAT 990.00
PROMPT
PROMPT Values greater than 3% indicate contention.
SELECT a.child#,
(a.SLEEPS / a.GETS) * 100 "Ratio %"
FROM v$latch_children a
WHERE a.name = 'cache buffers lru chain'
ORDER BY 1;
7.longops script to find in oracle 10g
COLUMN sid FORMAT 999
COLUMN serial# FORMAT 9999999
COLUMN machine FORMAT A30
COLUMN progress_pct FORMAT 99999999.00
COLUMN elapsed FORMAT A10
COLUMN remaining FORMAT A10
SELECT s.sid,
s.serial#,
s.machine,
ROUND(sl.elapsed_seconds/60) || ':' || MOD(sl.elapsed_seconds,60) elapsed,
ROUND(sl.time_remaining/60) || ':' || MOD(sl.time_remaining,60) remaining,
ROUND(sl.sofar/sl.totalwork*100, 2) progress_pct
FROM v$session s,
v$session_longops sl
WHERE s.sid = sl.sid
AND s.serial# = sl.serial#;
COLUMN serial# FORMAT 9999999
COLUMN machine FORMAT A30
COLUMN progress_pct FORMAT 99999999.00
COLUMN elapsed FORMAT A10
COLUMN remaining FORMAT A10
SELECT s.sid,
s.serial#,
s.machine,
ROUND(sl.elapsed_seconds/60) || ':' || MOD(sl.elapsed_seconds,60) elapsed,
ROUND(sl.time_remaining/60) || ':' || MOD(sl.time_remaining,60) remaining,
ROUND(sl.sofar/sl.totalwork*100, 2) progress_pct
FROM v$session s,
v$session_longops sl
WHERE s.sid = sl.sid
AND s.serial# = sl.serial#;
8.locked_objects script to find in oracle 10g
SET LINESIZE 500
SET PAGESIZE 1000
SET VERIFY OFF
COLUMN owner FORMAT A20
COLUMN username FORMAT A20
COLUMN object_owner FORMAT A20
COLUMN object_name FORMAT A30
COLUMN locked_mode FORMAT A15
SELECT b.session_id AS sid,
NVL(b.oracle_username, '(oracle)') AS username,
a.owner AS object_owner,
a.object_name,
Decode(b.locked_mode, 0, 'None',
1, 'Null (NULL)',
2, 'Row-S (SS)',
3, 'Row-X (SX)',
4, 'Share (S)',
5, 'S/Row-X (SSX)',
6, 'Exclusive (X)',
b.locked_mode) locked_mode,
b.os_user_name
FROM dba_objects a,
v$locked_object b
WHERE a.object_id = b.object_id
ORDER BY 1, 2, 3, 4;
SET PAGESIZE 1000
SET VERIFY OFF
COLUMN owner FORMAT A20
COLUMN username FORMAT A20
COLUMN object_owner FORMAT A20
COLUMN object_name FORMAT A30
COLUMN locked_mode FORMAT A15
SELECT b.session_id AS sid,
NVL(b.oracle_username, '(oracle)') AS username,
a.owner AS object_owner,
a.object_name,
Decode(b.locked_mode, 0, 'None',
1, 'Null (NULL)',
2, 'Row-S (SS)',
3, 'Row-X (SX)',
4, 'Share (S)',
5, 'S/Row-X (SSX)',
6, 'Exclusive (X)',
b.locked_mode) locked_mode,
b.os_user_name
FROM dba_objects a,
v$locked_object b
WHERE a.object_id = b.object_id
ORDER BY 1, 2, 3, 4;
9.latch_hit_ratios script in oracle 10g
SET LINESIZE 200
COLUMN latch_hit_ratio FORMAT 990.00
SELECT l.name,
l.gets,
l.misses,
((1 - (l.misses / l.gets)) * 100) AS latch_hit_ratio
FROM v$latch l
WHERE l.gets != 0
UNION
SELECT l.name,
l.gets,
l.misses,
100 AS latch_hit_ratio
FROM v$latch l
WHERE l.gets = 0
ORDER BY 4 DESC;
COLUMN latch_hit_ratio FORMAT 990.00
SELECT l.name,
l.gets,
l.misses,
((1 - (l.misses / l.gets)) * 100) AS latch_hit_ratio
FROM v$latch l
WHERE l.gets != 0
UNION
SELECT l.name,
l.gets,
l.misses,
100 AS latch_hit_ratio
FROM v$latch l
WHERE l.gets = 0
ORDER BY 4 DESC;
10.invalid_objects script in oracle 10g
-- -----------------------------------------------------------------------------------
COLUMN object_name FORMAT A30
SELECT owner,
object_type,
object_name,
status
FROM dba_objects
WHERE status = 'INVALID'
ORDER BY owner, object_type, object_name;
COLUMN object_name FORMAT A30
SELECT owner,
object_type,
object_name,
status
FROM dba_objects
WHERE status = 'INVALID'
ORDER BY owner, object_type, object_name;
11.index_usage script in oracle 10g
SET VERIFY OFF
SET LINESIZE 200
SELECT table_name,
index_name,
used,
start_monitoring,
end_monitoring
FROM v$object_usage
WHERE table_name = UPPER('&1')
AND index_name = DECODE(UPPER('&2'), 'ALL', index_name, UPPER('&2'));
SET LINESIZE 200
SELECT table_name,
index_name,
used,
start_monitoring,
end_monitoring
FROM v$object_usage
WHERE table_name = UPPER('&1')
AND index_name = DECODE(UPPER('&2'), 'ALL', index_name, UPPER('&2'));
12.index_partitions script in oracle 10g
SET LINESIZE 500
SET PAGESIZE 1000
SET FEEDBACK OFF
SET VERIFY OFF
SELECT a.index_name,
a.partition_name,
a.tablespace_name,
a.initial_extent,
a.next_extent,
a.pct_increase,
a.num_rows
FROM dba_ind_partitions a
WHERE a.index_name = Decode(Upper('&&1'),'ALL',a.index_name,Upper('&&1'))
AND a.index_owner = Upper('&&2')
ORDER BY a.index_name, a.partition_name
/
SET PAGESIZE 1000
SET FEEDBACK OFF
SET VERIFY OFF
SELECT a.index_name,
a.partition_name,
a.tablespace_name,
a.initial_extent,
a.next_extent,
a.pct_increase,
a.num_rows
FROM dba_ind_partitions a
WHERE a.index_name = Decode(Upper('&&1'),'ALL',a.index_name,Upper('&&1'))
AND a.index_owner = Upper('&&2')
ORDER BY a.index_name, a.partition_name
/
13.index_monitoring_status script in oracle 10g
SET VERIFY OFF
SELECT table_name,
index_name,
monitoring
FROM v$object_usage
WHERE table_name = UPPER('&1')
AND index_name = DECODE(UPPER('&2'), 'ALL', index_name, UPPER('&2'));
SELECT table_name,
index_name,
monitoring
FROM v$object_usage
WHERE table_name = UPPER('&1')
AND index_name = DECODE(UPPER('&2'), 'ALL', index_name, UPPER('&2'));
14.index_extents script in oracle 10g
SET LINESIZE 500
SET PAGESIZE 1000
SET VERIFY OFF
SELECT i.index_name,
Count(e.segment_name) extents,
i.max_extents,
t.num_rows "ROWS",
Trunc(i.initial_extent/1024) "INITIAL K",
Trunc(i.next_extent/1024) "NEXT K",
t.table_name
FROM all_tables t,
all_indexes i,
dba_extents e
WHERE i.table_name = t.table_name
AND i.owner = t.owner
AND e.segment_name = i.index_name
AND e.owner = i.owner
AND i.table_name = Decode(Upper('&&1'),'ALL',i.table_name,Upper('&&1'))
AND i.owner = Upper('&&2')
GROUP BY t.table_name,
i.index_name,
i.max_extents,
t.num_rows,
i.initial_extent,
i.next_extent
HAVING Count(e.segment_name) > 5
ORDER BY Count(e.segment_name) DESC;
SET PAGESIZE 1000
SET VERIFY OFF
SELECT i.index_name,
Count(e.segment_name) extents,
i.max_extents,
t.num_rows "ROWS",
Trunc(i.initial_extent/1024) "INITIAL K",
Trunc(i.next_extent/1024) "NEXT K",
t.table_name
FROM all_tables t,
all_indexes i,
dba_extents e
WHERE i.table_name = t.table_name
AND i.owner = t.owner
AND e.segment_name = i.index_name
AND e.owner = i.owner
AND i.table_name = Decode(Upper('&&1'),'ALL',i.table_name,Upper('&&1'))
AND i.owner = Upper('&&2')
GROUP BY t.table_name,
i.index_name,
i.max_extents,
t.num_rows,
i.initial_extent,
i.next_extent
HAVING Count(e.segment_name) > 5
ORDER BY Count(e.segment_name) DESC;
15.identify_trace_file script in oracle 10g
SET LINESIZE 100
COLUMN trace_file FORMAT A60
SELECT s.sid,
s.serial#,
pa.value || '/' || LOWER(SYS_CONTEXT('userenv','instance_name')) ||
'_ora_' || p.spid || '.trc' AS trace_file
FROM v$session s,
v$process p,
v$parameter pa
WHERE pa.name = 'user_dump_dest'
AND s.paddr = p.addr
AND s.audsid = SYS_CONTEXT('USERENV', 'SESSIONID');
COLUMN trace_file FORMAT A60
SELECT s.sid,
s.serial#,
pa.value || '/' || LOWER(SYS_CONTEXT('userenv','instance_name')) ||
'_ora_' || p.spid || '.trc' AS trace_file
FROM v$session s,
v$process p,
v$parameter pa
WHERE pa.name = 'user_dump_dest'
AND s.paddr = p.addr
AND s.audsid = SYS_CONTEXT('USERENV', 'SESSIONID');
16.hot_blocks script in oracle 10g
SET LINESIZE 200
SET VERIFY OFF
SELECT *
FROM (SELECT name,
addr,
gets,
misses,
sleeps
FROM v$latch_children
WHERE name = 'cache buffers chains'
AND misses > 0
ORDER BY misses DESC)
WHERE rownum < 11;
ACCEPT address PROMPT "Enter ADDR: "
COLUMN owner FORMAT A15
COLUMN object_name FORMAT A30
COLUMN subobject_name FORMAT A20
SELECT *
FROM (SELECT o.owner,
o.object_name,
o.subobject_name,
bh.tch,
bh.obj,
bh.file#,
bh.dbablk,
bh.class,
bh.state
FROM x$bh bh,
dba_objects o
WHERE o.data_object_id = bh.obj
AND hladdr = '&address'
ORDER BY tch DESC)
WHERE rownum < 11;
SET VERIFY OFF
SELECT *
FROM (SELECT name,
addr,
gets,
misses,
sleeps
FROM v$latch_children
WHERE name = 'cache buffers chains'
AND misses > 0
ORDER BY misses DESC)
WHERE rownum < 11;
ACCEPT address PROMPT "Enter ADDR: "
COLUMN owner FORMAT A15
COLUMN object_name FORMAT A30
COLUMN subobject_name FORMAT A20
SELECT *
FROM (SELECT o.owner,
o.object_name,
o.subobject_name,
bh.tch,
bh.obj,
bh.file#,
bh.dbablk,
bh.class,
bh.state
FROM x$bh bh,
dba_objects o
WHERE o.data_object_id = bh.obj
AND hladdr = '&address'
ORDER BY tch DESC)
WHERE rownum < 11;
17.high_water_mark script in oracle 10g
SET SERVEROUTPUT ON
SET VERIFY OFF
DECLARE
CURSOR cu_tables IS
SELECT a.owner,
a.table_name
FROM all_tables a
WHERE a.table_name = Decode(Upper('&&1'),'ALL',a.table_name,Upper('&&1'))
AND a.owner = Upper('&&2');
op1 NUMBER;
op2 NUMBER;
op3 NUMBER;
op4 NUMBER;
op5 NUMBER;
op6 NUMBER;
op7 NUMBER;
BEGIN
Dbms_Output.Disable;
Dbms_Output.Enable(1000000);
Dbms_Output.Put_Line('TABLE UNUSED BLOCKS TOTAL BLOCKS HIGH WATER MARK');
Dbms_Output.Put_Line('------------------------------ --------------- --------------- ---------------');
FOR cur_rec IN cu_tables LOOP
Dbms_Space.Unused_Space(cur_rec.owner,cur_rec.table_name,'TABLE',op1,op2,op3,op4,op5,op6,op7);
Dbms_Output.Put_Line(RPad(cur_rec.table_name,30,' ') ||
LPad(op3,15,' ') ||
LPad(op1,15,' ') ||
LPad(Trunc(op1-op3-1),15,' '));
END LOOP;
END;
/
SET VERIFY OFF
DECLARE
CURSOR cu_tables IS
SELECT a.owner,
a.table_name
FROM all_tables a
WHERE a.table_name = Decode(Upper('&&1'),'ALL',a.table_name,Upper('&&1'))
AND a.owner = Upper('&&2');
op1 NUMBER;
op2 NUMBER;
op3 NUMBER;
op4 NUMBER;
op5 NUMBER;
op6 NUMBER;
op7 NUMBER;
BEGIN
Dbms_Output.Disable;
Dbms_Output.Enable(1000000);
Dbms_Output.Put_Line('TABLE UNUSED BLOCKS TOTAL BLOCKS HIGH WATER MARK');
Dbms_Output.Put_Line('------------------------------ --------------- --------------- ---------------');
FOR cur_rec IN cu_tables LOOP
Dbms_Space.Unused_Space(cur_rec.owner,cur_rec.table_name,'TABLE',op1,op2,op3,op4,op5,op6,op7);
Dbms_Output.Put_Line(RPad(cur_rec.table_name,30,' ') ||
LPad(op3,15,' ') ||
LPad(op1,15,' ') ||
LPad(Trunc(op1-op3-1),15,' '));
END LOOP;
END;
/
19.free_space script in oracle 10g
SET SERVEROUTPUT ON
SET PAGESIZE 1000
SET LINESIZE 255
SET FEEDBACK OFF
SELECT Substr(df.tablespace_name,1,20) "Tablespace Name",
Substr(df.file_name,1,40) "File Name",
Round(df.bytes/1024/1024,2) "Size (M)",
Round(e.used_bytes/1024/1024,2) "Used (M)",
Round(f.free_bytes/1024/1024,2) "Free (M)",
Rpad(' '|| Rpad ('X',Round(e.used_bytes*10/df.bytes,0), 'X'),11,'-') "% Used"
FROM DBA_DATA_FILES DF,
(SELECT file_id,
Sum(Decode(bytes,NULL,0,bytes)) used_bytes
FROM dba_extents
GROUP by file_id) E,
(SELECT Max(bytes) free_bytes,
file_id
FROM dba_free_space
GROUP BY file_id) f
WHERE e.file_id (+) = df.file_id
AND df.file_id = f.file_id (+)
ORDER BY df.tablespace_name,
df.file_name;
PROMPT
SET FEEDBACK ON
SET PAGESIZE 18
SET PAGESIZE 1000
SET LINESIZE 255
SET FEEDBACK OFF
SELECT Substr(df.tablespace_name,1,20) "Tablespace Name",
Substr(df.file_name,1,40) "File Name",
Round(df.bytes/1024/1024,2) "Size (M)",
Round(e.used_bytes/1024/1024,2) "Used (M)",
Round(f.free_bytes/1024/1024,2) "Free (M)",
Rpad(' '|| Rpad ('X',Round(e.used_bytes*10/df.bytes,0), 'X'),11,'-') "% Used"
FROM DBA_DATA_FILES DF,
(SELECT file_id,
Sum(Decode(bytes,NULL,0,bytes)) used_bytes
FROM dba_extents
GROUP by file_id) E,
(SELECT Max(bytes) free_bytes,
file_id
FROM dba_free_space
GROUP BY file_id) f
WHERE e.file_id (+) = df.file_id
AND df.file_id = f.file_id (+)
ORDER BY df.tablespace_name,
df.file_name;
PROMPT
SET FEEDBACK ON
SET PAGESIZE 18
20.fks script in oracle 10g
PROMPT
SET VERIFY OFF
SET FEEDBACK OFF
SET LINESIZE 255
SET PAGESIZE 1000
PROMPT
PROMPT Constraints Owned By Table
PROMPT ==========================
SELECT c.constraint_name "Constraint",
Decode(c.constraint_type,'P','Primary Key',
'U','Unique Key',
'C','Check',
'R','Foreign Key',
c.constraint_type) "Type",
c.r_owner "Ref Table",
c.r_constraint_name "Ref Constraint"
FROM all_constraints c
WHERE c.table_name = Upper('&&1')
AND c.owner = Upper('&&2');
PROMPT
PROMPT Constraints Referencing Table
PROMPT =============================
SELECT c1.table_name "Table",
c1.constraint_name "Foreign Key",
c1.r_constraint_name "References"
FROM all_constraints c1
WHERE c1.owner = Upper('&&2')
AND c1.r_constraint_name IN (SELECT c2.constraint_name
FROM all_constraints c2
WHERE c2.table_name = Upper('&&1')
AND c2.owner = Upper('&&2')
AND c2.constraint_type IN ('P','U'));
SET VERIFY ON
SET FEEDBACK ON
SET PAGESIZE 1000
PROMPT
SET VERIFY OFF
SET FEEDBACK OFF
SET LINESIZE 255
SET PAGESIZE 1000
PROMPT
PROMPT Constraints Owned By Table
PROMPT ==========================
SELECT c.constraint_name "Constraint",
Decode(c.constraint_type,'P','Primary Key',
'U','Unique Key',
'C','Check',
'R','Foreign Key',
c.constraint_type) "Type",
c.r_owner "Ref Table",
c.r_constraint_name "Ref Constraint"
FROM all_constraints c
WHERE c.table_name = Upper('&&1')
AND c.owner = Upper('&&2');
PROMPT
PROMPT Constraints Referencing Table
PROMPT =============================
SELECT c1.table_name "Table",
c1.constraint_name "Foreign Key",
c1.r_constraint_name "References"
FROM all_constraints c1
WHERE c1.owner = Upper('&&2')
AND c1.r_constraint_name IN (SELECT c2.constraint_name
FROM all_constraints c2
WHERE c2.table_name = Upper('&&1')
AND c2.owner = Upper('&&2')
AND c2.constraint_type IN ('P','U'));
SET VERIFY ON
SET FEEDBACK ON
SET PAGESIZE 1000
PROMPT
21.file_io script in oracle 10g
SET PAGESIZE 1000
SELECT Substr(d.name,1,50) "File Name",
f.phyblkrd "Blocks Read",
f.phyblkwrt "Blocks Writen",
f.phyblkrd + f.phyblkwrt "Total I/O"
FROM v$filestat f,
v$datafile d
WHERE d.file# = f.file#
ORDER BY f.phyblkrd + f.phyblkwrt DESC;
SET PAGESIZE 18
SELECT Substr(d.name,1,50) "File Name",
f.phyblkrd "Blocks Read",
f.phyblkwrt "Blocks Writen",
f.phyblkrd + f.phyblkwrt "Total I/O"
FROM v$filestat f,
v$datafile d
WHERE d.file# = f.file#
ORDER BY f.phyblkrd + f.phyblkwrt DESC;
SET PAGESIZE 18
22.explain script in oracle 10g
SET PAGESIZE 100
SET LINESIZE 200
SET VERIFY OFF
COLUMN plan FORMAT A50
COLUMN object_name FORMAT A30
COLUMN object_type FORMAT A15
COLUMN bytes FORMAT 9999999999
COLUMN cost FORMAT 9999999
COLUMN partition_start FORMAT A20
COLUMN partition_stop FORMAT A20
SELECT LPAD(' ', 2 * (level - 1)) ||
DECODE (level,1,NULL,level-1 || '.' || pt.position || ' ') ||
INITCAP(pt.operation) ||
DECODE(pt.options,NULL,'',' (' || INITCAP(pt.options) || ')') plan,
pt.object_name,
pt.object_type,
pt.bytes,
pt.cost,
pt.partition_start,
pt.partition_stop
FROM plan_table pt
START WITH pt.id = 0
AND pt.statement_id = '&1'
SET LINESIZE 200
SET VERIFY OFF
COLUMN plan FORMAT A50
COLUMN object_name FORMAT A30
COLUMN object_type FORMAT A15
COLUMN bytes FORMAT 9999999999
COLUMN cost FORMAT 9999999
COLUMN partition_start FORMAT A20
COLUMN partition_stop FORMAT A20
SELECT LPAD(' ', 2 * (level - 1)) ||
DECODE (level,1,NULL,level-1 || '.' || pt.position || ' ') ||
INITCAP(pt.operation) ||
DECODE(pt.options,NULL,'',' (' || INITCAP(pt.options) || ')') plan,
pt.object_name,
pt.object_type,
pt.bytes,
pt.cost,
pt.partition_start,
pt.partition_stop
FROM plan_table pt
START WITH pt.id = 0
AND pt.statement_id = '&1'
23.error script in oracle 10g
SELECT To_Char(a.line) || ' - ' || a.text error
FROM user_source a,
user_errors b
WHERE a.name = Upper('&&1')
AND a.name = b.name
AND a.type = b.type
AND a.line = b.line
ORDER BY a.name, a.line;
FROM user_source a,
user_errors b
WHERE a.name = Upper('&&1')
AND a.name = b.name
AND a.type = b.type
AND a.line = b.line
ORDER BY a.name, a.line;
24.error_stack script in oracle 10g
SET SERVEROUTPUT ON
DECLARE
v_stack VARCHAR2(2000);
BEGIN
v_stack := Dbms_Utility.Format_Error_Stack;
Dbms_Output.Put_Line(v_stack);
END;
/
DECLARE
v_stack VARCHAR2(2000);
BEGIN
v_stack := Dbms_Utility.Format_Error_Stack;
Dbms_Output.Put_Line(v_stack);
END;
/
25.dispatchers script in oracle 10g
SET LINESIZE 500
SET PAGESIZE 1000
SET VERIFY OFF
SELECT a.name "Name",
a.status "Status",
a.accept "Accept",
a.messages "Total Mesgs",
a.bytes "Total Bytes",
a.owned "Circs Owned",
a.idle "Total Idle Time",
a.busy "Total Busy Time",
Round(a.busy/(a.busy + a.idle),2) "Load"
FROM v$dispatcher a
ORDER BY 1;
SET PAGESIZE 14
SET VERIFY ON
SET PAGESIZE 1000
SET VERIFY OFF
SELECT a.name "Name",
a.status "Status",
a.accept "Accept",
a.messages "Total Mesgs",
a.bytes "Total Bytes",
a.owned "Circs Owned",
a.idle "Total Idle Time",
a.busy "Total Busy Time",
Round(a.busy/(a.busy + a.idle),2) "Load"
FROM v$dispatcher a
ORDER BY 1;
SET PAGESIZE 14
SET VERIFY ON
26.db_info script in oracle 10g
SET PAGESIZE 1000
SET LINESIZE 100
SET FEEDBACK OFF
SELECT *
FROM v$database;
SELECT *
FROM v$instance;
SELECT *
FROM v$version;
SELECT a.name,
a.value
FROM v$sga a;
SELECT Substr(c.name,1,60) "Controlfile",
NVL(c.status,'UNKNOWN') "Status"
FROM v$controlfile c
ORDER BY 1;
SELECT Substr(d.name,1,60) "Datafile",
NVL(d.status,'UNKNOWN') "Status",
d.enabled "Enabled",
LPad(To_Char(Round(d.bytes/1024000,2),'9999990.00'),10,' ') "Size (M)"
FROM v$datafile d
ORDER BY 1;
SELECT l.group# "Group",
Substr(l.member,1,60) "Logfile",
NVL(l.status,'UNKNOWN') "Status"
FROM v$logfile l
ORDER BY 1,2;
PROMPT
SET PAGESIZE 14
SET FEEDBACK ON
SET LINESIZE 100
SET FEEDBACK OFF
SELECT *
FROM v$database;
SELECT *
FROM v$instance;
SELECT *
FROM v$version;
SELECT a.name,
a.value
FROM v$sga a;
SELECT Substr(c.name,1,60) "Controlfile",
NVL(c.status,'UNKNOWN') "Status"
FROM v$controlfile c
ORDER BY 1;
SELECT Substr(d.name,1,60) "Datafile",
NVL(d.status,'UNKNOWN') "Status",
d.enabled "Enabled",
LPad(To_Char(Round(d.bytes/1024000,2),'9999990.00'),10,' ') "Size (M)"
FROM v$datafile d
ORDER BY 1;
SELECT l.group# "Group",
Substr(l.member,1,60) "Logfile",
NVL(l.status,'UNKNOWN') "Status"
FROM v$logfile l
ORDER BY 1,2;
PROMPT
SET PAGESIZE 14
SET FEEDBACK ON
27.db_cache_advice script in oracle 10g
COLUMN size_for_estimate FORMAT 999,999,999,999 heading 'Cache Size (MB)'
COLUMN buffers_for_estimate FORMAT 999,999,999 heading 'Buffers'
COLUMN estd_physical_read_factor FORMAT 999.90 heading 'Estd Phys|Read Factor'
COLUMN estd_physical_reads FORMAT 999,999,999 heading 'Estd Phys| Reads'
SELECT size_for_estimate,
buffers_for_estimate,
estd_physical_read_factor,
estd_physical_reads
FROM v$db_cache_advice
WHERE name = 'DEFAULT'
AND block_size = (SELECT value
FROM v$parameter
WHERE name = 'db_block_size')
AND advice_status = 'ON';
COLUMN buffers_for_estimate FORMAT 999,999,999 heading 'Buffers'
COLUMN estd_physical_read_factor FORMAT 999.90 heading 'Estd Phys|Read Factor'
COLUMN estd_physical_reads FORMAT 999,999,999 heading 'Estd Phys| Reads'
SELECT size_for_estimate,
buffers_for_estimate,
estd_physical_read_factor,
estd_physical_reads
FROM v$db_cache_advice
WHERE name = 'DEFAULT'
AND block_size = (SELECT value
FROM v$parameter
WHERE name = 'db_block_size')
AND advice_status = 'ON';
28.column_defaults script in oracle 10g
SET LINESIZE 100
SET VERIFY OFF
SELECT a.column_name "Column",
a.data_default "Default"
FROM all_tab_columns a
WHERE a.table_name = Upper('&1')
AND a.data_default IS NOT NULL
/
SET VERIFY OFF
SELECT a.column_name "Column",
a.data_default "Default"
FROM all_tab_columns a
WHERE a.table_name = Upper('&1')
AND a.data_default IS NOT NULL
/
29.script to find in oracle 10g - code_dep_on
SET VERIFY OFF
SET FEEDBACK OFF
SET LINESIZE 255
SET PAGESIZE 1000
BREAK ON type SKIP 1
PROMPT
SELECT a.type,
SUBSTR(a.owner,1,10) AS owner,
a.name
FROM all_dependencies a
WHERE a.referenced_name = UPPER('&1')
AND a.referenced_owner = DECODE(UPPER('&2'), 'ALL', a.referenced_owner, UPPER('&2'))
ORDER BY 1,2,3;
SET VERIFY ON
SET FEEDBACK ON
SET PAGESIZE 22
PROMPT
SET FEEDBACK OFF
SET LINESIZE 255
SET PAGESIZE 1000
BREAK ON type SKIP 1
PROMPT
SELECT a.type,
SUBSTR(a.owner,1,10) AS owner,
a.name
FROM all_dependencies a
WHERE a.referenced_name = UPPER('&1')
AND a.referenced_owner = DECODE(UPPER('&2'), 'ALL', a.referenced_owner, UPPER('&2'))
ORDER BY 1,2,3;
SET VERIFY ON
SET FEEDBACK ON
SET PAGESIZE 22
PROMPT
30.Script to find in oracle 10g - code_dep
SET VERIFY OFF
SET FEEDBACK OFF
SET LINESIZE 255
SET PAGESIZE 1000
BREAK ON type SKIP 1
PROMPT
SELECT a.referenced_type AS type,
SUBSTR(a.referenced_owner,1,10) AS ref_owner,
a.referenced_name AS ref_name,
SUBSTR(a.referenced_link_name,1,20) AS ref_link_name
FROM all_dependencies a
WHERE a.name = Upper('&1')
AND a.owner = DECODE(UPPER('&2'), 'ALL', a.referenced_owner, UPPER('&2'))
ORDER BY 1,2,3;
SET VERIFY ON
SET FEEDBACK ON
SET PAGESIZE 22
PROMPT
SET FEEDBACK OFF
SET LINESIZE 255
SET PAGESIZE 1000
BREAK ON type SKIP 1
PROMPT
SELECT a.referenced_type AS type,
SUBSTR(a.referenced_owner,1,10) AS ref_owner,
a.referenced_name AS ref_name,
SUBSTR(a.referenced_link_name,1,20) AS ref_link_name
FROM all_dependencies a
WHERE a.name = Upper('&1')
AND a.owner = DECODE(UPPER('&2'), 'ALL', a.referenced_owner, UPPER('&2'))
ORDER BY 1,2,3;
SET VERIFY ON
SET FEEDBACK ON
SET PAGESIZE 22
PROMPT
31.Script to find in oracle 10g - call_stack
SET SERVEROUTPUT ON
DECLARE
v_stack VARCHAR2(2000);
BEGIN
v_stack := Dbms_Utility.Format_Call_Stack;
Dbms_Output.Put_Line(v_stack);
END;
/
DECLARE
v_stack VARCHAR2(2000);
BEGIN
v_stack := Dbms_Utility.Format_Call_Stack;
Dbms_Output.Put_Line(v_stack);
END;
/
32.Script to find in oracle 10g - cache_hit_ratio
PROMPT
PROMPT Hit ratio should exceed 89%
SELECT Sum(Decode(a.name, 'consistent gets', a.value, 0)) "Consistent Gets",
Sum(Decode(a.name, 'db block gets', a.value, 0)) "DB Block Gets",
Sum(Decode(a.name, 'physical reads', a.value, 0)) "Physical Reads",
Round(((Sum(Decode(a.name, 'consistent gets', a.value, 0)) +
Sum(Decode(a.name, 'db block gets', a.value, 0)) -
Sum(Decode(a.name, 'physical reads', a.value, 0)) )/
(Sum(Decode(a.name, 'consistent gets', a.value, 0)) +
Sum(Decode(a.name, 'db block gets', a.value, 0))))
*100,2) "Hit Ratio %"
FROM v$sysstat a;
PROMPT Hit ratio should exceed 89%
SELECT Sum(Decode(a.name, 'consistent gets', a.value, 0)) "Consistent Gets",
Sum(Decode(a.name, 'db block gets', a.value, 0)) "DB Block Gets",
Sum(Decode(a.name, 'physical reads', a.value, 0)) "Physical Reads",
Round(((Sum(Decode(a.name, 'consistent gets', a.value, 0)) +
Sum(Decode(a.name, 'db block gets', a.value, 0)) -
Sum(Decode(a.name, 'physical reads', a.value, 0)) )/
(Sum(Decode(a.name, 'consistent gets', a.value, 0)) +
Sum(Decode(a.name, 'db block gets', a.value, 0))))
*100,2) "Hit Ratio %"
FROM v$sysstat a;
33.Script to find in oracle 10g - active_sessions
SET LINESIZE 500
SET PAGESIZE 1000
COLUMN username FORMAT A15
COLUMN machine FORMAT A25
COLUMN logon_time FORMAT A20
SELECT NVL(s.username, '(oracle)') AS username,
s.osuser,
s.sid,
s.serial#,
p.spid,
s.lockwait,
s.status,
s.module,
s.machine,
s.program,
TO_CHAR(s.logon_Time,'DD-MON-YYYY HH24:MI:SS') AS logon_time
FROM v$session s,
v$process p
WHERE s.paddr = p.addr
AND s.status = 'ACTIVE'
ORDER BY s.username, s.osuser;
SET PAGESIZE 1000
COLUMN username FORMAT A15
COLUMN machine FORMAT A25
COLUMN logon_time FORMAT A20
SELECT NVL(s.username, '(oracle)') AS username,
s.osuser,
s.sid,
s.serial#,
p.spid,
s.lockwait,
s.status,
s.module,
s.machine,
s.program,
TO_CHAR(s.logon_Time,'DD-MON-YYYY HH24:MI:SS') AS logon_time
FROM v$session s,
v$process p
WHERE s.paddr = p.addr
AND s.status = 'ACTIVE'
ORDER BY s.username, s.osuser;
34.Script to find in oracle 10g - access
SET SERVEROUTPUT ON
SET PAGESIZE 1000
SET LINESIZE 255
SET VERIFY OFF
SELECT Substr(a.object,1,30) object,
a.type,
a.sid,
b.username,
b.osuser,
b.program
FROM v$access a,
v$session b
WHERE a.sid = b.sid
AND a.owner = Upper('&1');
PROMPT
SET PAGESIZE 18
SET PAGESIZE 1000
SET LINESIZE 255
SET VERIFY OFF
SELECT Substr(a.object,1,30) object,
a.type,
a.sid,
b.username,
b.osuser,
b.program
FROM v$access a,
v$session b
WHERE a.sid = b.sid
AND a.owner = Upper('&1');
PROMPT
SET PAGESIZE 18
35.Script to find in oracle 10g - windows
SET LINESIZE 250
COLUMN comments FORMAT A40
SELECT window_name,
resource_plan,
enabled,
active,
comments
FROM dba_scheduler_windows
ORDER BY window_name;
COLUMN comments FORMAT A40
SELECT window_name,
resource_plan,
enabled,
active,
comments
FROM dba_scheduler_windows
ORDER BY window_name;
36.Script to find in oracle 10g - window_groups
SET LINESIZE 250
COLUMN comments FORMAT A40
SELECT window_group_name,
enabled,
number_of_windows,
comments
FROM dba_scheduler_window_groups
ORDER BY window_group_name;
SELECT window_group_name,
window_name
FROM dba_scheduler_wingroup_members
ORDER BY window_group_name, window_name;
COLUMN comments FORMAT A40
SELECT window_group_name,
enabled,
number_of_windows,
comments
FROM dba_scheduler_window_groups
ORDER BY window_group_name;
SELECT window_group_name,
window_name
FROM dba_scheduler_wingroup_members
ORDER BY window_group_name, window_name;
37.Script to find in oracle 10g - session_waits
SET LINESIZE 200
SET PAGESIZE 1000
COLUMN username FORMAT A20
COLUMN event FORMAT A30
COLUMN wait_class FORMAT A15
SELECT NVL(s.username, '(oracle)') AS username,
s.sid,
s.serial#,
sw.event,
sw.wait_class,
sw.wait_time,
sw.seconds_in_wait,
sw.state
FROM v$session_wait sw,
v$session s
WHERE s.sid = sw.sid
ORDER BY sw.seconds_in_wait DESC;
SET PAGESIZE 1000
COLUMN username FORMAT A20
COLUMN event FORMAT A30
COLUMN wait_class FORMAT A15
SELECT NVL(s.username, '(oracle)') AS username,
s.sid,
s.serial#,
sw.event,
sw.wait_class,
sw.wait_time,
sw.seconds_in_wait,
sw.state
FROM v$session_wait sw,
v$session s
WHERE s.sid = sw.sid
ORDER BY sw.seconds_in_wait DESC;
38.Script to find in oracle 10g - services
SET LINESIZE 200
COLUMN name FORMAT A30
COLUMN network_name FORMAT A50
SELECT name,
network_name
FROM dba_services
ORDER BY name;
COLUMN name FORMAT A30
COLUMN network_name FORMAT A50
SELECT name,
network_name
FROM dba_services
ORDER BY name;
39.Script to find in oracle 10g - segment_advisor
SET SERVEROUTPUT ON SIZE 1000000
SET LINESIZE 200
SET VERIFY OFF
DECLARE
l_object_id NUMBER;
l_task_name VARCHAR2(32767) := 'SEGMENT_ADVISOR_TASK';
l_object_type VARCHAR2(32767) := UPPER('&1');
l_attr1 VARCHAR2(32767) := UPPER('&2');
l_attr2 VARCHAR2(32767) := UPPER('&3');
BEGIN
IF l_attr2 = 'NULL' THEN
l_attr2 := NULL;
END IF;
DBMS_ADVISOR.create_task (
advisor_name => 'Segment Advisor',
task_name => l_task_name);
DBMS_ADVISOR.create_object (
task_name => l_task_name,
object_type => l_object_type,
attr1 => l_attr1,
attr2 => l_attr2,
attr3 => NULL,
attr4 => 'null',
attr5 => NULL,
object_id => l_object_id);
DBMS_ADVISOR.set_task_parameter (
task_name => l_task_name,
parameter => 'RECOMMEND_ALL',
value => 'TRUE');
DBMS_ADVISOR.execute_task(task_name => l_task_name);
FOR cur_rec IN (SELECT f.impact,
o.type,
o.attr1,
o.attr2,
f.message,
f.more_info
FROM dba_advisor_findings f
JOIN dba_advisor_objects o ON f.object_id = o.object_id AND f.task_name = o.task_name
WHERE f.task_name = l_task_name
ORDER BY f.impact DESC)
LOOP
DBMS_OUTPUT.put_line('..');
DBMS_OUTPUT.put_line('Type : ' || cur_rec.type);
DBMS_OUTPUT.put_line('Attr1 : ' || cur_rec.attr1);
DBMS_OUTPUT.put_line('Attr2 : ' || cur_rec.attr2);
DBMS_OUTPUT.put_line('Message : ' || cur_rec.message);
DBMS_OUTPUT.put_line('More info : ' || cur_rec.more_info);
END LOOP;
DBMS_ADVISOR.delete_task(task_name => l_task_name);
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.put_line('Error : ' || DBMS_UTILITY.format_error_backtrace);
DBMS_ADVISOR.delete_task(task_name => l_task_name);
END;
/
SET LINESIZE 200
SET VERIFY OFF
DECLARE
l_object_id NUMBER;
l_task_name VARCHAR2(32767) := 'SEGMENT_ADVISOR_TASK';
l_object_type VARCHAR2(32767) := UPPER('&1');
l_attr1 VARCHAR2(32767) := UPPER('&2');
l_attr2 VARCHAR2(32767) := UPPER('&3');
BEGIN
IF l_attr2 = 'NULL' THEN
l_attr2 := NULL;
END IF;
DBMS_ADVISOR.create_task (
advisor_name => 'Segment Advisor',
task_name => l_task_name);
DBMS_ADVISOR.create_object (
task_name => l_task_name,
object_type => l_object_type,
attr1 => l_attr1,
attr2 => l_attr2,
attr3 => NULL,
attr4 => 'null',
attr5 => NULL,
object_id => l_object_id);
DBMS_ADVISOR.set_task_parameter (
task_name => l_task_name,
parameter => 'RECOMMEND_ALL',
value => 'TRUE');
DBMS_ADVISOR.execute_task(task_name => l_task_name);
FOR cur_rec IN (SELECT f.impact,
o.type,
o.attr1,
o.attr2,
f.message,
f.more_info
FROM dba_advisor_findings f
JOIN dba_advisor_objects o ON f.object_id = o.object_id AND f.task_name = o.task_name
WHERE f.task_name = l_task_name
ORDER BY f.impact DESC)
LOOP
DBMS_OUTPUT.put_line('..');
DBMS_OUTPUT.put_line('Type : ' || cur_rec.type);
DBMS_OUTPUT.put_line('Attr1 : ' || cur_rec.attr1);
DBMS_OUTPUT.put_line('Attr2 : ' || cur_rec.attr2);
DBMS_OUTPUT.put_line('Message : ' || cur_rec.message);
DBMS_OUTPUT.put_line('More info : ' || cur_rec.more_info);
END LOOP;
DBMS_ADVISOR.delete_task(task_name => l_task_name);
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.put_line('Error : ' || DBMS_UTILITY.format_error_backtrace);
DBMS_ADVISOR.delete_task(task_name => l_task_name);
END;
/
40.Script to find in oracle 10g - lock_tree
SET LINESIZE 500
SET PAGESIZE 1000
COLUMN username FORMAT A15
COLUMN machine FORMAT A25
COLUMN logon_time FORMAT A20
SELECT LPAD(' ', (level-1)*2, ' ') || NVL(s.username, '(oracle)') AS username,
s.osuser,
s.sid,
s.serial#,
s.lockwait,
s.status,
s.module,
s.machine,
s.program,
TO_CHAR(s.logon_Time,'DD-MON-YYYY HH24:MI:SS') AS logon_time
FROM v$session s
CONNECT BY PRIOR s.sid = s.blocking_session
START WITH s.blocking_session IS NULL;
SET PAGESIZE 14
SET PAGESIZE 1000
COLUMN username FORMAT A15
COLUMN machine FORMAT A25
COLUMN logon_time FORMAT A20
SELECT LPAD(' ', (level-1)*2, ' ') || NVL(s.username, '(oracle)') AS username,
s.osuser,
s.sid,
s.serial#,
s.lockwait,
s.status,
s.module,
s.machine,
s.program,
TO_CHAR(s.logon_Time,'DD-MON-YYYY HH24:MI:SS') AS logon_time
FROM v$session s
CONNECT BY PRIOR s.sid = s.blocking_session
START WITH s.blocking_session IS NULL;
SET PAGESIZE 14
41.Script to find in oracle 10g jobs_running
SET LINESIZE 200
COLUMN owner FORMAT A20
SELECT owner,
job_name,
running_instance,
elapsed_time
FROM dba_scheduler_running_jobs
ORDER BY owner, job_name;
COLUMN owner FORMAT A20
SELECT owner,
job_name,
running_instance,
elapsed_time
FROM dba_scheduler_running_jobs
ORDER BY owner, job_name;
42.Script to find in oracle 10g to Displays scheduler job information.
SET LINESIZE 200
COLUMN owner FORMAT A20
COLUMN next_run_date FORMAT A35
SELECT owner,
job_name,
enabled,
job_class,
next_run_date
FROM dba_scheduler_jobs
ORDER BY owner, job_name;
COLUMN owner FORMAT A20
COLUMN next_run_date FORMAT A35
SELECT owner,
job_name,
enabled,
job_class,
next_run_date
FROM dba_scheduler_jobs
ORDER BY owner, job_name;
43.Script to find in oracle 10g for displays scheduler information about job schedules.
SET LINESIZE 250
COLUMN owner FORMAT A20
COLUMN schedule_name FORMAT A30
COLUMN start_date FORMAT A35
COLUMN repeat_interval FORMAT A50
COLUMN end_date FORMAT A35
COLUMN comments FORMAT A40
SELECT owner,
schedule_name,
start_date,
repeat_interval,
end_date,
comments
FROM dba_scheduler_schedules
ORDER BY owner, schedule_name;
COLUMN owner FORMAT A20
COLUMN schedule_name FORMAT A30
COLUMN start_date FORMAT A35
COLUMN repeat_interval FORMAT A50
COLUMN end_date FORMAT A35
COLUMN comments FORMAT A40
SELECT owner,
schedule_name,
start_date,
repeat_interval,
end_date,
comments
FROM dba_scheduler_schedules
ORDER BY owner, schedule_name;
44.Script to find in oracle 10g for Displays scheduler information about job programs.
SET LINESIZE 250
COLUMN owner FORMAT A20
COLUMN program_name FORMAT A30
COLUMN program_action FORMAT A50
COLUMN comments FORMAT A40
SELECT owner,
program_name,
program_type,
program_action,
number_of_arguments,
enabled,
comments
FROM dba_scheduler_programs
ORDER BY owner, program_name;
COLUMN owner FORMAT A20
COLUMN program_name FORMAT A30
COLUMN program_action FORMAT A50
COLUMN comments FORMAT A40
SELECT owner,
program_name,
program_type,
program_action,
number_of_arguments,
enabled,
comments
FROM dba_scheduler_programs
ORDER BY owner, program_name;
45.Script to find in oracle 10g for job_classes
SET LINESIZE 200
COLUMN service FORMAT A20
COLUMN comments FORMAT A40
SELECT job_class_name,
resource_consumer_group,
service,
logging_level,
log_history,
comments
FROM dba_scheduler_job_classes
ORDER BY job_class_name;
COLUMN service FORMAT A20
COLUMN comments FORMAT A40
SELECT job_class_name,
resource_consumer_group,
service,
logging_level,
log_history,
comments
FROM dba_scheduler_job_classes
ORDER BY job_class_name;
46.Script to find in oracle 10g for feature_usage
COLUMN name FORMAT A50
COLUMN detected_usages FORMAT 999999999999
SELECT u1.name,
u1.detected_usages
FROM dba_feature_usage_statistics u1
WHERE u1.version = (SELECT MAX(u2.version)
FROM dba_feature_usage_statistics u2
WHERE u2.name = u1.name)
ORDER BY u1.name;
COLUMN FORMAT DEFAULT
COLUMN detected_usages FORMAT 999999999999
SELECT u1.name,
u1.detected_usages
FROM dba_feature_usage_statistics u1
WHERE u1.version = (SELECT MAX(u2.version)
FROM dba_feature_usage_statistics u2
WHERE u2.name = u1.name)
ORDER BY u1.name;
COLUMN FORMAT DEFAULT
47.Script to find dynamic_memory in Oracle 10g
COLUMN name FORMAT A40
COLUMN value FORMAT A40
SELECT name,
value
FROM v$parameter
WHERE SUBSTR(name, 1, 1) = '_'
ORDER BY name;
COLUMN FORMAT DEFAULT
COLUMN value FORMAT A40
SELECT name,
value
FROM v$parameter
WHERE SUBSTR(name, 1, 1) = '_'
ORDER BY name;
COLUMN FORMAT DEFAULT
48.script to find db_usage_hwm in oracle 10g
COLUMN name FORMAT A40
COLUMN highwater FORMAT 999999999999
COLUMN last_value FORMAT 999999999999
SET PAGESIZE 24
SELECT hwm1.name,
hwm1.highwater,
hwm1.last_value
FROM dba_high_water_mark_statistics hwm1
WHERE hwm1.version = (SELECT MAX(hwm2.version)
FROM dba_high_water_mark_statistics hwm2
WHERE hwm2.name = hwm1.name)
ORDER BY hwm1.name;
COLUMN highwater FORMAT 999999999999
COLUMN last_value FORMAT 999999999999
SET PAGESIZE 24
SELECT hwm1.name,
hwm1.highwater,
hwm1.last_value
FROM dba_high_water_mark_statistics hwm1
WHERE hwm1.version = (SELECT MAX(hwm2.version)
FROM dba_high_water_mark_statistics hwm2
WHERE hwm2.name = hwm1.name)
ORDER BY hwm1.name;
49.Script to find active_session_waits in Oracle 10g
SET LINESIZE 250
SET PAGESIZE 1000
COLUMN username FORMAT A15
COLUMN osuser FORMAT A15
COLUMN sid FORMAT 99999
COLUMN serial# FORMAT 9999999
COLUMN wait_class FORMAT A15
COLUMN state FORMAT A19
COLUMN logon_time FORMAT A20
SELECT NVL(a.username, '(oracle)') AS username,
a.osuser,
a.sid,
a.serial#,
d.spid AS process_id,
a.wait_class,
a.seconds_in_wait,
a.state,
a.blocking_session,
a.blocking_session_status,
a.module,
TO_CHAR(a.logon_Time,'DD-MON-YYYY HH24:MI:SS') AS logon_time
FROM v$session a,
v$process d
WHERE a.paddr = d.addr
AND a.status = 'ACTIVE'
ORDER BY 1,2;
SET PAGESIZE 14
SET PAGESIZE 1000
COLUMN username FORMAT A15
COLUMN osuser FORMAT A15
COLUMN sid FORMAT 99999
COLUMN serial# FORMAT 9999999
COLUMN wait_class FORMAT A15
COLUMN state FORMAT A19
COLUMN logon_time FORMAT A20
SELECT NVL(a.username, '(oracle)') AS username,
a.osuser,
a.sid,
a.serial#,
d.spid AS process_id,
a.wait_class,
a.seconds_in_wait,
a.state,
a.blocking_session,
a.blocking_session_status,
a.module,
TO_CHAR(a.logon_Time,'DD-MON-YYYY HH24:MI:SS') AS logon_time
FROM v$session a,
v$process d
WHERE a.paddr = d.addr
AND a.status = 'ACTIVE'
ORDER BY 1,2;
SET PAGESIZE 14
No comments:
Post a Comment