Mir Sayeed Hassan – Oracle Blog

Truncate all the tables data from the Oracle schema in Oracle database 11gR2

sys@TESTDB>;create user mir identified by mirhassan123;
User created.

sys@TESTDB>;grant connect,resource to mir;
Grant succeeded.

sys@TESTDB>;conn mir
Enter password:
Connected.

mir@TESTDB>;sho user
USER is "MIR"

mir@TESTDB>;create table t1(eno number(10));
Table created.

mir@TESTDB>;insert into t1 values(1);
1 row created.

mir@TESTDB>;/
1 row created.
…

mir@TESTDB>;create table t2(eno number(10));
Table created.

mir@TESTDB>;insert into t2 values(1);
1 row created.
mir@TESTDB>;/
1 row created.
….

mir@TESTDB>;commit;
Commit complete.

 Verify the data:

mir@TESTDB>;select table_name from user_tables;
TABLE_NAME
------------------------------
T1
T2

mir@TESTDB>;select * from t1;
 ENO
----------
         1
         1
         1
         1

mir@TESTDB>;select * from t2;
       ENO
----------
         1
         1
         1
         1

Create the procedure to truncate all the tables in its own schema:

CREATE OR REPLACE PROCEDURE mir_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 mir_truncate;
/
Procedure created.

Execute the above procedure to truncate all the tables:

mir@TESTDB>;exec mir_truncate;
PL/SQL procedure successfully completed.

mir@TESTDB>;select * from t1;
no rows selected
mir@TESTDB>;select * from t2;
no rows selected

=====Hence tested & Verified in our test env=====