1、创建表

CREATE TABLE FREE_GB
(
RowversionDate date default sysdate
, TABLESPACE_NAME VARCHAR2(100)
, 总空间_GB NUMBER(10,2) NOT NULL
, 已使用空间_GB NUMBER(10,2) NOT NULL
, 剩余空间_GB NUMBER(10,2) NOT NULL
, 可自动扩充空间_GB NUMBER(10,2) NOT NULL
, 已用空间比例 NUMBER(10,2) NOT NULL
, 剩余空间比例 NUMBER(10,2) NOT NULL
, 剩余加可自动扩充空间_GB NUMBER(10,2) NOT NULL
);

2、创建记录表空间使用率的存储过程

create or replace procedure TableSpaceStat
as
begin
insert into FREE_GB(RowversionDate,TABLESPACE_NAME,总空间_GB,已使用空间_GB,剩余空间_GB,可自动扩充空间_GB,已用空间比例,剩余空间比例,剩余加可自动扩充空间_GB)
select sysdate ,a.tablespace_name,
round(a.abytes/ 1024 / 1024 / 1024,1) 总空间_GB,
round((a.abytes- f.fbytes) / 1024 / 1024 / 1024, 1) 已使用空间_GB,
round(f.fbytes/ 1024 / 1024 / 1024, 1) 剩余空间_GB,
round(a.CanAutoextenBytes/ 1024 / 1024 / 1024, 1) “可自动扩充空间_GB”,
(1 - round(f.fbytes/ a.abytes, 2)) * 100 “已用空间比例”,
round(f.fbytes/ a.abytes, 2) * 100 “剩余空间比例”,
round((f.fbytes+a.CanAutoextenBytes)/ 1024 / 1024 / 1024, 1) “剩余加可自动扩充空间_GB”
from (select tablespace_name, sum(bytes) abytes,
sum (case when autoextensible=‘YES’ then maxBytes-Bytes else 0 end) as CanAutoextenBytes
from dba_data_files group by tablespace_name) a,
(select tablespace_name, sum(bytes) fbytes from dba_free_space group by tablespace_name) f
where a.tablespace_name= f.tablespace_name
order by f.fbytes+a.CanAutoextenBytes desc;
commit;
end TableSpaceStat;

3、创建作业，每周日23：00执行一次

BEGIN
DBMS_SCHEDULER.CREATE_JOB (
job_name => ‘“APPS”.“记录每周表空间状态”’,
job_type => ‘STORED_PROCEDURE’,
job_action => ‘APPS.TABLESPACESTAT’,
number_of_arguments => 0,
start_date => NULL,
repeat_interval => ‘FREQ=WEEKLY;BYDAY=SUN’,
end_date => NULL,
enabled => FALSE,
auto_drop => FALSE,
comments => ‘记录每周表空间状态’);

DBMS_SCHEDULER.SET_ATTRIBUTE( 
         name => '"APPS"."记录每周表空间状态"', 
         attribute => 'logging_level', value => DBMS_SCHEDULER.LOGGING_OFF);

DBMS_SCHEDULER.enable(
         name => '"APPS"."记录每周表空间状态"');

END;

4、查询表数据

select
to_char(RowversionDate, ‘yyyy-mm-dd hh24:mi:ss’),
TABLESPACE_NAME,
总空间_GB,
已使用空间_GB,
剩余空间_GB,
可自动扩充空间_GB,
已用空间比例,
剩余空间比例,
剩余加可自动扩充空间_GB
from FREE_GB;