CODEWITHSUNDEEP
sqloracle-database
Lily
Lily

Reputation: 6012

SQL: delete all the data from all available tables

I am using oracle DB to maintain more than 30 tables, how can I delete all the data from all the tables? I only want to delete the data but not drop the tables.

Upvotes: 28

Views: 50158

Answers (9)

Forage
Forage

Reputation: 2900

A slight variation on Andomar's answer to truncate all tables for a specific user instead of only those of the current user:

SELECT 'TRUNCATE TABLE ' || owner || '.' || table_name || ';' FROM all_tables WHERE owner = 'user/schema'

Replace the user/schema bit above with the name of the user/schema (between the quotes) you are interested in.

Upvotes: 1

Ayush Aggarwal
Ayush Aggarwal

Reputation: 11

I created this stored proc, using the answers mentioned above. This works perfectly without any errors or exceptions.

    create or replace PROCEDURE DELETE_ALL_DATA
AS 
cursor r1 is select * from user_constraints;
cursor r2 is select * from user_tables;
cursor r3 is select * from user_constraints;
cursor r4 is select * from user_tables;

BEGIN

    FOR c1 IN r1
  loop
    for c2 in r2
    loop
        begin
       if c1.table_name = c2.table_name and c1.status = 'ENABLED' THEN
        dbms_utility.exec_ddl_statement('alter table ' || c1.owner || '.' || c1.table_name || ' disable constraint ' || c1.constraint_name);
       end if;
        EXCEPTION
         WHEN NO_DATA_FOUND
           THEN
           continue;
         WHEN OTHERS 
           THEN
           continue;
           end;
    end loop;
  END LOOP;

    FOR T in (SELECT table_name FROM user_tables) LOOP
      begin
      EXECUTE IMMEDIATE 'TRUNCATE TABLE '||T.table_name;
      EXCEPTION
         WHEN NO_DATA_FOUND
           THEN
           continue;
         WHEN OTHERS 
           THEN
           continue;
           end;
    END LOOP;

    FOR c1 IN r3
  loop
    for c2 in r4
    loop
        begin
       if c1.table_name = c2.table_name and c1.status = 'DISABLED' THEN
        dbms_utility.exec_ddl_statement('alter table ' || c1.owner || '.' || c1.table_name || ' enable constraint ' || c1.constraint_name);
       end if;
        EXCEPTION
         WHEN NO_DATA_FOUND
           THEN
           continue;
         WHEN OTHERS 
           THEN
           continue;
           end;
    end loop;
  END LOOP;

    commit;
END DELETE_ALL_DATA;

Upvotes: 1

Brahmareddy K
Brahmareddy K

Reputation: 614

Delete all the data from all tables in oracle

DECLARE
  str VARCHAR2(100);
BEGIN
  FOR i IN
  (SELECT object_name FROM user_objects WHERE object_type='TABLE'
  )
  LOOP
    str := 'Truncate table '|| i.object_name;
    EXECUTE IMMEDIATE str;
    DBMS_OUTPUT.PUT_LINE('table data deleted :' || i.object_name);
  END LOOP;
END;

For more info: http://www.oracleinformation.com/2014/10/delete-all-the-data-from-all-tables.html

Upvotes: 0

michaldo
michaldo

Reputation: 351

There is no command 'ALTER TABLE XXX DISABLE ALL CONSTRAINTS'

I propose this;

BEGIN
  FOR c IN (SELECT table_name, constraint_name FROM user_constraints WHERE constraint_type = 'R')
  LOOP
    EXECUTE IMMEDIATE ('alter table ' || c.table_name || ' disable constraint ' || c.constraint_name);
  END LOOP;
  FOR c IN (SELECT table_name FROM user_tables)
  LOOP
    EXECUTE IMMEDIATE ('truncate table ' || c.table_name);
  END LOOP;
  FOR c IN (SELECT table_name, constraint_name FROM user_constraints WHERE constraint_type = 'R')
  LOOP
    EXECUTE IMMEDIATE ('alter table ' || c.table_name || ' enable constraint ' || c.constraint_name);
  END LOOP;
END;

Upvotes: 35

YOusaFZai
YOusaFZai

Reputation: 698

these two line script are the best

EXEC sp_MSForEachTable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL'
GO

EXEC sp_MSForEachTable 'DELETE FROM ?'
GO

Upvotes: -3

DCookie
DCookie

Reputation: 43523

To address the issue of constraints, something like this should work:

BEGIN

    FOR T in (SELECT table_name FROM user_tables) LOOP
      EXECUTE IMMEDIATE 'ALTER TABLE '||T.table_name||' DISABLE ALL CONSTRAINTS';
    END LOOP;

    FOR T in (SELECT table_name FROM user_tables) LOOP
      EXECUTE IMMEDIATE 'TRUNCATE TABLE '||T.table_name;
    END LOOP;

    FOR T in (SELECT table_name FROM user_tables) LOOP
      EXECUTE IMMEDIATE 'ALTER TABLE '||T.table_name||' ENABLE ALL CONSTRAINTS';
    END LOOP;
END;

Upvotes: 18

Gary Myers
Gary Myers

Reputation: 35401

The potential drawback with a truncate is that it may fail on referential integrity constraints. So you'd want to disable foreign key constraints first, then do the truncate, then re-enable constraints. The 'plus' with cloning the schema (exp and imp) is that you could also drop and recreate the tablespace too (which you may want to do if you want to reclaim some physical disk space as a result of removing all the data).

Upvotes: 4

Andomar
Andomar

Reputation: 238048

Generate a script to truncate (= remove all rows from) all tables:

select 'truncate table ' || table_name || ';' from user_tables

And then execute the script.

Upvotes: 32

BCS
BCS

Reputation: 78506

Clone the schema and then drop the old tables?

Upvotes: 2

Related Questions