PL/SQL script which removes all data from all tables of a database
I spent quite a bit of time today, because I needed something that removes all data from my oracle 11.0 database. I didn’t want to perform a drop command, as the tables should stay there. I tried it with the truncate command which failed because of the constraints.
This script disables all constraints, performs truncate commands on each table and re-enables the constraints. It’s generic and should work out of the box, that means you don’t have to change anything. Make sure that you run it as database user and not as sys user, otherwise it will fuck up the content of your tables 
NAME:
clean_stm_database.sql
PURPOSE:
This script first disables all constraints on all tables. In
the second step, all data is being truncated on all tables.
Last but not least, all constraints are re-enabled
INSTRUCTIONS:
Run this script as database user (e.g. STM_T)
REVISIONS:
Ver Date Author Description
——— ———- ————— ——————————-
1.0.0 09.09.2011 Patrick Breiter draft version
******************************************************************************/
rem DROP procedure clean_database;
SET serveroutput ON;
CREATE OR REPLACE procedure clean_database
AS
table_name varchar2(255);
enabled_constraint varchar2(255);
disabled_constraint varchar2(255);
BEGIN
dbms_output.enable(1000000);
– this loop disables all constraints in every table
FOR c IN
(SELECT c.owner, c.table_name, c.constraint_name
FROM user_constraints c, user_tables t
WHERE c.table_name = t.table_name
AND c.STATUS = ‘ENABLED’
ORDER BY c.constraint_type DESC)
LOOP
disabled_constraint:=c.constraint_name;
dbms_utility.exec_ddl_statement(‘alter table ‘ || c.owner || ‘.’ || c.table_name || ‘ disable constraint ‘ || c.constraint_name);
dbms_output.put_line(‘disabled: ‘ || disabled_constraint);
END LOOP;
– this loop truncates all data from every table
FOR tab IN (SELECT table_name FROM user_tables ORDER BY table_name DESC)
loop
table_name:=tab.table_name;
execute immediate ‘truncate table ‘|| table_name;
dbms_output.put_line(‘truncated: ‘ || table_name);
end loop;
– this loop re-enables all constraints on all tables
FOR c IN
(SELECT c.owner, c.table_name, c.constraint_name
FROM user_constraints c, user_tables t
WHERE c.table_name = t.table_name
AND c.STATUS = ‘DISABLED’
ORDER BY c.constraint_type)
LOOP
enabled_constraint:=c.constraint_name;
dbms_utility.exec_ddl_statement(‘alter table ‘ || c.owner || ‘.’ || c.table_name || ‘ enable constraint ‘ || c.constraint_name);
dbms_output.put_line(‘re-enabled: ‘ || disabled_constraint);
END LOOP;
END;
/
execute clean_database;
An Twitter senden
