Truncate tables from one schema

1. Create script to disable foreign keys


select 'ALTER TABLE'||' '||OWNER||'.'||TABLE_NAME||' DISABLE CONSTRAINT '||CONSTRAINT_NAME||' ;' from DBA_CONSTRAINTS where CONSTRAINT_TYPE = 'R' and OWNER='schema_owner' and STATUS = 'ENABLED';


2. Create script to enable foreign keys


SELECT 'ALTER TABLE'||' '||owner||'.'||table_name||' ENABLE CONSTRAINT '||constraint_name||' ;' FROM dba_constraints WHERE constraint_type = 'R' and owner='schema_owner' and status = 'ENABLED';


3. Create script to truncate all tables from schema


select 'TRUNCATE TABLE '||OWNER||'.'||TABLE_NAME||' ;' from DBA_TABLES where OWNER='SICAP_LM_03';


4. Run scripts


@DISABLE_CONSTR.SQL
@TRUNCATE_TABLES.SQL
@ENABLE_CONSTR.SQL

OR

You can use this script after you connect with user you want to truncate tables from.

-- disable all FK constraints from user schema
begin

  for C1 in (select TABLE_NAME, CONSTRAINT_NAME from user_CONSTRAINTS where CONSTRAINT_TYPE = 'R' and STATUS = 'ENABLED')
  loop
      begin
         execute immediate 'ALTER TABLE '||C1.TABLE_NAME||' DISABLE CONSTRAINT '||C1.CONSTRAINT_NAME ;
         dbms_output.put_line('Disable '||c1.constraint_name||' on table '||c1.table_name);
      exception
         when others then
               dbms_output.put_line(sqlerrm||' '||c1.table_name);
      end;        
   END LOOP;
 end;
/

-- truncate all tables from user schema
begin
  for c2 in (select table_name from user_tables)        
   loop
      begin
         execute immediate 'truncate table '||c2.table_name;
         dbms_output.put_line('truncate table '||c2.table_name);
      exception
         when others then
               dbms_output.put_line(sqlerrm||' '||c2.table_name);
      end;        
   end LOOP;
end;
/

-- enable all FK constraints from user schema
begin
    for C3 in (select TABLE_NAME, CONSTRAINT_NAME from user_CONSTRAINTS where CONSTRAINT_TYPE = 'R' and STATUS = 'DISABLED')
  loop
      begin
         execute immediate 'ALTER TABLE '||C3.TABLE_NAME||' ENABLE CONSTRAINT '||C3.CONSTRAINT_NAME ;
         dbms_output.put_line('Enable '||c3.constraint_name || ' on table '||c3.table_name);
      exception
         when others then
               dbms_output.put_line(sqlerrm||' '||c3.table_name);
      end;        
   end LOOP;
end;
/