create or replace procedure pro_dba_spacemanage is
v_num        number(10);
v_rownum     number(10);
/*******************************************************
功能：监控数据库的各个表空间和归档日志的空间使用情况，便于空间的维护
说明：需要创建两个临时表dba_spacemanage_t,dba_spacemanage_t1用于存放最近一次监控记录和当前监控记录；表dba_spacemanage监控历史信息. 序列SEQ_DBA_SPACEMANAGE做主键,可以通过os的cron或是oracle定时任务来定期收集
********************************************************/
begin
execute immediate 'truncate table dba_spacemanage_t';
--确定空间使用表是否有数据，如果没有数据要初始化
select count(1) into v_num from dba_spacemanage;
--初始化数据
if v_num = 0 then
insert into dba_spacemanage_t
select * from dba_spacemanage_t1;
end if;
--确定有多少表空间，第一个1是因为我们还要监控archvielog，第二个1是用于后面返回行数方便
select count(1) + 1 + 1 into v_rownum from v$tablespace;
execute immediate 'truncate table dba_spacemanage_t1';
---表dba_spacemanage_t用于记录最近一次监控的记录
insert into dba_spacemanage_t
select *
from (select * from dba_spacemanage sp order by sp.type desc)
where rownum < v_rownum;
---表dba_spacemanage_t1用于记录当前监控的记录
insert into dba_spacemanage_t1
SELECT 1,
d.tablespace_name "Name",
sysdate,
TO_CHAR(NVL(a.bytes - NVL(f.bytes, 0), 0) / 1024 / 1024,'99999999.999') "used",
TO_CHAR(NVL(a.bytes / 1024 / 1024, 0), '99999990.900') "Size (M)",
to_char(nvl(a.bytes, 0)),
'0',
TO_CHAR(NVL((a.bytes - NVL(f.bytes, 0)) / a.bytes * 100, 0),'990.00') "Used %",
(select nvl(max(type), 0) + 1 from dba_spacemanage)
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  1,
d.tablespace_name "Name",
sysdate,
TO_CHAR(NVL(a.bytes - NVL(t.bytes, 0), 0) / 1024 / 1024,'99999999.999') "used",
TO_CHAR(NVL(a.bytes / 1024 / 1024, 0), '99999990.900') "Size (M)",
to_char(nvl(a.bytes, 0)),
'0',
TO_CHAR(NVL(t.bytes / a.bytes * 100, 0), '990.00') "Used %",
(select nvl(max(type), 0) + 1 from dba_spacemanage)
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'
union all
SELECT  1,
'archivelog' "Name",
sysdate "Date",
to_char(a.cur_size) "used(M)",
'0' "total(M)",
to_char(a.cur_size - pri_size) "space_diff(M)",
to_char((a.cur_size - pri_size) / pri_size) "space_rate",
'0' ,
(select nvl(max(type), 0) + 1 from dba_spacemanage) "type"
FROM (SELECT sum(v.BLOCKS * v.BLOCK_SIZE / 1024 / 1024) cur_size
FROM v$archived_log v
WHERE v.DEST_ID = 1
and trunc(v.FIRST_TIME, 'dd') = trunc(sysdate - 1, 'dd')) a,
(SELECT sum(v1.BLOCKS * v1.BLOCK_SIZE / 1024 / 1024) pri_size
FROM v$archived_log v1
WHERE v1.DEST_ID = 1
and trunc(v1.FIRST_TIME, 'dd') = trunc(sysdate - 2, 'dd')) b;
---通过比较表dba_spacemanage_t和dba_spacemanage_t1来计算表dba_spacemanage的内容
insert into dba_spacemanage
select seq_dba_spacemanage.nextval,
t.tbs_name,
t1.exec_date,
t1.space_used,
t1.space_total,
t1.space_used - t.space_used,
to_char( (t1.space_used - t.space_used) / t.space_used,'9999999.999'),
t1.space_usedrate,
t1.type
from dba_spacemanage_t t, dba_spacemanage_t1 t1
where t.tbs_name = t1.tbs_name;
end pro_dba_spacemanage;
###创建空间管理历史信息表
create table DBA_SPACEMANAGE
(
ID             NUMBER(20),
TBS_NAME       VARCHAR2(50),
EXEC_DATE      DATE,
SPACE_USED     VARCHAR2(50),
SPACE_TOTAL    VARCHAR2(50),
SPACE_DIFF     VARCHAR2(50),
SPACE_RATE     VARCHAR2(50),
SPACE_USEDRATE VARCHAR2(50),
TYPE           NUMBER(20)
)
tablespace YYPART;
-- Add comments to the columns
comment on column DBA_SPACEMANAGE.ID

comment on column DBA_SPACEMANAGE.TBS_NAME

comment on column DBA_SPACEMANAGE.EXEC_DATE

comment on column DBA_SPACEMANAGE.SPACE_USED

comment on column DBA_SPACEMANAGE.SPACE_TOTAL

comment on column DBA_SPACEMANAGE.SPACE_DIFF

comment on column DBA_SPACEMANAGE.SPACE_RATE

comment on column DBA_SPACEMANAGE.SPACE_USEDRATE

comment on column DBA_SPACEMANAGE.TYPE

####创建两个临时表

create table DBA_SPACEMANAGE_T as select * from DBA_SPACEMANAGE;
create table DBA_SPACEMANAGE_T1 as select * from DBA_SPACEMANAGE;
-- Create sequence
create sequence SEQ_DBA_SPACEMANAGE
minvalue 1
maxvalue 999999999999999999999
start with 250
increment by 1
cache 50;
######在oracle里创建定时任务，当然也可以在os下用cron创建定时任务

begin
sys.dbms_job.submit(job => :job,
what => 'pro_dba_spacemanage;',
next_date => to_date('10-10-2010 12:00:00', 'dd-mm-yyyy hh24:mi:ss'),
interval => 'TRUNC(SYSDATE + 1) + 12/24');
commit;
end;
/
###到此为止，这个procedure会每天中午12点收集一次信息，用如下sql可以知道每次查询空间的使用情况和变化情况
select 'db space total :',
trunc(t.exec_date, 'dd') "execdate",
sum(t.space_used) "space_used(M)",
sum(t.space_diff) "space_diff(M)",
sum(t.space_rate) "growth rate"
from dba_spacemanage t
where t.tbs_name not in ('archivelog','TEMP')
group by t.type, trunc(t.exec_date, 'dd')

--转自