codemonkey2480
asked on
Truncate all tabels in a schema
Have a Oracle 10 G database. Ho do I truncate all the tables in a schema?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
You could write a PL/SQL block to do it.
Something like this:
BEGIN
FOR C1 IN (SELECT TABLE_NAME FROM USER_TABLES) LOOP
BEGIN
EXECUTE IMMEDIATE 'TRUNCATE TABLE ' || C1.TABLE_NAME;
EXCEPTION WHEN OTHERS THEN NULL;
END LOOP;
END;
/
The catch is you cannot have any foreign keys. If you do have foreign keys, you will have to disable all of them. This can be done with a similar PL/SQL block. You just need to select the constraints from USER_CONSTRAINTS where CONSTRAINT_TYPE = 'R'.