Reputation: 14151

How do I delete the data from all my tables in ORACLE 10g

I have an ORACLE schema containing hundreds of tables. I would like to delete the data from all the tables (but don't want to DROP the tables).

Is there an easy way to do this or do I have to write an SQL script that retrieves all the table names and runs the TRUNCATE command on each ?

I would like to delete the data using commands in an SQL-Plus session.

Upvotes: 0

Answers (5)

Saïd

Reputation: 9378

I had to do this recently and wrote a stored procedure which you can run via: exec sp_truncate;. Most of the code is based off this: answer on disabling constraints

    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;
/

Upvotes: 1

David

Reputation: 14151

Putting the details from the OTN Discussion Forums: truncating multiple tables with single query thread into one SQL script gives the following which can be run in an SQL-Plus session:

SET SERVEROUTPUT ON

BEGIN
    -- Disable constraints
    DBMS_OUTPUT.PUT_LINE ('Disabling constraints');
    FOR reg IN (SELECT uc.table_name, uc.constraint_name FROM user_constraints uc) LOOP
        EXECUTE IMMEDIATE 'ALTER TABLE ' || reg.table_name || ' ' || 'DISABLE' ||
            ' CONSTRAINT ' || reg.constraint_name || ' CASCADE';
    END LOOP;

    -- Truncate tables
    DBMS_OUTPUT.PUT_LINE ('Truncating tables');
    FOR reg IN (SELECT table_name FROM user_tables) LOOP
        EXECUTE IMMEDIATE 'TRUNCATE TABLE ' || reg.table_name;
    END LOOP;

    -- Enable constraints
    DBMS_OUTPUT.PUT_LINE ('Enabling constraints');
    FOR reg IN (SELECT uc.table_name, uc.constraint_name FROM user_constraints uc) LOOP
        EXECUTE IMMEDIATE 'ALTER TABLE ' || reg.table_name || ' ' || 'ENABLE' ||
            ' CONSTRAINT ' || reg.constraint_name;
    END LOOP;
END;
/

Upvotes: 0

Adam Musch

Reputation: 13583

If you have any referential integrity constraints (foreign keys) then truncate won't work; you cannot truncate the parent table if any child tables exist, even if the children are empty.

The following PL/SQL should (it's untested, but I've run similar code in the past) iterate over the tables, disabling all the foreign keys, truncating them, then re-enabling all the foreign keys. If a table in another schema has an RI constraint against your table, this script will fail.

set serveroutput on size unlimited
declare
  l_sql       varchar2(2000);
  l_debug     number          := 1;  -- will output results if non-zero
                                     -- will execute sql if 0
  l_drop_user varchar2(30)    := ''  -- set the user whose tables you're dropping
begin
  for i in (select table_name, constraint_name from dba_constraints
             where owner = l_drop_user
               and constraint_type = 'R'
               and status = 'ENABLED')
  loop
    l_sql := 'alter table ' || l_drop_user || '.' || i.table_name || 
             ' disable constraint ' || i.constraint_name;
    if l_debug = 0 then
      execute immediate l_sql;
    else
      dbms_output.put_line(l_sql);
    end if;
  end loop;

  for i in (select table_name from dba_tables
             where owner = l_drop_user
            minus
            select view_name  from dba_views
             where owner = l_drop_user)
  loop
    l_sql := 'truncate table ' || l_drop_user || '.' || i.table_name ;
    if l_debug = 0 then
      execute immediate l_sql;
    else
      dbms_output.put_line(l_sql);
    end if;
  end loop;

  for i in (select table_name, constraint_name from dba_constraints
             where owner = l_drop_user
               and constraint_type = 'R'
               and status = 'DISABLED')
  loop
    l_sql := 'alter table ' || l_drop_user || '.' || i.table_name || 
             ' enable constraint ' || i.constraint_name;
    if l_debug = 0 then
      execute immediate l_sql;
    else
      dbms_output.put_line(l_sql);
    end if;
  end loop;
end;
/

Upvotes: 3