How to delete all data in Oracle Database with SQL?

Question

I had created a lot of tables and users while testing some SQL command and now I dont remember exactly all the tables and user's name.

But now I want to delete all of it for my big project.So is it possible to delete it in SQL command line ?

http://stackoverflow.com/questions/4393/drop-all-tables-whose-names-begin-with-a-certain-string Just change 'drop' to 'truncate'. — Matt, Aug 08 '15 at 05:47
That question is for SQL server, table details are stored in All_tables/USER_TABLES/dba_TABLES[see here](http://docs.oracle.com/cd/B28359_01/server.111/b28310/tables014.htm) instead of information schema. — Kryptonian, Aug 08 '15 at 06:16
If your question is about to find a solution that selects only tables that you have created and also with same login user and same schema, I think there is no solution! ;). — shA.t, Aug 08 '15 at 06:23

score 8 · Answer 1 · answered Mar 11 '19 at 14:13

This PL/SQL block will be useful to delete all the data in oracle data base

BEGIN
   FOR cur_rec IN (SELECT object_name, object_type
                     FROM user_objects
                    WHERE object_type IN
                             ('TABLE',
                              'VIEW',
                              'PACKAGE',
                              'PROCEDURE',
                              'FUNCTION',
                              'SEQUENCE',
                              'SYNONYM',
                              'PACKAGE BODY'
                             ))
   LOOP
      BEGIN
         IF cur_rec.object_type = 'TABLE'
         THEN
            EXECUTE IMMEDIATE    'DROP '
                              || cur_rec.object_type
                              || ' "'
                              || cur_rec.object_name
                              || '" CASCADE CONSTRAINTS';
         ELSE
            EXECUTE IMMEDIATE    'DROP '
                              || cur_rec.object_type
                              || ' "'
                              || cur_rec.object_name
                              || '"';
         END IF;
      EXCEPTION
         WHEN OTHERS
         THEN
            DBMS_OUTPUT.put_line (   'FAILED: DROP '
                                  || cur_rec.object_type
                                  || ' "'
                                  || cur_rec.object_name
                                  || '"'
                                 );
      END;
   END LOOP;
END;

/

score 1 · Answer 2 · answered Aug 08 '15 at 06:36

1

execute this command:

BEGIN
   FOR table_ IN (SELECT * FROM dba_tables where owner like 'YOUR_SCHEMA') LOOP
       execute immediate 'truncate table ' || table_.owner || '.' || table_.table_name ||' cascade';
   END LOOP;
END;
/

answered Aug 08 '15 at 06:36

Saeed Shahsavan

35
1
10

score 0 · Answer 3 · edited May 04 '16 at 10:22

You have two ways:

you can use function delete instead truncate for this function you should not disable constraints, but it work more slowly because you can rollback this operation if you will go this way:

BEGIN

  FOR table_ IN (SELECT * FROM dba_tables where owner like 'YOUR_SCHEMA') LOOP
   execute immediate 'delete from table ' || table_.owner || '.' || table_.table_name;
  END LOOP;
END;

use truncate it is more faster, but you must diable constraints on table:

begin

  for disable_constraint_ in
    (select * from dba_constraints where owner= 'YOUR_SCHEMA'
  )
  loop
  execute immediate 'alter table ' || disable_constraint_.owner || '.' || disable_constraint_.table_name ||' disable constraint '|| disable_constraint_.constraint_name;
  end loop;
  for table_ in (select * from dba_tables where owner = 'YOUR_SCHEMA')
  loop
    execute immediate 'truncate table ' || table_.owner || '.' ||table_.table_name ||' cascade';
  end loop;
  for enable_constaint_ in (select * from dba_constraints where owner= 'YOUR_SCHEMA')
  loop
  execute immediate 'alter table ' || enable_constaint_.owner || '.' || enable_constaint_.table_name ||' enable constraint '|| enable_constaint_.constraint_name;
 end loop;
end;

score 0 · Answer 4 · answered Aug 08 '15 at 09:19

   BEGIN

        FOR T in (SELECT table_name FROM user_tables) LOOP
          EXECUTE IMMEDIATE 'ALTER TABLE '||T.table_name||' DISABLE ALL CONSTRAINTS';
          --This will disable all the constraint  
        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;

Alter table ... disable all constraints throws a ORA-01735: invalid ALTER TABLE option if there is no constraint defined for the table, which would cause the script to fail to truncate every table if there is at least one table without constraint. You might want to place the execute immediate within a begin -exception block

How to delete all data in Oracle Database with SQL?

4 Answers4