oracle大表备份删除,oracle 批量删除从存储过程（物理备份），表及备份表

/*

1.删除存储过程

2.备份data表

3.删除data表

*/

DECLARE

V_SQL       VARCHAR2(2000);--删除的语句

CURSOR C1 IS --要删除的存储过程名

select * from (

select distinct upper(owner)||'.'||upper(object_name) procedures from dba_procedures)WHERE procedures in (

'ERMP_DATA.CSTM_TEN_HTZQRZ_PROC', 'ERMP_DATA.CUSTOMER_SCLIENT_01_2_PROC'

);

CURSOR T1 IS--要删除的表名

select * from (

select OWNER||'.'||TABLE_NAME TABLE_NAME from dba_tables where owner ='ERMP_DATA'

) where TABLE_NAME in (

'ERMP_DATA.CSTM_TEN_HTZQRZ','ERMP_DATA.CUSTOMER_SCLIENT_01_2','ERMP_DATA.LQD_NSFR1','ERMP_DATA.MKT_CURRENCY_EXCHANGE_RATE',

);

BEGIN

for p1 in C1 loop

V_SQL:= ' DROP PROCEDURE '|| p1.procedures;--下线存储过程

dbms_output.put_line(V_SQL);

EXECUTE IMMEDIATE V_SQL;

end loop;

for p2 in T1 loop

V_SQL:= ' CREATE TABLE ERMP_HIST.'||substr(p2.TABLE_NAME,11)||' AS SELECT * FROM '|| p2.TABLE_NAME; --备份表

dbms_output.put_line(V_SQL);

EXECUTE IMMEDIATE V_SQL;

V_SQL:= ' DROP TABLE '|| p2.TABLE_NAME;--删除表

dbms_output.put_line(V_SQL);

EXECUTE IMMEDIATE V_SQL;

end loop;

END;

/