Oracle SQL - Script to drop all tables, contents and datafiles

Question

I would like to create an SQL script that drops all of the tables and their contents and datafiles from a database.

Is this achievable without specifying specific tables?

Something like this:

DROP ALL TABLESPACES 
  INCLUDING CONTENTS AND DATAFILES;

Any help?

Answer 1

I believe you cannot drop ALL tablespaces--the database disallows dropping the default tablespace, and dropping the SYSTEM tablespace is of course forbidden.

There are a few other special tablespaces and other restrictions on dropping tablespaces as well, particularly when objects have components in multiple tablespaces. Presuming that isn't the case, you can drop most of the tables and clean up the datafiles via DROP TABLESPACE.

Your oracle version isn't specified in the question, but depending on your oracle version, you may have different clauses available when dropping.
I'll outline an approach for as far back as 10G below, but if you are on 12c, you might consider quotas as well. 12c doc here

As you included in your question, dropping (and deleting) datafiles (and tables) can be included when using clause INCLUDING CONTENTS AND DATAFILES in the DROP TABLESPACE command.

Keeping in mind the restrictions on dropping tablespaces(depending on the state of your database, there may be interventions required for some tablespaces), the below may be a starting point. This will iterate through all tablespaces (except the forbidden ones) and drop them one by one, printing any errors encountered. Dan Bracuk mentioned this approach in the comments as well. CASCADE CONSTRAINTS is used here to force dropping despite dependencies objects in other tablespaces.

BEGIN
  FOR TABLESPACE_POINTER IN (
  SELECT TABLESPACE_NAME
  FROM DBA_TABLESPACES
  WHERE TABLESPACE_NAME NOT IN
        ('SYSTEM','SYSAUX',
        (SELECT PROPERTY_VALUE
                            FROM DATABASE_PROPERTIES
                            WHERE PROPERTY_NAME = 'DEFAULT_PERMANENT_TABLESPACE'))
  ) LOOP
    BEGIN
      DBMS_OUTPUT.PUT_LINE(UTL_LMS.FORMAT_MESSAGE('Dropping tablespace:%s', TABLESPACE_POINTER.TABLESPACE_NAME));
      EXECUTE IMMEDIATE 'DROP TABLESPACE ' || TABLESPACE_POINTER.TABLESPACE_NAME || ' INCLUDING CONTENTS AND DATAFILES CASCADE CONSTRAINTS';
      EXCEPTION WHEN OTHERS THEN
      DBMS_OUTPUT.PUT_LINE(UTL_LMS.FORMAT_MESSAGE('Could not drop tablespace: %s Reason: %s', TABLESPACE_POINTER.TABLESPACE_NAME, SQLERRM));
    END;
  END LOOP;
END;
/

EDIT: In light of your comments about not dropping TABLESPACEs but instead TABLEs you created, I would suggest something like the following example.

Since your tables may or may not be in a shared tablespace with other user's tables and coexisting in the same datafiles, I would suggest not worrying about datafiles (or even tablespaces) and instead focus on dropping the TABLES. Oracle can manage the space. If you want to make sure the data allocated for the tables is reclaimed immediately, you can use the PURGE clause.

Here's an example.

We start out with no tables.

SELECT COUNT(*) FROM USER_TABLES;

COUNT(*)  
0        

Then create tables (movies in this example):

CREATE TABLE DIRECTOR(DIRECTOR_ID NUMBER PRIMARY KEY);
CREATE TABLE MOVIE(WOOKIE_ID NUMBER, DIRECTOR_ID REFERENCES DIRECTOR(DIRECTOR_ID));

Table DIRECTOR created.
Table MOVIE created.

The second table has a foreign key to the first, so if we try to drop in the 'wrong order', the database will stop us:

DROP TABLE DIRECTOR;

Error report -
ORA-02449: unique/primary keys in table referenced by foreign keys

To alleviate this, we can CASCADE CONSTRAINTS when we drop, with this syntax:

DROP TABLE DIRECTOR CASCADE CONSTRAINTS PURGE;

Table DIRECTOR dropped.

So to get all your tables, you just need to automate the generation of the drop commands. Much as in the first TABLESPACE example, I'll iterate through the list of This User's tables, and drop them one by one:

BEGIN
  FOR TABLE_POINTER IN (
  SELECT TABLE_NAME
  FROM USER_TABLES ) LOOP
    BEGIN
      DBMS_OUTPUT.PUT_LINE(UTL_LMS.FORMAT_MESSAGE('Dropping table:%s', TABLE_POINTER.TABLE_NAME));
      EXECUTE IMMEDIATE UTL_LMS.FORMAT_MESSAGE('DROP TABLE %s CASCADE CONSTRAINTS PURGE',TABLE_POINTER.TABLE_NAME);
      EXCEPTION WHEN OTHERS THEN
      DBMS_OUTPUT.PUT_LINE(UTL_LMS.FORMAT_MESSAGE('Could not drop table: %s Reason: %s', TABLE_POINTER.TABLE_NAME, SQLERRM));
    END;
  END LOOP;
END;
/

Result:

Dropping table:MOVIE
Dropping table:DIRECTOR

PL/SQL procedure successfully completed.

You can do the same thing for other object types--VIEWs, PACKAGEs, etc. You can also drop a user entirely if needed.