• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 770
  • Last Modified:

Truncate all tabels in a schema

Have a Oracle 10 G database. Ho do  I truncate all the tables in a schema?

0
codemonkey2480
Asked:
codemonkey2480
1 Solution
 
sventhanCommented:
begin
  for r in (select table_name from user_tables) loop
    execute immediate 'truncate table ' || r.table_name;
  end loop;
end;
0
 
johnsoneSenior Oracle DBACommented:
There is no command that I am aware of that will truncate all the tables.

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'.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

Tackle projects and never again get stuck behind a technical roadblock.
Join Now