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;

Run PL/SQL Fragments Directly in SqlPlus*

BEGIN
FOR i IN (SELECT table_name FROM user_tables)
LOOP
EXECUTE IMMEDIATE 'TRUNCATE TABLE ' || i.table_name;
END LOOP;
COMMIT;
END;

Ok so you want to run the fragment above that you got from a forum post somewhere and only have access to the command line SqlPlus* utility to do so.
You add more semi-colons, press the enter key repeatedly and hope it magically parses the snippet and determines that you must want to execute the contents of the BEGIN ... END block right?

Unfortunately it doesn't work that way :). You need to tell it to parse the contents of the buffer by adding the forward slash / to the end. Voila!

BEGIN
FOR i IN (SELECT table_name FROM user_tables)
LOOP
EXECUTE IMMEDIATE 'TRUNCATE TABLE ' || i.table_name;
END LOOP;
COMMIT;
END;
/

Please see this post for truncating/deleting all tables in a schema using a stored procedure in PL/SQL.