CREATE OR REPLACE PROCEDURE SCHEDULED_DELETE_DATA3 IS
M_SIZE NUMBER;
TYPE ST_TABLE_NAME_TYPE IS TABLE OF VARCHAR2(255);
V_ST_TABLE_NAME ST_TABLE_NAME_TYPE;
V_PARTITION_TRUNCATE_SQL VARCHAR2(500);
BEGIN
--V_ST_TABLE_NAME:= ST_TABLE_NAME_TYPE('MZP_TEST');
SELECT TABLE_NAME BULK COLLECT INTO V_ST_TABLE_NAME from USER_TABLES;
FOR I IN 1 .. V_ST_TABLE_NAME.COUNT LOOP
dbms_output.put_line(V_ST_TABLE_NAME(I));
SELECT NUM_ROWS * AVG_ROW_LEN/1024 INTO M_SIZE FROM USER_TABLES WHERE TABLE_NAME = V_ST_TABLE_NAME(I);
IF (M_SIZE<0) THEN
V_PARTITION_TRUNCATE_SQL:= 'TRUNCATE TABLE '|| V_ST_TABLE_NAME(I);
execute immediate V_PARTITION_TRUNCATE_SQL;
END IF;
END LOOP;
END;
oracle 存储过程表超过指定字节大小就清除表数据
最新推荐文章于 2021-04-07 08:08:57 发布