每天自動統計表空間的使用情況

邪丶壞人發表於2022-02-21
grant select any table to xie;
grant select any dictionary to xie;
每天自動統計表空間的使用情況：
首先建立表
create table t_tbs_his(tbs_name varchar2(20),tbs_size number,tbs_surp number,tbs_use number,tbs_rate number,time date DEFAULT SYSDATE);
comment on table t_tbs_his is '這個表是用來統計每天表空間的使用情況，大小單位是M，--by rjy';
comment on column t_tbs_his.tbs_surp is '這個欄位是表空間當前還剩多少';
comment on column t_tbs_his.tbs_use is '這個欄位是表空間當前用了多少';
comment on column t_tbs_his.tbs_rate is '這個欄位是表空間當前用了的百分比';
drop table t_tbs_his purge;
select * from t_tbs_his order by 1,time;
truncate table t_tbs_his;
-------------------------------------
然後建立儲存過程
create or replace PROCEDURE p_s_tbs
AS
BEGIN
insert into t_tbs_his SELECT a.tablespace_name "tbs_name",
total/1024/1024 "tbs_size",
free/1024/1024 "tbs_surp",
(total - free)/1024/1024 "tbs_use",
round((total-free)/total,4)*100 "tbs_rate" ,sysdate
FROM (SELECT tablespace_name, SUM(bytes) free
FROM dba_free_space
GROUP BY tablespace_name) a,
(SELECT tablespace_name, SUM(bytes) total
FROM dba_data_files
GROUP BY tablespace_name) b
WHERE a.tablespace_name = b.tablespace_name;
COMMIT;
END;
/
---------------------------------------
最後建立Job
DECLARE
X NUMBER(9);
BEGIN
SYS.DBMS_JOB.SUBMIT
(
job        => X
,what       => 'p_s_tbs;'--儲存過程名，可以是多個，用分號隔開
,next_date  => to_date('2020-09-30 06:30:00','yyyy-mm-dd hh24:mi:ss')
,interval   => 'TRUNC(SYSDATE+1)' --迴圈頻率為一天
,no_parse   => FALSE
);
COMMIT;
END;
--------------------
--刪除job
begin
dbms_job.remove(job);--job為任務號 可用SELECT j.* FROM  DBA_JOBS j
end;
--修改job
begin
dbms_job.change(264,
'initbigareasaleranking;initbigareamanagersalegrowth;initbigareasalegrowth;initbigareamanagersalerate;initbigareasalerate;initcontractsaledevote;initcustomsalerankings;',
to_date('2014.12.12 12:31:03', 'yyyy-mm-dd hh24:mi:ss'),
'TRUNC(SYSDATE + 1) + （12*60+31）/(24*60)');
commit;
end;
停止JOB
SELECT SID,JOB FROM DBA_JOBS_RUNNING;
SELECT SID,SERIAL# FROM V$SESSION WHERE SID='&SID';
ALTER SYSTEM KILL SESSION '&SID,&SERIAL';
begin
DBMS_JOB.BROKEN(&JOB,TRUE);
end;
select job, what, next_date, next_sec, sysdate, failures, broken,interval from user_jobs a;


來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/31098809/viewspace-2856711/，如需轉載，請註明出處，否則將追究法律責任。

相關文章