--- ORACLE 表空间 JOB 定期维护
CREATE OR REPLACE PROCEDURE ATUO_ORACLE_TABELSPACE IS
V_SQL VARCHAR2(3000);
V_USER_PERCENT VARCHAR2(30);
V_TABLESPACE_NAME VARCHAR2(30);
TYPE V_CURSOR IS REF CURSOR;
V_CUR V_CURSOR;
BEGIN
/*
* ORACLE 表空间定期维护
* 注意:1、建在 sys 账户下运行
* 2、事先创建 sys_tablespace
*/
--------------------------- Oracle 表空间维护 ------------------------------
EXECUTE IMMEDIATE 'TRUNCATE TABLE SYS_TABLESPACE';
EXECUTE IMMEDIATE 'INSERT INTO SYS_TABLESPACE
SELECT A.TABLESPACE_NAME TABLESPACE_NAME,
ROUND(TOTAL / 1024 / 1024, 2) ALL_SIZE,
ROUND(FREE / 1024 / 1024, 2) LEAVINGS_SIZE,
ROUND((TOTAL - FREE) / 1024 / 1024, 2) USER_SZIE,
ROUND((TOTAL - FREE) / TOTAL, 4) * 100 USER_PERCENT
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;
V_SQL := 'SELECT T.TABLESPACE_NAME,T.USER_PERCENT FROM SYS_TABLESPACE T';
OPEN V_CUR FOR V_SQL;
LOOP
FETCH V_CUR
INTO V_TABLESPACE_NAME,V_USER_PERCENT;
EXIT WHEN V_CUR%NOTFOUND;
-- 如果表空间使用率 >= 80 时,增加 3G
IF V_USER_PERCENT >= 80 THEN
V_SQL := 'ALTER TABLESPACE "'||V_TABLESPACE_NAME||'" ADD DATAFILE
''/APP/ORACLE/ORADATA/CCDB/ADD_NEW_DATA'||TO_CHAR(SYSDATE,'YYYYMMDDHH24MISS')||'.DBF'' SIZE 3072M';
EXECUTE IMMEDIATE V_SQL;
END IF;
END LOOP;
EXCEPTION
WHEN OTHERS THEN
BEGIN
DBMS_OUTPUT.PUT_LINE(' ATUO_ORACLE_TABELSPACE IS FAILE ! -- '||SQLERRM);
ROLLBACK;
END;
END ATUO_ORACLE_TABELSPACE;
/
-- Create table
create table SYS_TABLESPACE
(
TABLESPACE_NAME VARCHAR2(30),
ALL_SIZE NUMBER,
LEAVINGS_SIZE NUMBER,
USER_SZIE NUMBER,
USER_PERCENT NUMBER
)
---- SYS 执行 JOB
begin
sys.dbms_job.submit(job => :job,
what => 'ATUO_ORACLE_TABELSPACE;',
next_date => to_date('09-03-2011 03:00:00', 'dd-mm-yyyy hh24:mi:ss'),
interval => 'trunc(sysdate) + 1 + 3 / 24');
commit;
end;
/