Truncate or Delete All Tables in an Oracle Schema

It took a lot of poring over forums, faqs and of course a visit to the awesome programmer wiki to finally get a single block of PL/SQL code that did just this!

Most of the code comes from this answer and its basic algorithm is already described in this OTN forum post.

CREATE OR REPLACE PROCEDURE sp_truncate AS 
BEGIN
-- Disable all constraints
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
DBMS_UTILITY.EXEC_DDL_STATEMENT('ALTER TABLE ' || c.owner || '.' || c.table_name || ' disable constraint ' || c.constraint_name);
DBMS_OUTPUT.PUT_LINE('Disabled constraints for table ' || c.table_name);
END LOOP;

-- Truncate data in all tables
FOR i IN (SELECT table_name FROM user_tables)
LOOP
EXECUTE IMMEDIATE 'TRUNCATE TABLE ' || i.table_name;
DBMS_OUTPUT.PUT_LINE('Truncated table ' || i.table_name); 
END LOOP;

-- Enable all constraints
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
DBMS_UTILITY.EXEC_DDL_STATEMENT('ALTER TABLE ' || c.owner || '.' || c.table_name || ' enable constraint ' || c.constraint_name);
DBMS_OUTPUT.PUT_LINE('Enabled constraints for table ' || c.table_name);
END LOOP;

COMMIT;
END sp_truncate;
/

In case you want to be able to issue a rollback then you should change the TRUNCATE TABLE (a DDL statement) to a DELETE FROM (a DML statement) so that Oracle can recover the data from its redo logs.

Executing this stored procedure is as simple as:

exec sp_truncate;