51.Script: directories.sql
-- Description : Displays information about all directories.
[oracle@MaxwellDBA monitoring]$ cat directories.sql
-- -----------------------------------------------------------------------------------
-- File Name : /monitoring/directories.sql
-- Author : Maxwell
-- Description : Displays information about all directories.
-- Requirements : Access to the DBA views.
-- Call Syntax : @directories
-- Last Modified: 06-AUG-2022
-- -----------------------------------------------------------------------------------
SET LINESIZE 150
COLUMN owner FORMAT A20
COLUMN directory_name FORMAT A25
COLUMN directory_path FORMAT A80
SELECT *
FROM dba_directories
ORDER BY owner, directory_name;
[oracle@MaxwellDBA monitoring]$ 
52.Script: directory_permissions.sql
-- Description : Displays permission information about all directories.
[oracle@MaxwellDBA monitoring]$ cat directory_permissions.sql
-- -----------------------------------------------------------------------------------
-- File Name : /monitoring/directory_permissions.sql
-- Author : Maxwell
-- Description : Displays permission information about all directories.
-- Requirements : Access to the DBA views.
-- Call Syntax : @directory_permissions (directory_name)
-- Last Modified: 06-AUG-2022
-- -----------------------------------------------------------------------------------
SET LINESIZE 200
COLUMN grantee FORMAT A20
COLUMN owner FORMAT A10
COLUMN grantor FORMAT A20
COLUMN privilege FORMAT A20
SELECT *
FROM dba_tab_privs
WHERE table_name = UPPER('&1');
[oracle@MaxwellDBA monitoring]$ 
53.Script: dispatchers.sql
-- Description : Displays dispatcher statistics.
[oracle@MaxwellDBA monitoring]$ cat dispatchers.sql
-- -----------------------------------------------------------------------------------
-- File Name : /monitoring/dispatchers.sql
-- Author : Maxwell
-- Description : Displays dispatcher statistics.
-- Requirements : Access to the v$ views.
-- Call Syntax : @dispatchers
-- Last Modified: 06-AUG-2022
-- -----------------------------------------------------------------------------------
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
[oracle@MaxwellDBA monitoring]$ 
54.Script: error_stack.sql
-- Description : Displays contents of the error stack.
[oracle@MaxwellDBA monitoring]$ cat error_stack.sql
-- -----------------------------------------------------------------------------------
-- File Name : /monitoring/error_stack.sql
-- Author : Maxwell
-- Description : Displays contents of the error stack.
-- Call Syntax : @error_stack
-- Last Modified: 06-AUG-2022
-- -----------------------------------------------------------------------------------
SET SERVEROUTPUT ON
DECLARE
v_stack VARCHAR2(2000);
BEGIN
v_stack := Dbms_Utility.Format_Error_Stack;
Dbms_Output.Put_Line(v_stack);
END;
/
[oracle@MaxwellDBA monitoring]$SQL>
SQL> @/home/oracle/oracledba/monitoring/error_stack.sql
PL/SQL procedure successfully completed.
SQL> 55.Script: errors.sql
-- Description : Displays the source line and the associated error after compilation failure.
[oracle@MaxwellDBA monitoring]$ cat errors.sql
-- -----------------------------------------------------------------------------------
-- File Name : /monitoring/errors.sql
-- Author : Maxwell
-- Description : Displays the source line and the associated error after compilation failure.
-- Comments : Essentially the same as SHOW ERRORS.
-- Call Syntax : @errors (source-name)
-- Last Modified: 06-AUG-2022
-- -----------------------------------------------------------------------------------
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;
[oracle@MaxwellDBA monitoring]$56.Script: explain.sql
-- Description : Displays a tree-style execution plan of the specified statement after it has been explained
[oracle@MaxwellDBA monitoring]$ cat explain.sql
-- -----------------------------------------------------------------------------------
-- File Name : /monitoring/explain.sql
-- Author : Maxwell
-- Description : Displays a tree-style execution plan of the specified statement after it has been explained.
-- Requirements : Access to the plan table.
-- Call Syntax : @explain (statement-id)
-- Last Modified: 06-AUG-2022
-- -----------------------------------------------------------------------------------
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'
CONNECT BY PRIOR pt.id = pt.parent_id
AND pt.statement_id = '&1';
[oracle@MaxwellDBA monitoring]$57.Script: file_io.sql
[oracle@MaxwellDBA monitoring]$ cat file_io.sql
-- -----------------------------------------------------------------------------------
-- File Name : /monitoring/file_io.sql
-- Author : Maxwell
-- Description : Displays the amount of IO for each datafile.
-- Requirements : Access to the v$ views.
-- Call Syntax : @file_io
-- Last Modified: 06-AUG-2022
-- -----------------------------------------------------------------------------------
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
[oracle@MaxwellDBA monitoring]$ 
58.Script: fk_columns.sql
-- Description : Displays information on all FKs for the specified schema and table.
[oracle@MaxwellDBA monitoring]$ cat fk_columns.sql
-- -----------------------------------------------------------------------------------
-- File Name : /monitoring/fk_columns.sql
-- Author : Maxwell
-- Description : Displays information on all FKs for the specified schema and table.
-- Requirements : Access to the V$ views.
-- Call Syntax : @fk_columns (schema-name or all) (table-name or all)
-- Last Modified: 06-AUG-2022
-- -----------------------------------------------------------------------------------
SET VERIFY OFF
SET LINESIZE 1000
COLUMN column_name FORMAT A30
COLUMN r_column_name FORMAT A30
SELECT c.constraint_name,
cc.table_name,
cc.column_name,
rcc.table_name AS r_table_name,
rcc.column_name AS r_column_name,
cc.position
FROM dba_constraints c
JOIN dba_cons_columns cc ON c.owner = cc.owner AND c.constraint_name = cc.constraint_name
JOIN dba_cons_columns rcc ON c.owner = rcc.owner AND c.r_constraint_name = rcc.constraint_name AND cc.position = rcc.position
WHERE c.owner = DECODE(UPPER('&1'), 'ALL', c.owner, UPPER('&1'))
AND c.table_name = DECODE(UPPER('&2'), 'ALL', c.table_name, UPPER('&2'))
ORDER BY c.constraint_name, cc.table_name, cc.position;
[oracle@MaxwellDBA monitoring]$
59.Script: fks.sql
-- Description : Displays the constraints on a specific table and those referencing it.
[oracle@MaxwellDBA monitoring]$ cat fks.sql
-- -----------------------------------------------------------------------------------
-- File Name : /monitoring/fks.sql
-- Author : Maxwell
-- Description : Displays the constraints on a specific table and those referencing it.
-- Call Syntax : @fks (table-name) (schema)
-- Last Modified: 06-AUG-2022
-- -----------------------------------------------------------------------------------
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
[oracle@MaxwellDBA monitoring]$ 
60.Script: free_space.sql
-- Description : Displays space usage for each datafile.
[oracle@MaxwellDBA monitoring]$ cat free_space.sql
-- -----------------------------------------------------------------------------------
-- File Name : /monitoring/free_space.sql
-- Author : Maxwell
-- Description : Displays space usage for each datafile.
-- Requirements : Access to the DBA views.
-- Call Syntax : @free_space
-- Last Modified: 06-AUG-2022
-- -----------------------------------------------------------------------------------
SET PAGESIZE 100
SET LINESIZE 265
COLUMN tablespace_name FORMAT A20
COLUMN file_name FORMAT A50
SELECT df.tablespace_name,
df.file_name,
df.size_mb,
f.free_mb,
df.max_size_mb,
f.free_mb + (df.max_size_mb - df.size_mb) AS max_free_mb,
RPAD(' '|| RPAD('X',ROUND((df.max_size_mb-(f.free_mb + (df.max_size_mb - df.size_mb)))/max_size_mb*10,0), 'X'),11,'-') AS used_pct
FROM (SELECT file_id,
file_name,
tablespace_name,
TRUNC(bytes/1024/1024) AS size_mb,
TRUNC(GREATEST(bytes,maxbytes)/1024/1024) AS max_size_mb
FROM dba_data_files) df,
(SELECT TRUNC(SUM(bytes)/1024/1024) AS free_mb,
file_id
FROM dba_free_space
GROUP BY file_id) f
WHERE df.file_id = f.file_id (+)
ORDER BY df.tablespace_name,
df.file_name;
PROMPT
SET PAGESIZE 14
[oracle@MaxwellDBA monitoring]$
61.Script: health.sql
-- Description : Lots of information about the database so you can asses the general health of the system.
[oracle@MaxwellDBA monitoring]$ cat health.sql
-- -----------------------------------------------------------------------------------
-- File Name : /monitoring/health.sql
-- Author : Maxwell
-- Description : Lots of information about the database so you can asses the general health of the system.
-- Requirements : Access to the V$ & DBA views and several other monitoring scripts.
-- Call Syntax : @health (username/password@service)
-- Last Modified: 06-AUG-2022
-- -----------------------------------------------------------------------------------
SPOOL Health_Checks.txt
conn &1
@db_info
@sessions
@ts_full
@max_extents
SPOOL OFF
[oracle@MaxwellDBA monitoring]$62.Script: hidden_parameters.sql
-- Description : Displays a list of one or all the hidden parameters.
[oracle@MaxwellDBA monitoring]$ cat hidden_parameters.sql
-- -----------------------------------------------------------------------------------
-- File Name : /monitoring/hidden_parameters.sql
-- Author : Maxwell
-- Description : Displays a list of one or all the hidden parameters.
-- Requirements : Access to the v$ views.
-- Call Syntax : @hidden_parameters (parameter-name or all)
-- Last Modified: 06-AUG-2022
-- -----------------------------------------------------------------------------------
SET VERIFY OFF
COLUMN parameter FORMAT a37
COLUMN description FORMAT a30 WORD_WRAPPED
COLUMN session_value FORMAT a10
COLUMN instance_value FORMAT a10
SELECT a.ksppinm AS parameter,
a.ksppdesc AS description,
b.ksppstvl AS session_value,
c.ksppstvl AS instance_value
FROM x$ksppi a,
x$ksppcv b,
x$ksppsv c
WHERE a.indx = b.indx
AND a.indx = c.indx
AND a.ksppinm LIKE '/_%' ESCAPE '/'
AND a.ksppinm = DECODE(LOWER('&1'), 'all', a.ksppinm, LOWER('&1'))
ORDER BY a.ksppinm;
[oracle@MaxwellDBA monitoring]$63.Script: high_water_mark.sql
-- Description : Displays the High Water Mark for the specified table, or all tables.
[oracle@MaxwellDBA monitoring]$ cat high_water_mark.sql
-- -----------------------------------------------------------------------------------
-- File Name : /monitoring/high_water_mark.sql
-- Author : Maxwell
-- Description : Displays the High Water Mark for the specified table, or all tables.
-- Requirements : Access to the Dbms_Space.
-- Call Syntax : @high_water_mark (table_name or all) (schema-name)
-- Last Modified: 06-AUG-2022
-- -----------------------------------------------------------------------------------
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 ON
[oracle@MaxwellDBA monitoring]$
64.Script: hot_blocks.sql
-- Description : Detects hot blocks.
-- -----------------------------------------------------------------------------------
-- File Name : /monitoring/hot_blocks.sql
-- Author : Maxwell
-- Description : Detects hot blocks.
-- Call Syntax : @hot_blocks
-- Last Modified: 06-AUG-2022
-- -----------------------------------------------------------------------------------
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;65.Script: identify_trace_file.sql
-- Description : Displays the name of the trace file associated with the current session.
[oracle@MaxwellDBA monitoring]$ cat identify_trace_file.sql
-- -----------------------------------------------------------------------------------
-- File Name : /monitoring/identify_trace_file.sql
-- Author : Maxwell
-- Description : Displays the name of the trace file associated with the current session.
-- Requirements : Access to the V$ views.
-- Call Syntax : @identify_trace_file
-- Last Modified: 06-AUG-2022
-- -----------------------------------------------------------------------------------
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');
[oracle@MaxwellDBA monitoring]$ 66.Script: index_extents.sql
-- Description : Displays number of extents for all indexes belonging to the specified table, or all tables.
[oracle@MaxwellDBA monitoring]$ cat index_extents.sql
-- -----------------------------------------------------------------------------------
-- File Name : /monitoring/index_extents.sql
-- Author : Maxwell
-- Description : Displays number of extents for all indexes belonging to the specified table, or all tables.
-- Requirements : Access to the DBA views.
-- Call Syntax : @index_extents (table_name or all) (schema-name)
-- Last Modified: 06-AUG-2022
-- -----------------------------------------------------------------------------------
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 18
SET VERIFY ON
[oracle@MaxwellDBA monitoring]$ 67.Script: index_monitoring_status.sql
-- Description : Shows the monitoring status for the specified table indexes.
[oracle@MaxwellDBA monitoring]$ cat index_monitoring_status.sql
-- -----------------------------------------------------------------------------------
-- File Name : /monitoring/index_monitoring_status.sql
-- Author : Maxwell
-- Description : Shows the monitoring status for the specified table indexes.
-- Call Syntax : @index_monitoring_status (table-name) (index-name or all)
-- Last Modified: 06-AUG-2022
-- -----------------------------------------------------------------------------------
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'));
[oracle@MaxwellDBA monitoring]$ 68.Script: index_partitions.sql
-- Description : Displays partition information for the specified index, or all indexes.
[oracle@MaxwellDBA monitoring]$ cat index_partitions.sql
-- -----------------------------------------------------------------------------------
-- File Name : /monitoring/index_partitions.sql
-- Author : Maxwell
-- Description : Displays partition information for the specified index, or all indexes.
-- Requirements : Access to the DBA views.
-- Call Syntax : @index_patitions (index_name or all) (schema-name)
-- Last Modified: 06-AUG-2022
-- -----------------------------------------------------------------------------------
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
/
PROMPT
SET PAGESIZE 14
SET FEEDBACK ON
[oracle@MaxwellDBA monitoring]$69.Script: index_usage.sql
-- Description : Shows the usage for the specified table indexes.
[oracle@MaxwellDBA monitoring]$ cat index_usage.sql
-- -----------------------------------------------------------------------------------
-- File Name : /monitoring/index_usage.sql
-- Author : Maxwell
-- Description : Shows the usage for the specified table indexes.
-- Call Syntax : @index_usage (table-name) (index-name or all)
-- Last Modified: 06-AUG-2022
-- -----------------------------------------------------------------------------------
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'));
[oracle@MaxwellDBA monitoring]$70.Script: invalid_objects.sql
-- Description : Lists all invalid objects in the database.
[oracle@MaxwellDBA monitoring]$ cat invalid_objects.sql
-- -----------------------------------------------------------------------------------
-- File Name : /monitoring/invalid_objects.sql
-- Author : Maxwell
-- Description : Lists all invalid objects in the database.
-- Call Syntax : @invalid_objects
-- Requirements : Access to the DBA views.
-- Last Modified: 06-AUG-2022
-- -----------------------------------------------------------------------------------
COLUMN owner FORMAT A30
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;
[oracle@MaxwellDBA monitoring]$
2167
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
