oracle数据库巡检脚本：
SET MARKUP HTML ON ENTMAP OFF;
SET MARKUP HTML ON SPOOL ON
<style type="text/css"> -
table {border:1px solid #9ec9ec; width:60%;border-width:1px 0px 0px 1px;text-align:center;font-size:12px} -
td {border:solid #9ec9ec; border-width:0px 1px 1px 0px; padding:4px 0px;}-
</style>'
PROMPT ****************************************************************************
PROMPT **************** ***********************
PROMPT **************** SYSWARE Database HealthCheck Report ***********************
PROMPT **************** ***********************
PROMPT ************************************** **************************************
Script
break on today
column today noprint new_value xdate
select to_char(sysdate,'yyyy-mm-dd') today from dual;
column instance_name noprint new_value dbname
select instance_name from v$instance;
set heading on
set pages 40000 lines 300 trims on
set feedback off
spool db_check2_&dbname._&xdate..html
select substr(to_char(sysdate,'fmMonth DD, YYYY HH:MI:SS P.M.'),1,35) today from dual;
-- +============================================================================+
-- | |
-- | <<<<< Database and Instance Information >>>>> |
-- | |
-- +============================================================================+
prompt
prompt <center><font size="+2" face="Arial,Helvetica,Geneva,sans-serif" color="#663300"><b><u>Database and Instance Information</u></b></font></center>
-- +----------------------------------------------------------------------------+
-- | - version - |
-- +----------------------------------------------------------------------------+
prompt <a name="version"></a>
prompt <font size="+2" face="Arial,Helvetica,Geneva,sans-serif" color="#336699"><b>Version</b></font><hr align="left" width="460">
CLEAR COLUMNS BREAKS COMPUTES
COLUMN banner FORMAT a120 HEADING 'Banner'
SELECT * FROM v$version;
prompt <center>[<a class="noLink" href="#top">Top</a>]</center><p>
-- +----------------------------------------------------------------------------+
-- | - options - |
-- +----------------------------------------------------------------------------+
prompt <a name="options"></a>
prompt <font size="+2" face="Arial,Helvetica,Geneva,sans-serif" color="#336699"><b>Options</b></font><hr align="left" width="460">
CLEAR COLUMNS BREAKS COMPUTES
COLUMN parameter HEADING 'Option Name' ENTMAP off
COLUMN value HEADING 'Installed?' ENTMAP off
SELECT
DECODE( value
, 'FALSE'
, '<b><font color="#336699">' || parameter || '</font></b>'
, '<b><font color="#336699">' || parameter || '</font></b>') parameter
, DECODE( value
, 'FALSE'
, '<div align="center"><font color="#990000"><b>' || value || '</b></font></div>'
, '<div align="center">' || value || '</div>' ) value
FROM v$option
ORDER BY parameter;
prompt <center>[<a class="noLink" href="#top">Top</a>]</center><p>
-- +----------------------------------------------------------------------------+
-- | - INSTANCE OVERVIEW - |
-- +----------------------------------------------------------------------------+
prompt <a name="instance_overview"></a>
prompt <font size="+2" face="Arial,Helvetica,Geneva,sans-serif" color="#336699"><b>Instance Overview</b></font><hr align="left" width="460">
CLEAR COLUMNS BREAKS COMPUTES
COLUMN instance_name_print FORMAT a75 HEADING 'Instance|Name' ENTMAP off
COLUMN instance_number_print FORMAT a75 HEADING 'Instance|Num' ENTMAP off
COLUMN thread_number_print HEADING 'Thread|Num' ENTMAP off
COLUMN host_name_print FORMAT a75 HEADING 'Host|Name' ENTMAP off
COLUMN version HEADING 'Oracle|Version' ENTMAP off
COLUMN start_time FORMAT a75 HEADING 'Start|Time' ENTMAP off
COLUMN uptime HEADING 'Uptime|(in days)' ENTMAP off
COLUMN parallel FORMAT a75 HEADING 'Parallel - (RAC)' ENTMAP off
COLUMN instance_status FORMAT a75 HEADING 'Instance|Status' ENTMAP off
COLUMN database_status FORMAT a75 HEADING 'Database|Status' ENTMAP off
COLUMN logins FORMAT a75 HEADING 'Logins' ENTMAP off
COLUMN archiver FORMAT a75 HEADING 'Archiver' ENTMAP off
SELECT
'<div align="center"><font color="#336699"><b>' || instance_name || '</b></font></div>' instance_name_print
, '<div align="center">' || instance_number || '</div>' instance_number_print
, '<div align="center">' || thread# || '</div>' thread_number_print
, '<div align="center">' || host_name || '</div>' host_name_print
, '<div align="center">' || version || '</div>' version
, '<div align="center">' || TO_CHAR(startup_time,'mm/dd/yyyy HH24:MI:SS') || '</div>' start_time
, ROUND(TO_CHAR(SYSDATE-startup_time), 2) uptime
, '<div align="center">' || parallel || '</div>' parallel
, '<div align="center">' || status || '</div>' instance_status
, '<div align="center">' || logins || '</div>' logins
, DECODE( archiver
, 'FAILED'
, '<div align="center"><b><font color="#990000">' || archiver || '</font></b></div>'
, '<div align="center"><b><font color="darkgreen">' || archiver || '</font></b></div>') archiver
FROM gv$instance
ORDER BY instance_number;
prompt <center>[<a class="noLink" href="#top">Top</a>]</center><p>
-- +----------------------------------------------------------------------------+
-- | - DATABASE OVERVIEW - |
-- +----------------------------------------------------------------------------+
prompt <a name="database_overview"></a>
prompt <font size="+2" face="Arial,Helvetica,Geneva,sans-serif" color="#336699"><b>Database Overview</b></font><hr align="left" width="460">
CLEAR COLUMNS BREAKS COMPUTES
COLUMN name FORMAT a75 HEADING 'Database|Name' ENTMAP off
COLUMN dbid HEADING 'Database|ID' ENTMAP off
COLUMN db_unique_name HEADING 'Database|Unique Name' ENTMAP off
COLUMN creation_date HEADING 'Creation|Date' ENTMAP off
COLUMN platform_name_print HEADING 'Platform|Name' ENTMAP off
COLUMN current_scn HEADING 'Current|SCN' ENTMAP off
COLUMN log_mode HEADING 'Log|Mode' ENTMAP off
COLUMN open_mode HEADING 'Open|Mode' ENTMAP off
COLUMN force_logging HEADING 'Force|Logging' ENTMAP off
COLUMN flashback_on HEADING 'Flashback|On?' ENTMAP off
COLUMN controlfile_type HEADING 'Controlfile|Type' ENTMAP off
COLUMN last_open_incarnation_number HEADING 'Last Open|Incarnation Num' ENTMAP off
SELECT
'<div align="center"><font color="#336699"><b>' || name || '</b></font></div>' name
, '<div align="center">' || dbid || '</div>' dbid
, '<div align="center">' || db_unique_name || '</div>' db_unique_name
, '<div align="center">' || TO_CHAR(created, 'mm/dd/yyyy HH24:MI:SS') || '</div>' creation_date
, '<div align="center">' || platform_name || '</div>' platform_name_print
, '<div align="center">' || current_scn || '</div>' current_scn
, '<div align="center">' || log_mode || '</div>' log_mode
, '<div align="center">' || open_mode || '</div>' open_mode
, '<div align="center">' || force_logging || '</div>' force_logging
, '<div align="center">' || flashback_on || '</div>' flashback_on
, '<div align="center">' || controlfile_type || '</div>' controlfile_type
, '<div align="center">' || last_open_incarnation# || '</div>' last_open_incarnation_number
FROM v$database;
prompt <center>[<a class="noLink" href="#top">Top</a>]</center><p>
-- +----------------------------------------------------------------------------+
-- | - CONTROL FILES - |
-- +----------------------------------------------------------------------------+
prompt <a name="control_files"></a>
prompt <font size="+2" face="Arial,Helvetica,Geneva,sans-serif" color="#336699"><b>Control Files</b></font><hr align="left" width="460">
CLEAR COLUMNS BREAKS COMPUTES
COLUMN name HEADING 'Controlfile Name' ENTMAP off
COLUMN status FORMAT a75 HEADING 'Status' ENTMAP off
COLUMN file_size FORMAT a75 HEADING 'File Size' ENTMAP off
SELECT
'<tt>' || c.name || '</tt>' name
, DECODE( c.status
, NULL
, '<div align="center"><b><font color="darkgreen">VALID</font></b></div>'
, '<div align="center"><b><font color="#663300">' || c.status || '</font></b></div>') status
, '<div align="right">' || TO_CHAR(block_size * file_size_blks, '999,999,999,999') || '</div>' file_size
FROM
v$controlfile c
ORDER BY
c.name;
prompt <center>[<a class="noLink" href="#top">Top</a>]</center><p>
-- +----------------------------------------------------------------------------+
-- | - ONLINE REDO LOGS - |
-- +----------------------------------------------------------------------------+
prompt <a name="online_redo_logs"></a>
prompt <font size="+2" face="Arial,Helvetica,Geneva,sans-serif" color="#336699"><b>Online Redo Logs</b></font><hr align="left" width="460">
CLEAR COLUMNS BREAKS COMPUTES
COLUMN instance_name_print FORMAT a95 HEADING 'Instance Name' ENTMAP off
COLUMN thread_number_print FORMAT a95 HEADING 'Thread Number' ENTMAP off
COLUMN groupno HEADING 'Group Number' ENTMAP off
COLUMN sequence# HEADING 'Sequence#' ENTMAP off
COLUMN member HEADING 'Member' ENTMAP off
COLUMN redo_file_type FORMAT a75 HEADING 'Redo Type' ENTMAP off
COLUMN log_status FORMAT a75 HEADING 'Log Status' ENTMAP off
COLUMN bytes FORMAT 999,999,999,999 HEADING 'Bytes_Mb' ENTMAP off
COLUMN first_time FORMAT a75 HEADING 'FIRST_TIME' ENTMAP off
COLUMN archived FORMAT a75 HEADING 'Archived?' ENTMAP off
BREAK ON report ON instance_name_print ON thread_number_print
SELECT
'<div align="center"><font color="#336699"><b>' || i.instance_name || '</b></font></div>' instance_name_print
, '<div align="center">' || i.thread# || '</div>' thread_number_print
, f.group# groupno
, l.sequence# sequence#
, '<tt>' || f.member || '</tt>' member
, f.type redo_file_type
, DECODE( l.status
, 'CURRENT'
, '<div align="center"><b><font color="darkgreen">' || l.status || '</font></b></div>'
, '<div align="center"><b><font color="#990000">' || l.status || '</font></b></div>') log_status
, l.bytes/1024/1024 bytes
, l.first_time first_time
, '<div align="center">' || l.archived || '</div>' archived
FROM
gv$logfile f
, gv$log l
, gv$instance i
WHERE
f.group# = l.group#
AND l.thread# = i.thread#
AND i.inst_id = f.inst_id
AND f.inst_id = l.inst_id
ORDER BY
i.instance_name
, f.group#
, f.member;
prompt <center>[<a class="noLink" href="#top">Top</a>]</center><p>
-- +----------------------------------------------------------------------------+
-- | - REDO LOG SWITCHES - |
-- +----------------------------------------------------------------------------+
prompt <a name="redo_log_switches"></a>
prompt <font size="+2" face="Arial,Helvetica,Geneva,sans-serif" color="#336699"><b>Redo Log Switches</b></font><hr align="left" width="460">
CLEAR COLUMNS BREAKS COMPUTES
COLUMN DAY FORMAT a75 HEADING 'Day / Time' ENTMAP off
COLUMN H00 FORMAT 999,999B HEADING '00' ENTMAP off
COLUMN H01 FORMAT 999,999B HEADING '01' ENTMAP off
COLUMN H02 FORMAT 999,999B HEADING '02' ENTMAP off
COLUMN H03 FORMAT 999,999B HEADING '03' ENTMAP off
COLUMN H04 FORMAT 999,999B HEADING '04' ENTMAP off
COLUMN H05 FORMAT 999,999B HEADING '05' ENTMAP off
COLUMN H06 FORMAT 999,999B HEADING '06' ENTMAP off
COLUMN H07 FORMAT 999,999B HEADING '07' ENTMAP off
COLUMN H08 FORMAT 999,999B HEADING '08' ENTMAP off
COLUMN H09 FORMAT 999,999B HEADING '09' ENTMAP off
COLUMN H10 FORMAT 999,999B HEADING '10' ENTMAP off
COLUMN H11 FORMAT 999,999B HEADING '11' ENTMAP off
COLUMN H12 FORMAT 999,999B HEADING '12' ENTMAP off
COLUMN H13 FORMAT 999,999B HEADING '13' ENTMAP off
COLUMN H14 FORMAT 999,999B HEADING '14' ENTMAP off
COLUMN H15 FORMAT 999,999B HEADING '15' ENTMAP off
COLUMN H16 FORMAT 999,999B HEADING '16' ENTMAP off
COLUMN H17 FORMAT 999,999B HEADING '17' ENTMAP off
COLUMN H18 FORMAT 999,999B HEADING '18' ENTMAP off
COLUMN H19 FORMAT 999,999B HEADING '19' ENTMAP off
COLUMN H20 FORMAT 999,999B HEADING '20' ENTMAP off
COLUMN H21 FORMAT 999,999B HEADING '21' ENTMAP off
COLUMN H22 FORMAT 999,999B HEADING '22' ENTMAP off
COLUMN H23 FORMAT 999,999B HEADING '23' ENTMAP off
COLUMN TOTAL FORMAT 999,999,999 HEADING 'Total' ENTMAP off
BREAK ON report
COMPUTE sum LABEL '<font color="#990000"><b>Total:</b></font>' avg label '<font color="#990000"><b>Average:</b></font>' OF total ON report
SELECT
'<div align="center"><font color="#336699"><b>' || SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH:MI:SS'),1,5) || '</b></font></div>' DAY
, SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'00',1,0)) H00
, SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'01',1,0)) H01
, SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'02',1,0)) H02
, SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'03',1,0)) H03
, SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'04',1,0)) H04
, SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'05',1,0)) H05
, SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'06',1,0)) H06
, SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'07',1,0)) H07
, SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'08',1,0)) H08
, SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'09',1,0)) H09
, SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'10',1,0)) H10
, SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'11',1,0)) H11
, SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'12',1,0)) H12
, SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'13',1,0)) H13
, SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'14',1,0)) H14
, SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'15',1,0)) H15
, SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'16',1,0)) H16
, SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'17',1,0)) H17
, SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'18',1,0)) H18
, SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'19',1,0)) H19
, SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'20',1,0)) H20
, SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'21',1,0)) H21
, SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'22',1,0)) H22
, SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'23',1,0)) H23
, COUNT(*) TOTAL
FROM
v$log_history a where SYSDATE - first_time < 35
GROUP BY SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH:MI:SS'),1,5) order by 1
/
prompt <center>[<a class="noLink" href="#top">Top</a>]</center><p>
-- +----------------------------------------------------------------------------+
-- | - TABLESPACES - |
-- +----------------------------------------------------------------------------+
prompt <a name="tablespaces"></a>
prompt <font size="+2" face="Arial,Helvetica,Geneva,sans-serif" color="#336699"><b>Tablespaces</b></font><hr align="left" width="460">
CLEAR COLUMNS BREAKS COMPUTES
set pages 1000
set feed off
set linesize 300
select a.tablespace_name, cur_total,
nvl(freespace,0) cur_free, (cur_total-nvl(freespace,0)) cur_used,
(nvl(freespace,0)/cur_total)*100 "%CUR_FREE", --%freespace not considering autoextension
max_can_ext2, --max this TS can extend to
(nvl(freespace,0)+max_can_ext2-cur_total)/max_can_ext2*100 "%MAX_FREE" --%freespace considering autoextension
from
(select tablespace_name, sum(bytes)/1048576 cur_total,
sum(decode(maxbytes,0,bytes,greatest(maxbytes,bytes)))/1048576 max_can_ext2 --even if autoextensible, maxbytes may be < bytes
from dba_data_files
group by tablespace_name) a,
(select tablespace_name, sum(bytes)/1048576 freespace
from dba_free_space
group by tablespace_name) b
where a.tablespace_name = b.tablespace_name (+)
and a.tablespace_name not in
(select tablespace_name from dba_tablespaces where status = 'READ ONLY')
and a.tablespace_name != 'AUDIT_DATA' -- this TS is checked manually
order by 7;
prompt <center>[<a class="noLink" href="#top">Top</a>]</center><p>
-- +----------------------------------------------------------------------------+
-- | - DATA FILES - |
-- +----------------------------------------------------------------------------+
prompt <a name="data_files"></a>
prompt <font size="+2" face="Arial,Helvetica,Geneva,sans-serif" color="#336699"><b>Data Files</b></font><hr align="left" width="460">
CLEAR COLUMNS BREAKS COMPUTES
COLUMN tablespace HEADING 'Tablespace Name / File Class' ENTMAP off
COLUMN filename HEADING 'Filename' ENTMAP off
COLUMN filesize FORMAT 999,999,999,999,999 HEADING 'File Size' ENTMAP off
COLUMN autoextensible HEADING 'Autoextensible' ENTMAP off
COLUMN increment_by FORMAT 999,999,999,999,999 HEADING 'Next' ENTMAP off
COLUMN maxbytes FORMAT 999,999,999,999,999 HEADING 'Max' ENTMAP off
BREAK ON report
COMPUTE sum LABEL '<font color="#990000"><b>Total: </b></font>' OF filesize ON report
SELECT /*+ ordered */
'<font color="#336699"><b>' || d.tablespace_name || '</b></font>' tablespace
, '<tt>' || d.file_name || '</tt>' filename
, d.bytes/1024/1024 filesize
, '<div align="center">' || NVL(d.autoextensible, '<br>') || '</div>' autoextensible
, d.increment_by * e.value/1024 increment_by
, d.maxbytes/1024/1024 maxbytes
, v.status
FROM
sys.dba_data_files d
, v$datafile v
, (SELECT value
FROM v$parameter
WHERE name = 'db_block_size') e
WHERE
(d.file_name = v.name)
UNION
SELECT
'<font color="#336699"><b>' || d.tablespace_name || '</b></font>' tablespace
, '<tt>' || d.file_name || '</tt>' filename
, d.bytes/1024/1024 filesize
, '<div align="center">' || NVL(d.autoextensible, '<br>') || '</div>' autoextensible
, d.increment_by * e.value/1024 increment_by
, d.maxbytes/1024/1024 maxbytes
, d.status
FROM
sys.dba_temp_files d
, (SELECT value
FROM v$parameter
WHERE name = 'db_block_size') e
UNION
SELECT
'<font color="#336699"><b>[ ONLINE REDO LOG ]</b></font>'
, '<tt>' || a.member || '</tt>'
, b.bytes/1024/1024
, null
, null
, null
, null
FROM
v$logfile a
, v$log b
WHERE
a.group# = b.group#
UNION
SELECT
'<font color="#336699"><b>[ CONTROL FILE ]</b></font>'
, '<tt>' || a.name || '</tt>'
, null
, null
, null
, null
, null
FROM
v$controlfile a
ORDER BY
1
, 2;
prompt <center>[<a class="noLink" href="#top">Top</a>]</center><p>
-- +----------------------------------------------------------------------------+
-- | - DATABASE GROWTH - |
-- +----------------------------------------------------------------------------+
prompt <a name="database_growth"></a>
prompt <font size="+2" face="Arial,Helvetica,Geneva,sans-serif" color="#336699"><b>Database Growth</b></font><hr align="left" width="460">
CLEAR COLUMNS BREAKS COMPUTES
COLUMN month FORMAT a75 HEADING 'Month'
COLUMN growth FORMAT 999,999,999,999,999 HEADING 'Growth (bytes)'
BREAK ON report
COMPUTE SUM label '<font color="#990000"><b>Total:</b></font>' OF growth ON report
SELECT
'<div align="left"><font color="#336699"><b>' || TO_CHAR(creation_time, 'RRRR-MM') || '</b></font></div>' month
, SUM(bytes) growth
FROM sys.v_$datafile
GROUP BY TO_CHAR(creation_time, 'RRRR-MM')
ORDER BY TO_CHAR(creation_time, 'RRRR-MM');
--prompt <center>[<a class="noLink" href="#top">Top</a>]</center><p>
--
---- +----------------------------------------------------------------------------+
---- | - TABLESPACE EXTENTS - |
---- +----------------------------------------------------------------------------+
--
--prompt <a name="tablespace_extents"></a>
--prompt <font size="+2" face="Arial,Helvetica,Geneva,sans-serif" color="#336699"><b>Tablespace Extents</b></font><hr align="left" width="460">
--
--CLEAR COLUMNS BREAKS COMPUTES
--
--COLUMN tablespace_name HEADING 'Tablespace Name' ENTMAP off
--COLUMN largest_ext FORMAT 999,999,999,999,999 HEADING 'Largest Extent' ENTMAP off
--COLUMN smallest_ext FORMAT 999,999,999,999,999 HEADING 'Smallest Extent' ENTMAP off
--COLUMN total_free FORMAT 999,999,999,999,999 HEADING 'Total Free' ENTMAP off
--COLUMN pieces FORMAT 999,999,999,999,999 HEADING 'Number of Free Extents' ENTMAP off
--
--break on report
--compute sum label '<font color="#990000"><b>Total:</b></font>' of largest_ext smallest_ext total_free pieces on report
--
--SELECT
-- '<b><font color="#336699">' || tablespace_name || '</font></b>' tablespace_name
-- , max(bytes) largest_ext
-- , min(bytes) smallest_ext
-- , sum(bytes) total_free
-- , count(*) pieces
--FROM
-- dba_free_space
--GROUP BY
-- tablespace_name
--ORDER BY
-- tablespace_name;
--
--prompt <center>[<a class="noLink" href="#top">Top</a>]</center><p>
-- +----------------------------------------------------------------------------+
-- | - TABLESPACE TO OWNER - |
-- +----------------------------------------------------------------------------+
prompt <a name="tablespace_to_owner"></a>
prompt <font size="+2" face="Arial,Helvetica,Geneva,sans-serif" color="#336699"><b>Tablespace to Owner</b></font><hr align="left" width="460">
CLEAR COLUMNS BREAKS COMPUTES
COLUMN tablespace_name FORMAT a75 HEADING 'Tablespace Name' ENTMAP off
COLUMN owner FORMAT a75 HEADING 'Owner' ENTMAP off
COLUMN segment_type FORMAT a75 HEADING 'Segment Type' ENTMAP off
COLUMN bytes FORMAT 999,999,999,999,999 HEADING 'Size (in Bytes)' ENTMAP off
COLUMN seg_count FORMAT 999,999,999,999 HEADING 'Segment Count' ENTMAP off
BREAK ON report ON tablespace_name
COMPUTE sum LABEL '<font color="#990000"><b>Total: </b></font>' of seg_count bytes ON report
SELECT
'<font color="#336699"><b>' || tablespace_name || '</b></font>' tablespace_name
, '<div align="right">' || owner || '</div>' owner
, '<div align="right">' || segment_type || '</div>' segment_type
, sum(bytes) bytes
, count(*) seg_count
FROM
dba_segments
GROUP BY
tablespace_name
, owner
, segment_type
ORDER BY
tablespace_name
, owner
, segment_type;
prompt <center>[<a class="noLink" href="#top">Top</a>]</center><p>
-- +----------------------------------------------------------------------------+
-- | - OWNER TO TABLESPACE - |
-- +----------------------------------------------------------------------------+
prompt <a name="owner_to_tablespace"></a>
prompt <font size="+2" face="Arial,Helvetica,Geneva,sans-serif" color="#336699"><b>Owner to Tablespace</b></font><hr align="left" width="460">
CLEAR COLUMNS BREAKS COMPUTES
COLUMN owner FORMAT a75 HEADING 'Owner' ENTMAP off
COLUMN tablespace_name FORMAT a75 HEADING 'Tablespace Name' ENTMAP off
COLUMN segment_type FORMAT a75 HEADING 'Segment Type' ENTMAP off
COLUMN bytes FORMAT 999,999,999,999,999 HEADING 'Size (in Bytes)' ENTMAP off
COLUMN seg_count FORMAT 999,999,999,999 HEADING 'Segment Count' ENTMAP off
break on report on owner
compute sum label '<font color="#990000"><b>Total: </b></font>' of seg_count bytes on report
SELECT
'<font color="#336699"><b>' || owner || '</b></font>' owner
, '<div align="right">' || tablespace_name || '</div>' tablespace_name
, '<div align="right">' || segment_type || '</div>' segment_type
, sum(bytes) bytes
, count(*) seg_count
FROM
dba_segments
GROUP BY
owner
, tablespace_name
, segment_type
ORDER BY
owner
, tablespace_name
, segment_type;
prompt <center>[<a class="noLink" href="#top">Top</a>]</center><p>
-- +----------------------------------------------------------------------------+
-- | - RMAN BACKUP JOBS - |
-- +----------------------------------------------------------------------------+
prompt <a name="rman_backup_jobs"></a>
prompt <font size="+2" face="Arial,Helvetica,Geneva,sans-serif" color="#336699"><b>RMAN Backup Jobs</b></font><hr align="left" width="460">
prompt <b>Last 10 RMAN backup jobs</b>
CLEAR COLUMNS BREAKS COMPUTES
COLUMN backup_name FORMAT a130 HEADING 'Backup Name' ENTMAP off
COLUMN start_time FORMAT a75 HEADING 'Start Time' ENTMAP off
COLUMN elapsed_time FORMAT a75 HEADING 'Elapsed Time' ENTMAP off
COLUMN status HEADING 'Status' ENTMAP off
COLUMN input_type HEADING 'Input Type' ENTMAP off
COLUMN output_device_type HEADING 'Output Devices' ENTMAP off
COLUMN input_size HEADING 'Input Size' ENTMAP off
COLUMN output_size HEADING 'Output Size' ENTMAP off
COLUMN output_rate_per_sec HEADING 'Output Rate Per Sec' ENTMAP off
SELECT
'<div nowrap><b><font color="#336699">' || r.command_id || '</font></b></div>' backup_name
, '<div nowrap align="right">' || TO_CHAR(r.start_time, 'mm/dd/yyyy HH24:MI:SS') || '</div>' start_time
, '<div nowrap align="right">' || r.time_taken_display || '</div>' elapsed_time
, DECODE( r.status
, 'COMPLETED'
, '<div align="center"><b><font color="darkgreen">' || r.status || '</font></b></div>'
, 'RUNNING'
, '<div align="center"><b><font color="#000099">' || r.status || '</font></b></div>'
, 'FAILED'
, '<div align="center"><b><font color="#990000">' || r.status || '</font></b></div>'
, '<div align="center"><b><font color="#663300">' || r.status || '</font></b></div>'
) status
, r.input_type input_type
, r.output_device_type output_device_type
, '<div nowrap align="right">' || r.input_bytes_display || '</div>' input_size
, '<div nowrap align="right">' || r.output_bytes_display || '</div>' output_size
, '<div nowrap align="right">' || r.output_bytes_per_sec_display || '</div>' output_rate_per_sec
FROM
(select
command_id
, start_time
, time_taken_display
, status
, input_type
, output_device_type
, input_bytes_display
, output_bytes_display
, output_bytes_per_sec_display
from v$rman_backup_job_details
order by start_time DESC
) r
WHERE
rownum < 11;
prompt <center>[<a class="noLink" href="#top">Top</a>]</center><p>
-- +----------------------------------------------------------------------------+
-- | - RMAN BACKUP PIECES - |
-- +----------------------------------------------------------------------------+
prompt <a name="rman_backup_pieces"></a>
prompt <font size="+2" face="Arial,Helvetica,Geneva,sans-serif" color="#336699"><b>RMAN Backup Pieces</b></font><hr align="left" width="460">
prompt <b>Available backup pieces contained in the control file including available and expired backup sets</b>
CLEAR COLUMNS BREAKS COMPUTES
COLUMN bs_key FORMAT a75 HEADING 'BS Key' ENTMAP off
COLUMN piece# HEADING 'Piece #' ENTMAP off
COLUMN copy# HEADING 'Copy #' ENTMAP off
COLUMN bp_key HEADING 'BP Key' ENTMAP off
COLUMN status HEADING 'Status' ENTMAP off
COLUMN handle HEADING 'Handle' ENTMAP off
COLUMN start_time FORMAT a75 HEADING 'Start Time' ENTMAP off
COLUMN completion_time FORMAT a75 HEADING 'End Time' ENTMAP off
COLUMN elapsed_seconds FORMAT 999,999,999,999,999 HEADING 'Elapsed Seconds' ENTMAP off
COLUMN deleted FORMAT a10 HEADING 'Deleted?' ENTMAP off
BREAK ON bs_key
SELECT
'<div align="center"><font color="#336699"><b>' || bs.recid || '</b></font></div>' bs_key
, bp.piece# piece#
, bp.copy# copy#
, bp.recid bp_key
, DECODE( status
, 'A', '<div nowrap align="center"><font color="darkgreen"><b>Available</b></font></div>'
, 'D', '<div nowrap align="center"><font color="#000099"><b>Deleted</b></font></div>'
, 'X', '<div nowrap align="center"><font color="#990000"><b>Expired</b></font></div>') status
, handle handle
, '<div nowrap align="right">' || TO_CHAR(bp.start_time, 'mm/dd/yyyy HH24:MI:SS') || '</div>' start_time
, '<div nowrap align="right">' || TO_CHAR(bp.completion_time, 'mm/dd/yyyy HH24:MI:SS') || '</div>' completion_time
, bp.elapsed_seconds elapsed_seconds
FROM
v$backup_set bs
, v$backup_piece bp
WHERE
bs.set_stamp = bp.set_stamp
AND bs.set_count = bp.set_count
AND bp.status IN ('A', 'X')
ORDER BY
bs.recid
, piece#;
prompt <center>[<a class="noLink" href="#top">Top</a>]</center><p>
-- +----------------------------------------------------------------------------+
-- | - RMAN BACKUP CONTROL FILES - |
-- +----------------------------------------------------------------------------+
prompt <a name="rman_backup_control_files"></a>
prompt <font size="+2" face="Arial,Helvetica,Geneva,sans-serif" color="#336699"><b>RMAN Backup Control Files</b></font><hr align="left" width="460">
prompt <b>Available automatic control files within all available (and expired) backup sets</b>
CLEAR COLUMNS BREAKS COMPUTES
COLUMN bs_key FORMAT a75 HEADING 'BS Key' ENTMAP off
COLUMN piece# HEADING 'Piece #' ENTMAP off
COLUMN copy# HEADING 'Copy #' ENTMAP off
COLUMN bp_key HEADING 'BP Key' ENTMAP off
COLUMN controlfile_included FORMAT a75 HEADING 'Controlfile Included?' ENTMAP off
COLUMN status HEADING 'Status' ENTMAP off
COLUMN handle HEADING 'Handle' ENTMAP off
COLUMN start_time FORMAT a40 HEADING 'Start Time' ENTMAP off
COLUMN completion_time FORMAT a40 HEADING 'End Time' ENTMAP off
COLUMN elapsed_seconds FORMAT 999,999,999,999,999 HEADING 'Elapsed Seconds' ENTMAP off
COLUMN deleted FORMAT a10 HEADING 'Deleted?' ENTMAP off
BREAK ON bs_key
SELECT
'<div align="center"><font color="#336699"><b>' || bs.recid || '</b></font></div>' bs_key
, bp.piece# piece#
, bp.copy# copy#
, bp.recid bp_key
, '<div align="center"><font color="#663300"><b>' ||
DECODE(bs.controlfile_included, 'NO', '-', bs.controlfile_included) ||
'</b></font></div>' controlfile_included
, DECODE( status
, 'A', '<div nowrap align="center"><font color="darkgreen"><b>Available</b></font></div>'
, 'D', '<div nowrap align="center"><font color="#000099"><b>Deleted</b></font></div>'
, 'X', '<div nowrap align="center"><font color="#990000"><b>Expired</b></font></div>') status
, handle handle
FROM
v$backup_set bs
, v$backup_piece bp
WHERE
bs.set_stamp = bp.set_stamp
AND bs.set_count = bp.set_count
AND bp.status IN ('A', 'X')
AND bs.controlfile_included != 'NO'
ORDER BY
bs.recid
, piece#;
prompt <center>[<a class="noLink" href="#top">Top</a>]</center><p>
-- +----------------------------------------------------------------------------+
-- | - RMAN BACKUP SPFILE - |
-- +----------------------------------------------------------------------------+
prompt <a name="rman_backup_spfile"></a>
prompt <font size="+2" face="Arial,Helvetica,Geneva,sans-serif" color="#336699"><b>RMAN Backup SPFILE</b></font><hr align="left" width="460">
prompt <b>Available automatic SPFILE backups within all available (and expired) backup sets</b>
CLEAR COLUMNS BREAKS COMPUTES
COLUMN bs_key FORMAT a75 HEADING 'BS Key' ENTMAP off
COLUMN piece# HEADING 'Piece #' ENTMAP off
COLUMN copy# HEADING 'Copy #' ENTMAP off
COLUMN bp_key HEADING 'BP Key' ENTMAP off
COLUMN spfile_included FORMAT a75 HEADING 'SPFILE Included?' ENTMAP off
COLUMN status HEADING 'Status' ENTMAP off
COLUMN handle HEADING 'Handle' ENTMAP off
COLUMN start_time FORMAT a40 HEADING 'Start Time' ENTMAP off
COLUMN completion_time FORMAT a40 HEADING 'End Time' ENTMAP off
COLUMN elapsed_seconds FORMAT 999,999,999,999,999 HEADING 'Elapsed Seconds' ENTMAP off
COLUMN deleted FORMAT a10 HEADING 'Deleted?' ENTMAP off
BREAK ON bs_key
SELECT
'<div align="center"><font color="#336699"><b>' || bs.recid || '</b></font></div>' bs_key
, bp.piece# piece#
, bp.copy# copy#
, bp.recid bp_key
, '<div align="center"><font color="#663300"><b>' ||
NVL(sp.spfile_included, '-') ||
'</b></font></div>' spfile_included
, DECODE( status
, 'A', '<div nowrap align="center"><font color="darkgreen"><b>Available</b></font></div>'
, 'D', '<div nowrap align="center"><font color="#000099"><b>Deleted</b></font></div>'
, 'X', '<div nowrap align="center"><font color="#990000"><b>Expired</b></font></div>') status
, handle handle
FROM
v$backup_set bs
, v$backup_piece bp
, (select distinct set_stamp, set_count, 'YES' spfile_included
from v$backup_spfile) sp
WHERE
bs.set_stamp = bp.set_stamp
AND bs.set_count = bp.set_count
AND bp.status IN ('A', 'X')
AND bs.set_stamp = sp.set_stamp
AND bs.set_count = sp.set_count
ORDER BY
bs.recid
, piece#;
prompt <center>[<a class="noLink" href="#top">Top</a>]</center><p>
-- +----------------------------------------------------------------------------+
-- | - ARCHIVING MODE - |
-- +----------------------------------------------------------------------------+
prompt <a name="archiving_mode"></a>
prompt <font size="+2" face="Arial,Helvetica,Geneva,sans-serif" color="#336699"><b>Archiving Mode</b></font><hr align="left" width="460">
archive log list
prompt <center>[<a class="noLink" href="#top">Top</a>]</center><p>
-- +----------------------------------------------------------------------------+
-- | - SGA TARGET ADVICE - |
-- +----------------------------------------------------------------------------+
prompt <a name="sga_target_advice"></a>
prompt <font size="+2" face="Arial,Helvetica,Geneva,sans-serif" color="#336699"><b>SGA Target Advice</b></font><hr align="left" width="460">
prompt Modify the SGA_TARGET parameter (up to the size of the SGA_MAX_SIZE, if necessary) to reduce
prompt the number of "Estimated Physical Reads".
CLEAR COLUMNS BREAKS COMPUTES
COLUMN instance_name FORMAT a79 HEADING 'Instance Name' ENTMAP off
COLUMN name FORMAT a79 HEADING 'Parameter Name' ENTMAP off
COLUMN value FORMAT a79 HEADING 'Value' ENTMAP off
BREAK ON report ON instance_name
SELECT
'<div align="left"><font color="#336699"><b>' || i.instance_name || '</b></font></div>' instance_name
, p.name name
, (CASE p.name
WHEN 'sga_max_size' THEN '<div align="right">' || TO_CHAR(p.value, '999,999,999,999,999') || '</div>'
WHEN 'sga_target' THEN '<div align="right">' || TO_CHAR(p.value, '999,999,999,999,999') || '</div>'
ELSE
'<div align="right">' || p.value || '</div>'
END) value
FROM
gv$parameter p
, gv$instance i
WHERE
p.inst_id = i.inst_id
AND p.name IN ('sga_max_size', 'sga_target')
ORDER BY
i.instance_name
, p.name;
CLEAR COLUMNS BREAKS COMPUTES
COLUMN instance_name FORMAT a79 HEADING 'Instance Name' ENTMAP off
COLUMN sga_size FORMAT 999,999,999,999,999 HEADING 'SGA Size' ENTMAP off
COLUMN sga_size_factor FORMAT 999,999,999,999,999 HEADING 'SGA Size Factor' ENTMAP off
COLUMN estd_db_time FORMAT 999,999,999,999,999 HEADING 'Estimated DB Time' ENTMAP off
COLUMN estd_db_time_factor FORMAT 999,999,999,999,999 HEADING 'Estimated DB Time Factor' ENTMAP off
COLUMN estd_physical_reads FORMAT 999,999,999,999,999 HEADING 'Estimated Physical Reads' ENTMAP off
BREAK ON report ON instance_name
SELECT
'<div align="left"><font color="#336699"><b>' || i.instance_name || '</b></font></div>' instance_name
, s.sga_size
, s.sga_size_factor
, s.estd_db_time
, s.estd_db_time_factor
, s.estd_physical_reads
FROM
gv$sga_target_advice s
, gv$instance i
WHERE
s.inst_id = i.inst_id
ORDER BY
i.instance_name
, s.sga_size_factor;
prompt <center>[<a class="noLink" href="#top">Top</a>]</center><p>
-- +----------------------------------------------------------------------------+
-- | - PGA TARGET ADVICE - |
-- +----------------------------------------------------------------------------+
prompt <a name="pga_target_advice"></a>
prompt <font size="+2" face="Arial,Helvetica,Geneva,sans-serif" color="#336699"><b>PGA Target Advice</b></font><hr align="left" width="460">
CLEAR COLUMNS BREAKS COMPUTES
COLUMN instance_name FORMAT a79 HEADING 'Instance Name' ENTMAP off
COLUMN name FORMAT a79 HEADING 'Parameter Name' ENTMAP off
COLUMN value FORMAT a79 HEADING 'Value' ENTMAP off
BREAK ON report ON instance_name
SELECT
'<div align="left"><font color="#336699"><b>' || i.instance_name || '</b></font></div>' instance_name
, p.name name
, (CASE p.name
WHEN 'pga_aggregate_target' THEN '<div align="right">' || TO_CHAR(p.value, '999,999,999,999,999') || '</div>'
ELSE
'<div align="right">' || p.value || '</div>'
END) value
FROM
gv$parameter p
, gv$instance i
WHERE
p.inst_id = i.inst_id
AND p.name IN ('pga_aggregate_target', 'workarea_size_policy')
ORDER BY
i.instance_name
, p.name;
CLEAR COLUMNS BREAKS COMPUTES
COLUMN instance_name FORMAT a79 HEADING 'Instance Name' ENTMAP off
COLUMN pga_target_for_estimate FORMAT 999,999,999,999,999 HEADING 'PGA Target for Estimate' ENTMAP off
COLUMN estd_extra_bytes_rw FORMAT 999,999,999,999,999 HEADING 'Estimated Extra Bytes R/W' ENTMAP off
COLUMN estd_pga_cache_hit_percentage FORMAT 999,999,999,999,999 HEADING 'Estimated PGA Cache Hit %' ENTMAP off
COLUMN estd_overalloc_count FORMAT 999,999,999,999,999 HEADING 'ESTD_OVERALLOC_COUNT' ENTMAP off
BREAK ON report ON instance_name
SELECT
'<div align="left"><font color="#336699"><b>' || i.instance_name || '</b></font></div>' instance_name
, p.pga_target_for_estimate
, p.estd_extra_bytes_rw
, p.estd_pga_cache_hit_percentage
, p.estd_overalloc_count
FROM
gv$pga_target_advice p
, gv$instance i
WHERE
p.inst_id = i.inst_id
ORDER BY
i.instance_name
, p.pga_target_for_estimate;
prompt <center>[<a class="noLink" href="#top">Top</a>]</center><p>
-- +----------------------------------------------------------------------------+
-- | - USERS WITH DBA PRIVILEGES - |
-- +----------------------------------------------------------------------------+
prompt <a name="users_with_dba_privileges"></a>
prompt <font size="+2" face="Arial,Helvetica,Geneva,sans-serif" color="#336699"><b>Users With DBA Privileges</b></font><hr align="left" width="460">
CLEAR COLUMNS BREAKS COMPUTES
COLUMN grantee FORMAT a70 HEADING 'Grantee' ENTMAP off
COLUMN granted_role FORMAT a35 HEADING 'Granted Role' ENTMAP off
COLUMN admin_option FORMAT a75 HEADING 'Admin. Option?' ENTMAP off
COLUMN default_role FORMAT a75 HEADING 'Default Role?' ENTMAP off
SELECT
'<b><font color="#336699">' || grantee || '</font></b>' grantee
, '<div align="center">' || granted_role || '</div>' granted_role
, DECODE( admin_option
, 'YES'
, '<div align="center"><font color="darkgreen"><b>' || admin_option || '</b></font></div>'
, 'NO'
, '<div align="center"><font color="#990000"><b>' || admin_option || '</b></font></div>'
, '<div align="center"><font color="#663300"><b>' || admin_option || '</b></font></div>') admin_option
, DECODE( default_role
, 'YES'
, '<div align="center"><font color="darkgreen"><b>' || default_role || '</b></font></div>'
, 'NO'
, '<div align="center"><font color="#990000"><b>' || default_role || '</b></font></div>'
, '<div align="center"><font color="#663300"><b>' || default_role || '</b></font></div>') default_role
FROM
dba_role_privs
WHERE
granted_role = 'DBA'
ORDER BY
grantee
, granted_role;
prompt <center>[<a class="noLink" href="#top">Top</a>]</center><p>
-- +----------------------------------------------------------------------------+
-- | - DB LINKS - |
-- +----------------------------------------------------------------------------+
prompt <a name="db_links"></a>
prompt <font size="+2" face="Arial,Helvetica,Geneva,sans-serif" color="#336699"><b>DB Links</b></font><hr align="left" width="460">
CLEAR COLUMNS BREAKS COMPUTES
COLUMN owner FORMAT a75 HEADING 'Owner' ENTMAP off
COLUMN db_link FORMAT a75 HEADING 'DB Link Name' ENTMAP off
COLUMN username HEADING 'Username' ENTMAP off
COLUMN host HEADING 'Host' ENTMAP off
COLUMN created FORMAT a75 HEADING 'Created' ENTMAP off
BREAK ON owner
SELECT
'<b><font color="#336699">' || owner || '</font></b>' owner
, db_link
, username
, host
, '<div nowrap align="right">' || TO_CHAR(created, 'mm/dd/yyyy HH24:MI:SS') || '</div>' created
FROM dba_db_links
ORDER BY owner, db_link;
prompt <center>[<a class="noLink" href="#top">Top</a>]</center><p>
-- +----------------------------------------------------------------------------+
-- | - DIRECTORIES - |
-- +----------------------------------------------------------------------------+
prompt <a name="dba_directories"></a>
prompt <font size="+2" face="Arial,Helvetica,Geneva,sans-serif" color="#336699"><b>Directories</b></font><hr align="left" width="460">
CLEAR COLUMNS BREAKS COMPUTES
COLUMN owner FORMAT a75 HEADING 'Owner' ENTMAP off
COLUMN directory_name FORMAT a75 HEADING 'Directory Name' ENTMAP off
COLUMN directory_path HEADING 'Directory Path' ENTMAP off
BREAK ON report ON owner
SELECT
'<div align="left"><font color="#336699"><b>' || owner || '</b></font></div>' owner
, '<b><font color="#663300">' || directory_name || '</font></b>' directory_name
, '<tt>' || directory_path || '</tt>' directory_path
FROM
dba_directories
ORDER BY
owner
, directory_name;
prompt <center>[<a class="noLink" href="#top">Top</a>]</center><p>
-- +----------------------------------------------------------------------------+
-- | - DIRECTORY PRIVILEGES - |
-- +----------------------------------------------------------------------------+
prompt <a name="dba_directory_privileges"></a>
prompt <font size="+2" face="Arial,Helvetica,Geneva,sans-serif" color="#336699"><b>Directory Privileges</b></font><hr align="left" width="460">
CLEAR COLUMNS BREAKS COMPUTES
COLUMN table_name FORMAT a75 HEADING 'Directory Name' ENTMAP off
COLUMN grantee FORMAT a75 HEADING 'Grantee' ENTMAP off
COLUMN privilege FORMAT a75 HEADING 'Privilege' ENTMAP off
COLUMN grantable FORMAT a75 HEADING 'Grantable?' ENTMAP off
BREAK ON report ON table_name ON grantee
SELECT
'<b><font color="#336699">' || table_name || '</font></b>' table_name
, '<b><font color="#663300">' || grantee || '</font></b>' grantee
, privilege privilege
, DECODE( grantable
, 'YES'
, '<div align="center"><font color="darkgreen"><b>' || grantable || '</b></font></div>'
, 'NO'
, '<div align="center"><font color="#990000"><b>' || grantable || '</b></font></div>'
, '<div align="center"><font color="#663300"><b>' || grantable || '</b></font></div>') grantable
FROM
dba_tab_privs
WHERE
privilege IN ('READ', 'WRITE')
ORDER BY
table_name
, grantee
, privilege;
prompt <center>[<a class="noLink" href="#top">Top</a>]</center><p>
-- +----------------------------------------------------------------------------+
-- | - LOB SEGMENTS - |
-- +----------------------------------------------------------------------------+
prompt <a name="dba_lob_segments"></a>
prompt <font size="+2" face="Arial,Helvetica,Geneva,sans-serif" color="#336699"><b>LOB Segments</b></font><hr align="left" width="460">
prompt <b>Excluding all internal system schemas (i.e. CTXSYS, MDSYS, SYS, SYSTEM)</b>
CLEAR COLUMNS BREAKS COMPUTES
COLUMN owner FORMAT a85 HEADING 'Owner' ENTMAP off
COLUMN table_name FORMAT a75 HEADING 'Table Name' ENTMAP off
COLUMN column_name FORMAT a75 HEADING 'Column Name' ENTMAP off
COLUMN segment_name FORMAT a125 HEADING 'LOB Segment Name' ENTMAP off
COLUMN tablespace_name FORMAT a75 HEADING 'Tablespace Name' ENTMAP off
COLUMN lob_segment_bytes FORMAT a75 HEADING 'Segment Size' ENTMAP off
COLUMN index_name FORMAT a125 HEADING 'LOB Index Name' ENTMAP off
COLUMN in_row FORMAT a75 HEADING 'In Row?' ENTMAP off
BREAK ON report ON owner ON table_name
SELECT
'<div nowrap align="left"><font color="#336699"><b>' || l.owner || '</b></font></div>' owner
, '<div nowrap>' || l.table_name || '</div>' table_name
, '<div nowrap>' || l.column_name || '</div>' column_name
, '<div nowrap>' || l.segment_name || '</div>' segment_name
, '<div nowrap>' || s.tablespace_name || '</div>' tablespace_name
, '<div nowrap align="right">' || TO_CHAR(s.bytes, '999,999,999,999,999') || '</div>' lob_segment_bytes
, '<div nowrap>' || l.index_name || '</div>' index_name
, DECODE( l.in_row
, 'YES'
, '<div align="center"><font color="darkgreen"><b>' || l.in_row || '</b></font></div>'
, 'NO'
, '<div align="center"><font color="#990000"><b>' || l.in_row || '</b></font></div>'
, '<div align="center"><font color="#663300"><b>' || l.in_row || '</b></font></div>') in_row
FROM
dba_lobs l
, dba_segments s
WHERE
l.owner = s.owner
AND l.segment_name = s.segment_name
AND l.owner NOT IN ( 'CTXSYS'
, 'DBSNMP'
, 'DMSYS'
, 'EXFSYS'
, 'IX'
, 'LBACSYS'
, 'MDSYS'
, 'OLAPSYS'
, 'ORDSYS'
, 'OUTLN'
, 'SYS'
, 'SYSMAN'
, 'SYSTEM'
, 'WKSYS'
, 'WMSYS'
, 'XDB')
ORDER BY
l.owner
, l.table_name
, l.column_name;
prompt <center>[<a class="noLink" href="#top">Top</a>]</center><p>
-- +----------------------------------------------------------------------------+
-- | - INVALID OBJECTS - |
-- +----------------------------------------------------------------------------+
prompt <a name="invalid_objects"></a>
prompt <font size="+2" face="Arial,Helvetica,Geneva,sans-serif" color="#336699"><b>Invalid Objects</b></font><hr align="left" width="460">
CLEAR COLUMNS BREAKS COMPUTES
COLUMN owner FORMAT a85 HEADING 'Owner' ENTMAP off
COLUMN object_name FORMAT a30 HEADING 'Object Name' ENTMAP off
COLUMN object_type FORMAT a20 HEADING 'Object Type' ENTMAP off
COLUMN status FORMAT a75 HEADING 'Status' ENTMAP off
BREAK ON report ON owner
COMPUTE count LABEL '<font color="#990000"><b>Grand Total: </b></font>' OF object_name ON report
SELECT
'<div nowrap align="left"><font color="#336699"><b>' || owner || '</b></font></div>' owner
, object_name
, object_type
, DECODE( status
, 'VALID'
, '<div align="center"><font color="darkgreen"><b>' || status || '</b></font></div>'
, '<div align="center"><font color="#990000"><b>' || status || '</b></font></div>' ) status
FROM dba_objects
WHERE status <> 'VALID'
ORDER BY
owner
, object_name;
prompt <center>[<a class="noLink" href="#top">Top</a>]</center><p>
-- +----------------------------------------------------------------------------+
-- | - BufferCache hit - |
-- +----------------------------------------------------------------------------+
prompt <a name="BufferCache hit"></a>
prompt <font size="+2" face="Arial,Helvetica,Geneva,sans-serif" color="#336699"><b>BufferCache hit</b></font><hr align="left" width="460">
select sum(decode(name,'physical reads',value,0))phys,
sum(decode(name,'db block gets',value,0))gets,
sum(decode(name,'consistent gets',value,0))con_gets,
(1-(sum(decode(name,'physical reads',value,0))/ (sum(decode(name,'db block gets',value,0))+ sum(decode(name,'consistent gets',value,0)))))*100 hitratio
from v$sysstat;
prompt <center>[<a class="noLink" href="#top">Top</a>]</center><p>
-- +----------------------------------------------------------------------------+
-- | - data dictionary hit- |
-- +----------------------------------------------------------------------------+
prompt <a name="data dictionary hit"></a>
prompt <font size="+2" face="Arial,Helvetica,Geneva,sans-serif" color="#336699"><b>data dictionary hit</b></font><hr align="left" width="460">
Select (1-(sum(getmisses)/sum(gets)))*100 "Hit Ratio" from v$rowcache;
prompt <center>[<a class="noLink" href="#top">Top</a>]</center><p>
-- +----------------------------------------------------------------------------+
-- | - LibraryCache hit- |
-- +----------------------------------------------------------------------------+
prompt <a name="LibraryCache hit"></a>
prompt <font size="+2" face="Arial,Helvetica,Geneva,sans-serif" color="#336699"><b>LibraryCache hit</b></font><hr align="left" width="460">
Select sum(Pins)/(sum(Pins)+sum(Reloads))*100 "Hit Ratio" from v$LibraryCache;
prompt <center>[<a class="noLink" href="#top">Top</a>]</center><p>
-- +----------------------------------------------------------------------------+
-- | - LibraryCache reload- |
-- +----------------------------------------------------------------------------+
prompt <a name="LibraryCache reload"></a>
prompt <font size="+2" face="Arial,Helvetica,Geneva,sans-serif" color="#336699"><b>LibraryCache reload</b></font><hr align="left" width="460">
select sum(pinhits-reloads)/sum(pins) "hit radio",sum(reloads)/sum(pins) "reload percent"
from v$librarycache;
prompt <center>[<a class="noLink" href="#top">Top</a>]</center><p>
-- +----------------------------------------------------------------------------+
-- | - Memory Sort Hit - |
-- +----------------------------------------------------------------------------+
prompt <a name="Memory Sort Hit"></a>
prompt <font size="+2" face="Arial,Helvetica,Geneva,sans-serif" color="#336699"><b>Memory Sort Hit</b></font><hr align="left" width="460">
Select a.value "disk sorts",b.value "memory sorts",
round((100*b.value)/decode((a.value+b.value),0,1,(a.value+b.value)),2) "pct memory sorts"
from v$sysstat a,v$sysstat b where a.name='sorts (disk)' and b.name='sorts (memory)';
prompt <center>[<a class="noLink" href="#top">Top</a>]</center><p>
-- +----------------------------------------------------------------------------+
-- | - pga_aggregate_target- |
-- +----------------------------------------------------------------------------+
prompt <a name="pga_aggregate_target"></a>
prompt <font size="+2" face="Arial,Helvetica,Geneva,sans-serif" color="#336699"><b>pga_aggregate_target</b></font><hr align="left" width="460">
select min(PGA_TARGET_FOR_ESTIMATE)
from v$pga_target_advice
where ESTD_PGA_CACHE_HIT_PERCENTAGE>95;
clear columns
col name format a50
select name,value from v$pgastat where NAME='total PGA allocated';
prompt <center>[<a class="noLink" href="#top">Top</a>]</center><p>
-- +----------------------------------------------------------------------------+
-- | - redolog buffer miss- |
-- +----------------------------------------------------------------------------+
prompt <a name="redolog buffer miss"></a>
prompt <font size="+2" face="Arial,Helvetica,Geneva,sans-serif" color="#336699"><b>redolog buffer miss</b></font><hr align="left" width="460">
SELECT name, gets, misses, immediate_gets, immediate_misses,Decode(gets,0,0,misses/gets*100) ratio1,
Decode(immediate_gets+immediate_misses,0,0,
immediate_misses/(immediate_gets+immediate_misses)*100) ratio2
FROM v$latch WHERE name IN ('redo allocation', 'redo copy');
prompt <center>[<a class="noLink" href="#top">Top</a>]</center><p>
-- +----------------------------------------------------------------------------+
-- | - Parameter - |
-- +----------------------------------------------------------------------------+
prompt <a name="Parameter"></a>
prompt <font size="+2" face="Arial,Helvetica,Geneva,sans-serif" color="#336699"><b>Parameter</b></font><hr align="left" width="460">
col "Parameter Name" format a40;
col "Value" format a40;
select substr(name,0,512) "Parameter Name", substr(value,0,512) "Value", isdefault "Default", issys_modifiable "Dynamic" from v$parameter order by name;
clear columns
prompt <center>[<a class="noLink" href="#top">Top</a>]</center><p>
-- +----------------------------------------------------------------------------+
-- | - Sga Info- |
-- +----------------------------------------------------------------------------+
prompt <a name="Sga Info"></a>
prompt <font size="+2" face="Arial,Helvetica,Geneva,sans-serif" color="#336699"><b>Sga Info</b></font><hr align="left" width="460">
col name format a30
col "value" format a40
select name ,substr(value,0,512) "value" from v$sga;
clear columns
prompt <center>[<a class="noLink" href="#top">Top</a>]</center><p>
-- +----------------------------------------------------------------------------+
-- | - archivelog Info- |
-- +----------------------------------------------------------------------------+
prompt <a name="archivelog Info"></a>
prompt <font size="+2" face="Arial,Helvetica,Geneva,sans-serif" color="#336699"><b>archivelog Info</b></font><hr align="left" width="460">
col name format a30
select name , created , log_mode from v$database;
clear columns
prompt <center>[<a class="noLink" href="#top">Top</a>]</center><p>
-- +----------------------------------------------------------------------------+
-- | - version Info- |
-- +----------------------------------------------------------------------------+
prompt <a name="version Info"></a>
prompt <font size="+2" face="Arial,Helvetica,Geneva,sans-serif" color="#336699"><b>version Info</b></font><hr align="left" width="460">
select * from v$version;
prompt <center>[<a class="noLink" href="#top">Top</a>]</center><p>
-- +----------------------------------------------------------------------------+
-- | - NLS Info- |
-- +----------------------------------------------------------------------------+
prompt <a name="NLS Info"></a>
prompt <font size="+2" face="Arial,Helvetica,Geneva,sans-serif" color="#336699"><b>NLS Info</b></font><hr align="left" width="460">
col parameter format a40
col value format a35
select parameter,value from v$nls_parameters order by PARAMETER;
clear columns
prompt <center>[<a class="noLink" href="#top">Top</a>]</center><p>
-- +----------------------------------------------------------------------------+
-- | - controlfile Info- |
-- +----------------------------------------------------------------------------+
-- prompt <a name="controlfile Info"></a>
-- prompt <font size="+2" face="Arial,Helvetica,Geneva,sans-serif" color="#336699"><b>controlfile Info</b></font><hr align="left" width="460">
-- col name format a40
-- select name,status from v$controlfile;
clear columns
prompt <center>[<a class="noLink" href="#top">Top</a>]</center><p>
-- +----------------------------------------------------------------------------+
-- | - online redolog Info- |
-- +----------------------------------------------------------------------------+
--prompt <a name="online redolog Info"></a>
--prompt <font size="+2" face="Arial,Helvetica,Geneva,sans-serif" color="#336699"><b>online redolog Info</b></font><hr align="left" width="460">
--col "member" format a40
--select f.group# "group", f.member "member", l.sequence#,l.bytes,l.archived,l.status,l. first_time
--from v$logfile f, v$log l
--where f.group#=l.group#
--order by f.group#,f.member;
--clear columns
--prompt <center>[<a class="noLink" href="#top">Top</a>]</center><p>
-- +----------------------------------------------------------------------------+
-- | - top memory sql - |
-- +----------------------------------------------------------------------------+
prompt <a name="online redolog Info"></a>
prompt <font size="+2" face="Arial,Helvetica,Geneva,sans-serif" color="#336699"><b>top memory sql </b></font><hr align="left" width="460">
col "Statement" format a30
Select b.username username,a.buffer_gets gets,a.executions exec,a.buffer_gets/decode(a.executions,0,1,a.executions)gets_exec_ratio,a.sql_text "Statement" from v$sqlarea a,dba_users b where a.parsing_user_id=b.user_id and a.buffer_gets>30000000 order by a.buffer_gets desc;
clear columns
prompt <center>[<a class="noLink" href="#top">Top</a>]</center><p>
-- +----------------------------------------------------------------------------+
-- | - top disk sql- |
-- +----------------------------------------------------------------------------+
prompt <a name="top disk sql"></a>
prompt <font size="+2" face="Arial,Helvetica,Geneva,sans-serif" color="#336699"><b>top disk sql</b></font><hr align="left" width="460">
col "Statement" format a30
Select b.username username,a.disk_reads reads,a.executions exec, a.disk_reads/decode(a.executions,0,1,a.executions)rds_exec_ratio,a.sql_text "Statement" from v$sqlarea a,dba_users b
where a.parsing_user_id=b.user_id and a.disk_reads>3000000 order by a.disk_reads desc;
clear columns
prompt <center>[<a class="noLink" href="#top">Top</a>]</center><p>
-- +----------------------------------------------------------------------------+
-- | - tablespace fragmentation- |
-- +----------------------------------------------------------------------------+
prompt <a name="tablespace fragmentation"></a>
prompt <font size="+2" face="Arial,Helvetica,Geneva,sans-serif" color="#336699"><b>tablespace fragmentation</b></font><hr align="left" width="460">
select tablespace_name,sqrt(max(blocks)/sum(blocks))*(100/sqrt(sqrt(count(blocks)))) fsfi
from dba_free_space
group by tablespace_name order by fsfi;
prompt
select tablespace_name,count(*) chunks , max(bytes/1024/1024) max_chunk from dba_free_space
group by tablespace_name order by max(bytes/1024/1024),count(*);
prompt <center>[<a class="noLink" href="#top">Top</a>]</center><p>
-- +----------------------------------------------------------------------------+
-- | - Table partition - |
-- +----------------------------------------------------------------------------+
prompt <a name="top disk sql"></a>
prompt <font size="+2" face="Arial,Helvetica,Geneva,sans-serif" color="#336699"><b>Table partition</b></font><hr align="left" width="460">
select t2.TABLE_OWNER,t1.table_name, t1.max_partition_name, t2.high_value
from (select table_name, max(partition_name) as max_partition_name
from dba_tab_partitions
group by table_name) t1,
(select TABLE_OWNER,table_name, partition_name, high_value
from dba_tab_partitions
where tablespace_name not in ('SYSAUX', 'SYSTEM')) t2
where t1.table_name = t2.table_name
and t1.max_partition_name = t2.partition_name
order by 1,2;
clear columns
prompt <center>[<a class="noLink" href="#top">Top</a>]</center><p>
-- +----------------------------------------------------------------------------+
-- | - ASM DISKGROUP - |
-- +----------------------------------------------------------------------------+
prompt <a name="ASM DISKGROUP"></a>
prompt <font size="+2" face="Arial,Helvetica,Geneva,sans-serif" color="#336699"><b>ASM DISKGROUP</b></font><hr align="left" width="460">
select name,state,type,total_mb ,free_mb from v$asm_diskgroup;
clear columns
prompt <center>[<a class="noLink" href="#top">Top</a>]</center><p>
-- +----------------------------------------------------------------------------+
-- | - DBA PROFILES - |
-- +----------------------------------------------------------------------------+
prompt <a name="DBA PROFILES"></a>
prompt <font size="+2" face="Arial,Helvetica,Geneva,sans-serif" color="#336699"><b>DBA PROFILES</b></font><hr align="left" width="460">
col PROFILE for a30
select * from dba_profiles order by 1;
clear columns
prompt <center>[<a class="noLink" href="#top">Top</a>]</center><p>
set heading on
set termout on
SPOOL OFF;
SET MARKUP HTML OFF;
exit
扫一扫
1381
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
