当前位置: 代码迷 >> HTML/CSS >> 检察存储结构-控制、REDO日志、表空间使用情况、数据文件等信息脚本-HTML
  详细解决方案

检察存储结构-控制、REDO日志、表空间使用情况、数据文件等信息脚本-HTML

热度:572   发布时间:2013-10-10 14:14:51.0
检查存储结构-控制、REDO日志、表空间使用情况、数据文件等信息脚本--HTML

检查项目:

数据库概要信息-库名、创建时间、运行平台、所在主机等

控制文件及控制文件记录信息

REDO日志简要信息--详细的参考

表空间及数据文件对应信息

表空间存储属性及使用情况--使用率

表空间内最大及最小的段对象

数据库总大小--汇总计算数据文件、日志文件、控制文件



脚本如下:

prompt Creating database report.
prompt This script must be run as a user with DBA privileges.
prompt This process can take several minutes to complete.
prompt

-- +----------------------------------------+
-- |                           SCRIPT SETTINGS                                  |
-- +----------------------------------------+

set termout       off
set echo          off
set feedback      off
set heading       off
set verify        off
set wrap          on
set trimspool     on
set serveroutput  on
set escape        on
set timing        off
set pagesize 50000
set linesize 175
set long     2000000000
clear buffer computes columns breaks
define fileName=storage_check
define versionNumber=5.3

-- +----------------------------------------+
-- |                   GATHER DATABASE REPORT INFORMATION                       |
-- +----------------------------------------+

COLUMN tdate NEW_VALUE _date NOPRINT
SELECT TO_CHAR(SYSDATE,'YYYY/MM/DD') tdate FROM dual;

COLUMN time NEW_VALUE _time NOPRINT
SELECT TO_CHAR(SYSDATE,'HH24:MI:SS') time FROM dual;

COLUMN date_time NEW_VALUE _date_time NOPRINT
SELECT TO_CHAR(SYSDATE,'MM/DD/YYYY HH24:MI:SS') date_time FROM dual;

COLUMN date_time_timezone NEW_VALUE _date_time_timezone NOPRINT
SELECT TO_CHAR(systimestamp, 'Mon DD, YYYY (') || TRIM(TO_CHAR(systimestamp, 'Day')) || TO_CHAR(systimestamp, ') "at" HH24:MI:SS ') || TO_CHAR(systimestamp, ' "in Timezone" TZR') date_time_timezone
FROM dual;

COLUMN spool_time NEW_VALUE _spool_time NOPRINT
SELECT TO_CHAR(SYSDATE,'YYYYMMDDhh24miss') spool_time FROM dual;

COLUMN dbname NEW_VALUE _dbname NOPRINT
SELECT name dbname FROM v$database;

COLUMN dbid NEW_VALUE _dbid NOPRINT
SELECT dbid dbid FROM v$database;

COLUMN platform_id NEW_VALUE _platform_id NOPRINT
SELECT platform_id platform_id FROM v$database;

COLUMN platform_name NEW_VALUE _platform_name NOPRINT
SELECT platform_name platform_name FROM v$database;

COLUMN global_name NEW_VALUE _global_name NOPRINT
SELECT global_name global_name FROM global_name;

COLUMN blocksize NEW_VALUE _blocksize NOPRINT
SELECT value blocksize FROM v$parameter WHERE name='db_block_size';

COLUMN startup_time NEW_VALUE _startup_time NOPRINT
SELECT TO_CHAR(startup_time, 'MM/DD/YYYY HH24:MI:SS') startup_time FROM v$instance;

COLUMN host_name NEW_VALUE _host_name NOPRINT
SELECT host_name host_name FROM v$instance;

COLUMN instance_name NEW_VALUE _instance_name NOPRINT
SELECT instance_name instance_name FROM v$instance;

COLUMN instance_number NEW_VALUE _instance_number NOPRINT
SELECT instance_number instance_number FROM v$instance;

COLUMN thread_number NEW_VALUE _thread_number NOPRINT
SELECT thread# thread_number FROM v$instance;

COLUMN cluster_database NEW_VALUE _cluster_database NOPRINT
SELECT value cluster_database FROM v$parameter WHERE name='cluster_database';

COLUMN cluster_database_instances NEW_VALUE _cluster_database_instances NOPRINT
SELECT value cluster_database_instances FROM v$parameter WHERE name='cluster_database_instances';

COLUMN reportRunUser NEW_VALUE _reportRunUser NOPRINT
SELECT user reportRunUser FROM dual;
COLUMN user_role NEW_VALUE _user_role NOPRINT
select granted_role user_role from user_role_privs;
COLUMN db_unique_name NEW_VALUE _db_unique_name NOPRINT
select db_unique_name from v$database;
COLUMN server_ip NEW_VALUE _server_ip NOPRINT
select utl_inaddr.get_host_address server_ip from dual;
COLUMN created NEW_VALUE _created NOPRINT
select to_char(created,'yyyy/mm/dd hh24:mi:ss') created from v$database;

-- +----------------------------------------+
-- |                   GATHER DATABASE REPORT INFORMATION                       |
-- +----------------------------------------+
set heading on
set markup html on spool on preformat off entmap on -
head ' -
  <title>Database STORAGE Report</title> -
  <style type="text/css"> -
    body              {font:12pt Arial,Helvetica,sans-serif; color:black; background:White;} -
    p                 {font:9pt Arial,Helvetica,sans-serif; color:black; background:White;} -
    table,tr,td       {font:9pt Arial,Helvetica,sans-serif; color:Black; background:#C0C0C0; padding:0px 0px 0px 0px; margin:0px 0px 0px 0px;} -
    th                {font:bold 9pt Arial,Helvetica,sans-serif; color:#336699; background:#cccc99; padding:0px 0px 0px 0px;} -
    h1                {font:bold 12pt Arial,Helvetica,Geneva,sans-serif; color:#336699; background-color:White; border-bottom:1px solid #cccc99; margin-top:0pt; margin-bottom:0pt; padding:0px 0px 0px 0px;} -
    h2                {font:bold 10pt Arial,Helvetica,Geneva,sans-serif; color:#336699; background-color:White; margin-top:4pt; margin-bottom:0pt;} -
    a                 {font:9pt Arial,Helvetica,sans-serif; color:#663300; margin-top:0pt; margin-bottom:0pt; vertical-align:top;} -
    a.link            {font:9pt Arial,Helvetica,sans-serif; color:#663300; margin-top:0pt; margin-bottom:0pt; vertical-align:top;} -
    a.noLink          {font:9pt Arial,Helvetica,sans-serif; color:#663300; text-decoration: none; margin-top:0pt; margin-bottom:0pt; vertical-align:top;} -
    a.noLinkBlue      {font:9pt Arial,Helvetica,sans-serif; color:#0000ff; text-decoration: none; margin-top:0pt; margin-bottom:0pt; vertical-align:top;} -
    a.noLinkDarkBlue  {font:9pt Arial,Helvetica,sans-serif; color:#000099; text-decoration: none; margin-top:0pt; margin-bottom:0pt; vertical-align:top;} -
    a.noLinkRed       {font:9pt Arial,Helvetica,sans-serif; color:#ff0000; text-decoration: none; margin-top:0pt; margin-bottom:0pt; vertical-align:top;} -
    a.noLinkDarkRed   {font:9pt Arial,Helvetica,sans-serif; color:#990000; text-decoration: none; margin-top:0pt; margin-bottom:0pt; vertical-align:top;} -
    a.noLinkGreen     {font:9pt Arial,Helvetica,sans-serif; color:#00ff00; text-decoration: none; margin-top:0pt; margin-bottom:0pt; vertical-align:top;} -
    a.noLinkDarkGreen {font:9pt Arial,Helvetica,sans-serif; color:#009900; text-decoration: none; margin-top:0pt; margin-bottom:0pt; vertical-align:top;} -
  </style>' -
body   'BGCOLOR="#C0C0C0"' -
table  'WIDTH="90%" BORDER="1"'
spool &FileName._&_dbname._&_spool_time..html
set markup html on entmap off

-- +============================================================================+
-- |                                                                            |
-- |        <<<<<     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>

-- +----------------------------------------+
-- |                            - REPORT HEADER -                               |
-- +----------------------------------------+

prompt
prompt <a name="report_header"></a>
prompt <font size="+2" face="Arial,Helvetica,Geneva,sans-serif" color="#336699"><b>Report Header</b></font><hr align="left" width="460">

prompt <table width="90%" border="1"> -
<tr><th align="left" width="20%">Report Name</th><td width="80%"><tt>&FileName._&_dbname._&_spool_time..html</tt></td></tr> -
<tr><th align="left" width="20%">Snapshot Database Version</th><td width="80%"><tt>&versionNumber</tt></td></tr> -
<tr><th align="left" width="20%">Created_Time</th><td width="80%"><tt>&_created</tt></td></tr> -
<tr><th align="left" width="20%">Run Date / Time / Timezone</th><td width="80%"><tt>&_date_time_timezone</tt></td></tr> -
<tr><th align="left" width="20%">Host Name</th><td width="80%"><tt>&_host_name</tt></td></tr> -
<tr><th align="left" width="20%">Server IP</th><td width="80%"><tt>&_server_ip</tt></td></tr> -
<tr><th align="left" width="20%">Database Name</th><td width="80%"><tt>&_dbname</tt></td></tr> -
<tr><th align="left" width="20%">Database UNIQUE Name</th><td width="80%"><tt>&_db_unique_name</tt></td></tr> -
<tr><th align="left" width="20%">Database ID</th><td width="80%"><tt>&_dbid</tt></td></tr> -
<tr><th align="left" width="20%">Global Database Name</th><td width="80%"><tt>&_global_name</tt></td></tr> -
<tr><th align="left" width="20%">Platform Name / ID</th><td width="80%"><tt>&_platform_name / &_platform_id</tt></td></tr> -
<tr><th align="left" width="20%">Clustered Database?</th><td width="80%"><tt>&_cluster_database</tt></td></tr> -
<tr><th align="left" width="20%">Clustered Database Instances</th><td width="80%"><tt>&_cluster_database_instances</tt></td></tr> -
<tr><th align="left" width="20%">Instance Name</th><td width="80%"><tt>&_instance_name</tt></td></tr> -
<tr><th align="left" width="20%">Instance Number</th><td width="80%"><tt>&_instance_number</tt></td></tr> -
<tr><th align="left" width="20%">Thread Number</th><td width="80%"><tt>&_thread_number</tt></td></tr> -
<tr><th align="left" width="20%">Database Startup Time</th><td width="80%"><tt>&_startup_time</tt></td></tr> -
<tr><th align="left" width="20%">Database Block Size</th><td width="80%"><tt>&_blocksize</tt></td></tr> -
<tr><th align="left" width="20%">Report Run User</th><td width="80%"><tt>&_reportRunUser</tt></td></tr> -
<tr><th align="left" width="20%">user_role</th><td width="80%"><tt>&_user_role</tt></td></tr> -
</table>

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="left">' || 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>

-- +----------------------------------------+
-- |                         - CONTROL FILE RECORDS -                           |
-- +----------------------------------------+

prompt <a name="control_file_records"></a>
prompt <font size="+2" face="Arial,Helvetica,Geneva,sans-serif" color="#336699"><b>Control File Records</b></font><hr align="left" width="460">

CLEAR COLUMNS BREAKS COMPUTES
COLUMN type           FORMAT          a95    HEADING 'Record Section Type'      ENTMAP off
COLUMN record_size    FORMAT       999,999   HEADING 'Record Size|(in bytes)'   ENTMAP off
COLUMN records_total  FORMAT       999,999   HEADING 'Records Allocated'        ENTMAP off
COLUMN bytes_alloc    FORMAT   999,999,999   HEADING 'Bytes Allocated'          ENTMAP off
COLUMN records_used   FORMAT       999,999   HEADING 'Records Used'             ENTMAP off
COLUMN bytes_used     FORMAT   999,999,999   HEADING 'Bytes Used'               ENTMAP off
COLUMN pct_used       FORMAT          B999   HEADING '% Used'                   ENTMAP off
COLUMN first_index                           HEADING 'First Index'              ENTMAP off
COLUMN last_index                            HEADING 'Last Index'               ENTMAP off
COLUMN last_recid                            HEADING 'Last RecID'               ENTMAP off
BREAK ON report
COMPUTE sum LABEL '<font color="#990000"><b>Total: </b></font>'   of record_size records_total bytes_alloc records_used bytes_used ON report
COMPUTE avg LABEL '<font color="#990000"><b>Average: </b></font>' of pct_used      ON report

SELECT
'<div align="left"><font color="#336699"><b>' || type || '</b></font></div>'  type,
record_size                                       record_size,
records_total                                     records_total,
(records_total * record_size)                     bytes_alloc,
records_used                                      records_used,
(records_used * record_size)                      bytes_used,
NVL(records_used/records_total * 100, 0)          pct_used,
first_index                                       first_index,
last_index                                        last_index,
last_recid                                        last_recid
FROM v$controlfile_record_section
ORDER BY type;

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 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'            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
  , '<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                                                                                          bytes
  , '<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>

-- +----------------------------------------+
-- |                            - tablespace_datafile -                               |
-- +----------------------------------------+
prompt <a name="datafile"></a>
prompt <font size="+2" face="Arial,Helvetica,Geneva,sans-serif" color="#336699"><b>tablespace_datafiles</b></font><hr align="left" width="460">
CLEAR COLUMNS BREAKS COMPUTES
COLUMN file_name                           HEADING 'File_name'  ENTMAP off
COLUMN file_id               HEADING 'File_ID'            ENTMAP off
COLUMN tablespace_name      HEADING 'Tablespace_Name'         ENTMAP off
COLUMN MB       HEADING 'FILE_MB'         ENTMAP off

select '<tt>' || tablespace_name || '</tt>' tablespace_name,
'<div align="center"><b><font color="red">' ||file_id ||'</font></b></div>' file_id,
'<div align="left">' ||file_name|| '</div>' file_name,
'<div align="left">' ||bytes/1024/1024 || '</div>' MB
from dba_data_files order by file_id;

prompt <center>[<a class="noLink" href="#top">Top</a>]</center><p>

-- +----------------------------------------+
-- |                            - database MB -                               |
-- +----------------------------------------+
prompt <a name="database MB"></a>
prompt <font size="+2" face="Arial,Helvetica,Geneva,sans-serif" color="#336699"><b>database MB</b></font><hr align="left" width="460">
CLEAR COLUMNS BREAKS COMPUTES
COLUMN sum_database_M NEW_VALUE _sum_database_M NOPRINT
COLUMN sum_datafile NEW_VALUE _sum_datafile NOPRINT
COLUMN sum_redo NEW_VALUE _sum_redo NOPRINT
COLUMN sum_ctl NEW_VALUE _sum_ctl NOPRINT
select sum_d+sum_r+sum_c   as sum_database_M,sum_d as sum_datafile,sum_r as sum_redo,sum_c  as sum_ctl from (select sum(m) as sum_d from (select file_name,tablespace_name,bytes/1024/1024 m from dba_data_files union select file_name,tablespace_name,bytes/1024/1024 m from dba_temp_files)) a,(select  sum(members*m) as sum_r from (select group#,members,bytes/1024/1024 m from v$log)) b,(select sum(bytes_m) as sum_c from (select name,block_size*file_size_blks/1024/1024 as bytes_m from v$controlfile)) c;

prompt <table width="90%" border="1"> -
<tr><th align="left" width="20%">sum_database_M</th><td width="80%"><tt>&_sum_database_M</tt></td></tr> -
<tr><th align="left" width="20%">sum_datafile</th><td width="80%"><tt>&_sum_datafile</tt></td></tr> -
<tr><th align="left" width="20%">sum_redo</th><td width="80%"><tt>&_sum_redo</tt></td></tr> -
<tr><th align="left" width="20%">sum_ctl</th><td width="80%"><tt>&_sum_ctl</tt></td></tr> -
</table>
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

COLUMN status                                  HEADING 'Status'            ENTMAP off
COLUMN name                                    HEADING 'Tablespace Name'   ENTMAP off
COLUMN type        FORMAT a12                  HEADING 'TS Type'           ENTMAP off
COLUMN extent_mgt  FORMAT a10                  HEADING 'Ext. Mgt.'         ENTMAP off
COLUMN segment_mgt FORMAT a9                   HEADING 'Seg. Mgt.'         ENTMAP off
COLUMN ts_size     FORMAT 999,999,999,999,999  HEADING 'Tablespace Size'   ENTMAP off
COLUMN free        FORMAT 999,999,999,999,999  HEADING 'Free (in MB)'   ENTMAP off
COLUMN used        FORMAT 999,999,999,999,999  HEADING 'Used (in MB)'   ENTMAP off
COLUMN pct_used                                HEADING 'Tab_Used'         ENTMAP off

BREAK ON report
COMPUTE SUM label '<font color="#990000"><b>Total:</b></font>'   OF ts_size used free ON report

SELECT
     '<b><font color="#336699">' || d.tablespace_name || '</font></b>'                               name
    ,DECODE(   d.status
            , 'OFFLINE'
            , '<div align="center"><b><font color="#990000">'   || d.status || '</font></b></div>'
            , '<div align="center"><b><font color="darkgreen">' || d.status || '</font></b></div>') status
  , d.contents                                          type
  , d.extent_management                                 extent_mgt
  , d.segment_space_management                          segment_mgt
  , (NVL(a.bytes, 0))/1024/1024                                     ts_size
  , (NVL(f.bytes, 0))/1024/1024                                     free
  , (NVL(a.bytes - NVL(f.bytes, 0), 0))/1024/1024                   used
  , '<div align="right"><b>' ||
          DECODE (
              (1-SIGN(1-SIGN(TRUNC(NVL((a.bytes - NVL(f.bytes, 0)) / a.bytes * 100, 0)) - 90)))
            , 1
            , '<font color="#990000">'   || TO_CHAR(TRUNC(NVL((a.bytes - NVL(f.bytes, 0)) / a.bytes * 100, 0))) || '</font>'
            , '<font color="darkgreen">' || TO_CHAR(TRUNC(NVL((a.bytes - NVL(f.bytes, 0)) / a.bytes * 100, 0))) || '</font>'
          )
    || '</b> %</div>' pct_used
FROM
    sys.dba_tablespaces d
  , ( select tablespace_name, sum(bytes) bytes
      from dba_data_files
      group by tablespace_name
    ) a
  , ( select tablespace_name, sum(bytes) bytes
      from dba_free_space
      group by tablespace_name
    ) f
WHERE
      d.tablespace_name = a.tablespace_name(+)
  AND d.tablespace_name = f.tablespace_name(+)
  AND NOT (
    d.extent_management like 'LOCAL'
    AND
    d.contents like 'TEMPORARY'
  )
UNION ALL
SELECT
    '<b><font color="#336699">' || d.tablespace_name  || '</font></b>'                              name,
 DECODE(   d.status
            , 'OFFLINE'
            , '<div align="center"><b><font color="#990000">'   || d.status || '</font></b></div>'
            , '<div align="center"><b><font color="darkgreen">' || d.status || '</font></b></div>') status
  , d.contents                                   type
  , d.extent_management                          extent_mgt
  , d.segment_space_management                   segment_mgt
  , (NVL(a.bytes, 0))/1024/1024                              ts_size
  , (NVL(a.bytes - NVL(t.bytes,0), 0))/1024/1024             free
  , (NVL(t.bytes, 0))/1024/1024                              used
  , '<div align="right"><b>' ||
          DECODE (
              (1-SIGN(1-SIGN(TRUNC(NVL(t.bytes / a.bytes * 100, 0)) - 90)))
            , 1
            , '<font color="#990000">'   || TO_CHAR(TRUNC(NVL(t.bytes / a.bytes * 100, 0))) || '</font>'
            , '<font color="darkgreen">' || TO_CHAR(TRUNC(NVL(t.bytes / a.bytes * 100, 0))) || '</font>'
          )
    || '</b> %</div>' pct_used
FROM
    sys.dba_tablespaces d
  , ( select tablespace_name, sum(bytes) bytes
      from dba_temp_files
      group by tablespace_name
    ) a
  , ( select tablespace_name, sum(bytes_cached) bytes
      from v$temp_extent_pool
      group by tablespace_name
    ) t
WHERE
      d.tablespace_name = a.tablespace_name(+)
  AND d.tablespace_name = t.tablespace_name(+)
  AND d.extent_management like 'LOCAL'
  AND d.contents like 'TEMPORARY'
ORDER BY 2;

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(in MB)'          ENTMAP off
COLUMN smallest_ext    FORMAT 999,999,999,999,999   HEADING 'Smallest Extent(in KB)'         ENTMAP off
COLUMN total_free      FORMAT 999,999,999,999,999   HEADING 'Total Free(in MB)'              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)/1024/1024       largest_ext
  , min(bytes)/1024       smallest_ext
  , sum(bytes)/1024/1024       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>
-- +----------------------------------------+
-- |                            - END OF REPORT -                               |
-- +----------------------------------------+
SPOOL OFF
SET MARKUP HTML OFF
SET TERMOUT ON
prompt
prompt Output written to: &FileName._&_dbname._&_spool_time..html
EXIT;

输出文件浏览器打开截图:


  相关解决方案