每天自动记录表空间大小及数据文件大小

最新推荐文章于 2021-05-19 18:14:00 发布

yin1898

最新推荐文章于 2021-05-19 18:14:00 发布

阅读量521

点赞数

文章标签： file date header

本文链接：https://blog.csdn.net/yin1898/article/details/5652643

版权

每天自动记录表空间大小及数据文件大小

在d:/db_status 创建4个文件内容如下。

4个文件

Create_CSV.BAT

DB_status.sql

DiskFile_Status.csv

TableSpace_Status.csv

Create_CSV.BAT

--------------------------------------------------------

d:
cd d:/db_status
SQLPLUS "SYSTEM/MANAGER" @DB_status.sql

--------------------------------------------------------

DB_status.sql

--------------------------------------------------------

set linesize 2000;
set pagesize 2000;
SET TRIMS off;
spool Temp.txt;
set heading off;
SELECT TO_CHAR(sysdate,'yyyy-MM-dd')||',' "Date,",t.tablespace_name||',' "Tablespace,",
      SUBSTR(d.file_name,1,80)||',' "Datafile name,",
       ROUND((d.max_bytes - NVL(f.sum_bytes, 0))/1024/1024,2)||',' "Used MB,",
       ROUND(NVL(f.sum_bytes, 0)/1024/1024,2)||',' "Free MB,",
       t.initial_extent||',' "Initial Extent,",
       t.next_extent||',' "Next Extent,",
       t.min_extents||',' "Min Extents,",
       t.max_extents||',' "Max Extents,",
       t.pct_increase||',' "Pct Increase,"
FROM (SELECT tablespace_name, file_id, SUM(bytes) sum_bytes
       FROM   DBA_FREE_SPACE
       GROUP BY tablespace_name, file_id) f,
      (SELECT tablespace_name, file_name, file_id, MAX(bytes) max_bytes, status
       FROM DBA_DATA_FILES
       GROUP BY tablespace_name, file_name, file_id, status) d,
      DBA_TABLESPACES t
WHERE t.tablespace_name = d.tablespace_name
AND   f.tablespace_name(+) = d.tablespace_name
AND   f.file_id(+) = d.file_id
GROUP BY t.tablespace_name, d.file_name, t.initial_extent,
         t.next_extent, t.min_extents, t.max_extents,
         t.pct_increase, t.status, d.max_bytes, f.sum_bytes, d.status;
spool off;
host type Temp.txt;>>D:/db_status/DiskFile_Status.csv

spool Temp.txt;
select TO_CHAR(sysdate,'yyyy-MM-dd')||',' "Date",a.tablespace_name||',' ,
       round(a.bytes_alloc / 1024 / 1024, 2)||',' megs_alloc,
       round(nvl(b.bytes_free, 0) / 1024 / 1024, 2)||',' megs_free,
       round((a.bytes_alloc - nvl(b.bytes_free, 0)) / 1024 / 1024, 2)||',' megs_used,
       round((nvl(b.bytes_free, 0) / a.bytes_alloc) * 100,2)||',' Pct_Free,
       100 - round((nvl(b.bytes_free, 0) / a.bytes_alloc) * 100,2)||',' Pct_used,
       round(maxbytes/1048576,2)||',' Max
from ( select f.tablespace_name,
               sum(f.bytes) bytes_alloc,
               sum(decode(f.autoextensible, 'YES',f.maxbytes,'NO', f.bytes)) maxbytes
        from dba_data_files f
        group by tablespace_name) a,
      ( select f.tablespace_name,
               sum(f.bytes) bytes_free
        from dba_free_space f
        group by tablespace_name) b
where a.tablespace_name = b.tablespace_name (+)
union all
select TO_CHAR(sysdate,'yyyy-MM-dd')||',' "Date",h.tablespace_name||',',
       round(sum(h.bytes_free + h.bytes_used) / 1048576, 2)||',' megs_alloc,
       round(sum((h.bytes_free + h.bytes_used) - nvl(p.bytes_used, 0)) / 1048576, 2)||',' megs_free,
       round(sum(nvl(p.bytes_used, 0))/ 1048576, 2)||',' megs_used,
       round((sum((h.bytes_free + h.bytes_used) - nvl(p.bytes_used, 0)) / sum(h.bytes_used + h.bytes_free)) * 100,2)||',' Pct_Free,
       100 - round((sum((h.bytes_free + h.bytes_used) - nvl(p.bytes_used, 0)) / sum(h.bytes_used + h.bytes_free)) * 100,2)||','
pct_used,
       round(sum(f.maxbytes) / 1048576, 2)||',' max
from   sys.v_$TEMP_SPACE_HEADER h, sys.v_$Temp_extent_pool p, dba_temp_files f
where p.file_id(+) = h.file_id
and    p.tablespace_name(+) = h.tablespace_name
and    f.file_id = h.file_id
and    f.tablespace_name = h.tablespace_name
group by h.tablespace_name
ORDER BY 1;
spool off;
host type Temp.txt;>>D:/db_status/TableSpace_Status.csv;
host del Temp.txt
exit;