Drop All Tables Oracle

Drop All Tables Oracle | In this post, we will learn to drop all the tables in the oracle database. The DROP clause in the oracle is used to delete the table from the database and then it will be placed in the recycle bin.

Sometimes when we try to drop the tables, we will face specific problems due to constraints, so let’s go through the query to solve all these constraints problems to drop all tables in oracle.

The SELECT clause will generate a DROP statement for every table in the schema. Here we are using the stored procedure to execute the required condition accordingly.

The given below query can be used to get the name of tables in the Oracle database within a specific user. It will list all the tables.

SELECT * FROM tab;

How to drop all tables in SQL Oracle? Here is the query below which drops all the tables in the schema.

BEGIN
   FOR i in (SELECT 'DROP TABLE ' ||
             table_name ||
             ' CASCADE CONSTRAINTS' stmt from user_tables)
   LOOP
      EXECUTE IMMEDIATE i.stmt;
   END LOOP;
   COMMIT;
END;
/

Output:-

PL/SQL procedure successfully completed.

This is how we drop all tables from schema Oracle. Currently, all the tables are not permanently removed from the Oracle database instead they are placed in the recycle bin. From the below query you can get the list of dropped tables.

SELECT * FROM tab;

Output:-

TNAME                          TABTYPE  CLUSTERID
------------------------------ ------- ----------
BIN$/veP++OsRF6tSYzLjcjjeA==$0 TABLE
BIN$A3XlvX5KSsGH1nZ1Ngw/RA==$0 TABLE
BIN$Bq8RWrj+QLOTHd0FqPHSWQ==$0 TABLE
BIN$D2ebjv87QBWU98Dw1M8vcw==$0 TABLE
BIN$Dyv5yW6IRL6sKgc6d3vTmw==$0 TABLE
BIN$EmoVbeeRQf2XWeUR+Rrcbg==$0 TABLE
BIN$Fg7Kt2HXRV6sFW8tGpjWGw==$0 TABLE

It shows that the tables are not deleted permanently instead they are placed in recycle bin. We can either roll back the table from the recycle bin or delete it permanently.

Query to rollback table from the recycle bin:- FLASHBACK TABLE <tableName> TO BEFORE DROP;

Example to flashback EMP, and DEPT tables from the Oracle database (if they were available before the drop):-

FLASHBACK TABLE dept TO BEFORE DROP;
FLASHBACK TABLE emp TO BEFORE DROP;

The below query will remove all the objects from the recycle bin. See more:- Oracle Purge Recyclebin

PURGE recyclebin;

Oracle Drop All Tables Starting With

In the previous example, we have seen how to drop all tables from schema Oracle. Here we will discuss how to drop the tables starting with the specific word/letter using the LIKE operator.

What is the LIKE operator?
LIKE operator is used in the where clause to search for the specific pattern which matches. Let’s create a table Test for sample demonstration:-

CREATE TABLE test(id NUMBER, name VARCHAR(10));
INSERT INTO test VALUES(1, 'John');
INSERT INTO test VALUES(2, 'Amelia');
SELECT * FROM test;

Now let’s see how to drop all the tables which start with specific patterns using the LIKE operator. Here is the query to drop all the tables which start with “tes” and following characters using the LIKE operator.

 BEGIN
   FOR record IN
     (
       SELECT
         table_name
       FROM
         all_tables
       WHERE
         table_name LIKE 'TES_%'
     )
   LOOP
     EXECUTE immediate 'DROP TABLE  '||
                        record.table_name || 
                        ' CASCADE CONSTRAINTS';
   END LOOP;
 END;
 /

Output:-

PL/SQL procedure successfully completed.

Now the query is executed and placed in recycle bin. We will delete everything from the recycle bin and then check now if the table exists or not.

SHOW recyclebin;
PURGE recyclebin;
SHOW recyclebin;
SELECT * FROM test;

The output of the last query:-

ORA-00942: table or view does not exist.

We can say that the table test doesn’t exist from the above result. This is how we drop all the tables which are starting with some specific word or character.

If you enjoyed this post, share it with your friends. Do you want to share more information about the topic discussed above or do you find anything incorrect? Let us know in the comments. Thank you!

Search