每天自动记录表空间大小及数据文件大小
在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;
--------------------------------------------------------
DiskFile_Status.csv
--------------------------------------------------------
Date,Tablespace,Datafilename,UsedMB,FreeMB,InitialExtent,NextExtent,MinExtents,MaxExtents,PctIncrease,
TableSpace_Status.csv
--------------------------------------------------------
DATE,TABLESPACE_NAME,MEGS_ALLOC,MEGS_FREE,MEGS_USED,PCT_FREE,PCT_USED,MAX,