1、编写存储过程实现备份限速
CREATE OR REPLACE PROCEDURE “SYSDBA”.“P_FULLBAK”
AS —备份限速读写200M/S
_BAKFILE VARCHAR;
_VSQL VARCHAR;
BEGIN
BAKFILE='DB_DAMENG_FULL’||TO_CHAR(sysdate, ‘YYYY_MM_DD_HH24_MI_SS’);
_VSQL =‘backup database full to ‘||_BAKFILE||’ backupset ‘’/dbbak/dmbak/’||_BAKFILE||‘’‘limit read speed 200 write speed 200 compressed level 1 task thread 2 parallel 2;’;
–print (_VSQL);
EXEC IMMEDIATE _VSQL;
COMMIT;
END;
通过配置代理作业完成备份工作，并定时删除过期备份：
call SP_CREATE_JOB(‘fullbak’,1,0,‘’,0,0,‘’,0,‘’);
call SP_JOB_CONFIG_START(‘fullbak’);
call SP_ADD_JOB_STEP(‘fullbak’, ‘step1’, 0, ‘call “SYSDBA”.“P_FULLBAK”();’, 1, 2, 0, 0, NULL, 0);
call SP_ADD_JOB_STEP(‘fullbak’, ‘cleanup’, 0, ‘CALL SF_BAKSET_BACKUP_DIR_ADD(’‘DISK’‘,’‘/dbbak/dmbak’‘);
CALL SF_BAKSET_REMOVE_BATCH (’‘DISK’‘,now()-5,NULL,NULL);
COMMIT;’, 1, 2, 0, 0, NULL, 0);
call SP_ADD_JOB_SCHEDULE(‘fullbak’, ‘everyday’, 1, 1, 1, 0, 0, ‘03:05:15’, NULL, ‘2023-03-13 20:01:48’, NULL, ‘’);
call SP_JOB_CONFIG_COMMIT(‘fullbak’);
2、编写存储过程实现统计单个模式数据量
----创建统计表
CREATE TABLE TABLECOUNT
(SCHNAME VARCHAR(20), NAME VARCHAR(100), C INT
);
----创建统计存储过程
CREATE OR REPLACE
PROCEDURE TABLE_COUNT
(
SCHNAME VARCHAR
(
20)
)
AS
I_SQL VARCHAR
(
500
)
;
TABNAME VARCHAR
(
100
)
;
C_TAB INT;
CURSOR C;
BEGIN
PRINT 1;
OPEN C FOR ‘SELECT NAME FROM SYSOBJECTS WHERE SUBTYPE$=’‘UTAB’’ AND SCHID=(SELECT ID FROM SYSOBJECTS WHERE NAME=‘’‘||SCHNAME||’‘’ AND TYPE$=‘‘SCH’’)';
LOOP
FETCH C INTO TABNAME;
EXIT
WHEN C%NOTFOUND;
I_SQL:=‘SELECT COUNT(*) FROM ‘||SCHNAME||’.’||TABNAME;
EXEC IMMEDIATE I_SQL INTO C_TAB;
INSERT INTO TABLECOUNT VALUES
(SCHNAME, TABNAME, C_TAB
);
COMMIT;
END LOOP;
END;

------调用存储过程进行统计单个模式数据量
------CALL TABLE_COUNT(‘SYS’)