Forked from jzi96/Oracle delete all tables in schema.sql
Created
November 14, 2016 08:26
-
-
Save jarek-przygodzki/1b16d2d97186a1492653dbacac8bcc5f to your computer and use it in GitHub Desktop.
Oracle delete all tables in schema (data)
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
SET SERVEROUTPUT ON | |
exec dbms_output.enable(1000000); | |
DECLARE | |
v_typ VARCHAR2(32); | |
v_name VARCHAR2(32); | |
v_constraint VARCHAR2(32); | |
v_sql VARCHAR2(100); | |
CURSOR c_objekte IS | |
SELECT typ, NAME | |
FROM ( | |
select 60 as seq, 'TABLE' as typ, OBJECT_NAME as name from user_objects WHERE OBJECT_TYPE='TABLE' AND not(OBJECT_NAME like 'BIN$%') | |
) | |
ORDER BY seq; | |
BEGIN | |
OPEN c_objekte; | |
LOOP | |
BEGIN | |
FETCH c_objekte | |
INTO v_typ, v_name; | |
EXIT WHEN c_objekte%NOTFOUND; | |
v_sql := 'TRUNCATE TABLE ' || v_name; | |
DBMS_OUTPUT.put_line(v_sql); | |
--EXECUTE IMMEDIATE 'BEGIN TRANSACTION'; | |
EXECUTE IMMEDIATE v_sql; | |
--EXECUTE IMMEDIATE 'COMMIT TRANSACTION'; | |
EXCEPTION | |
WHEN OTHERS THEN | |
DBMS_OUTPUT.put_line('Error in exec ' || v_sql); | |
DBMS_OUTPUT.put_line('retry with delete'); | |
v_sql := 'DELETE FROM ' || v_name; | |
DBMS_OUTPUT.put_line(v_sql); | |
--EXECUTE IMMEDIATE 'BEGIN TRANSACTION'; | |
EXECUTE IMMEDIATE v_sql; | |
END; | |
END LOOP; | |
CLOSE c_objekte; | |
v_sql := 'PURGE RECYCLEBIN'; | |
DBMS_OUTPUT.put_line(v_sql); | |
EXECUTE IMMEDIATE v_sql; | |
END; | |
/ | |
EXIT; |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment