有时候需要用create table as select * from tables 的方式备份一个表,然后写成存储过程定时执行
每天复制表,表名为每天的日期 yyyymmdd
CREATE OR REPLACE PROCEDURE INSERTA
AUTHID CURRENT_USER IS
V_TABLENAME VARCHAR2(100);
V_SQL VARCHAR2(1000);
BEGIN
SELECT UPPER('TABLENAME')||REPLACE(TO_CHAR(SYSDATE,'YYYY-MM-DD'),'-','') INTO V_TABLENAME FROM DUAL;
V_SQL:= 'CREATE TABLE '||V_TABLENAME||' AS SELECT * FROM TAB';
EXECUTE IMMEDIATE V_SQL;
END;