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博客

每天自动统计表空间的使用情况

最新文章