Skip to content

How to drop an Oracle database account from the inside

We are not DBAs and so we cannot run the DROP USER X CASCADE command which is a pretty scary command as it wipes out an entire schema. This said we still need to wipe the entire contents of database from time to time for development and testing purposes. In Oracle terms this means dropping all of the user database objects and purging the recycle bin of those dropped objects.

For security reasons I am not going to provide an automated script for deleting an oracle database from the inside. Instead I’m going to explain how to do this manually.

The first thing to do is list the user database objects you will have to drop: 

SQL> select object_type, object_name from user_objects order by object_type, object_name;

OBJECT_TYPE     OBJECT_NAME
----------------------- ------------------------------
INDEX           ADMIN_USER_PK
INDEX           ARCHIVE_FILE_DFI_IDX
INDEX           ARCHIVE_FILE_DIN_DFI_UN
INDEX           ARCHIVE_FILE_DIN_IDX
INDEX           ARCHIVE_FILE_PK
INDEX           ARCHIVE_FILE_RB_DIN_DFI_UN
INDEX           ARCHIVE_FILE_RB_PK
INDEX           ARCHIVE_ROUTE_PK
INDEX           ARCHIVE_ROUTE_SCI_TPI_UN
INDEX           EXPERIMENTS_GID_PK
INDEX           LOGICAL_LIBRARY_LLN_UN
INDEX           LOGICAL_LIBRARY_PK
INDEX           MEDIA_TYPE_MTN_UN
INDEX           MEDIA_TYPE_PK
INDEX           MOUNT_POLICY_PK
INDEX           NAME_PK
INDEX           RQSTER_GRP_RULE_PK
INDEX           RQSTER_RULE_PK
INDEX           STORAGE_CLASS_PK
INDEX           STORAGE_CLASS_SCN_UN
INDEX           TAPE_FILE_ARCHIVE_FILE_ID_IDX
INDEX           TAPE_FILE_PK
INDEX           TAPE_FILE_RB_PK
INDEX           TAPE_FILE_RB_VID_BLOCK_ID_UN
INDEX           TAPE_FILE_SBV_SBF_IDX
INDEX           TAPE_FILE_VID_BLOCK_ID_UN
INDEX           TAPE_FILE_VID_IDX
INDEX           TAPE_PK
INDEX           TAPE_POOL_PK
INDEX           TAPE_POOL_TPN_UN
INDEX           TAPE_TAPE_POOL_ID_IDX
INDEX           TEMP_T_F_I_B_AFI_IDX
INDEX           USAGESTATS_GID_TS_PK
INDEX           VIRTUAL_ORGANIZATION_PK
INDEX           VIRTUAL_ORGANIZATION_VON_UN
SEQUENCE        ARCHIVE_FILE_ID_SEQ
SEQUENCE        LOGICAL_LIBRARY_ID_SEQ
SEQUENCE        MEDIA_TYPE_ID_SEQ
SEQUENCE        STORAGE_CLASS_ID_SEQ
SEQUENCE        TAPE_POOL_ID_SEQ
SEQUENCE        VIRTUAL_ORGANIZATION_ID_SEQ
TABLE           ACTIVITIES_WEIGHTS
TABLE           ADMIN_USER
TABLE           ARCHIVE_FILE
TABLE           ARCHIVE_FILE_RECYCLE_BIN
TABLE           ARCHIVE_ROUTE
TABLE           CTA_CATALOGUE
TABLE           DISK_SYSTEM
TABLE           EXPERIMENTS
TABLE           LOGICAL_LIBRARY
TABLE           MEDIA_TYPE
TABLE           MOUNT_POLICY
TABLE           REQUESTER_GROUP_MOUNT_RULE
TABLE           REQUESTER_MOUNT_RULE
TABLE           STORAGE_CLASS
TABLE           TAPE
TABLE           TAPE_FILE
TABLE           TAPE_FILE_RECYCLE_BIN
TABLE           TAPE_POOL
TABLE           TEMP_TAPE_FILE_INSERTION_BATCH
TABLE           USAGESTATS
TABLE           VIRTUAL_ORGANIZATION

62 rows selected.

SQL>

Start by dropping the tables. You will need to execute both the following SELECT statement and its corresponding DROP TABLE statements in several rounds as different tables are involved in different foreign key relationships. 

SQL> select 'drop table ' || object_name || ';' from user_objects where object_type = 'TABLE' order by object_name;

'DROPTABLE'||OBJECT_NAME||';'
--------------------------------------------------------------------------------
drop table ACTIVITIES_WEIGHTS;
drop table ADMIN_USER;
drop table ARCHIVE_FILE;
drop table ARCHIVE_FILE_RECYCLE_BIN;
drop table ARCHIVE_ROUTE;
drop table CTA_CATALOGUE;
drop table DISK_SYSTEM;
drop table EXPERIMENTS;
drop table LOGICAL_LIBRARY;
drop table MEDIA_TYPE;
drop table MOUNT_POLICY;
drop table REQUESTER_GROUP_MOUNT_RULE;
drop table REQUESTER_MOUNT_RULE;
drop table STORAGE_CLASS;
drop table TAPE;
drop table TAPE_FILE;
drop table TAPE_FILE_RECYCLE_BIN;
drop table TAPE_POOL;
drop table TEMP_TAPE_FILE_INSERTION_BATCH;
drop table USAGESTATS;
drop table VIRTUAL_ORGANIZATION;

21 rows selected.

SQL>
Now paste the above DROP TABLE results back into the SQL> prompt. For some tables you’ll get a success: 
SQL> 
Table dropped.

SQL>
For others you will get a foreign key error: 
SQL> drop table ARCHIVE_FILE
           *
ERROR at line 1:
ORA-02449: unique/primary keys in table referenced by foreign keys

Just keep running the SELECT statement and its corresponding DROP TABLE statements until the SELECT returns no rows like so: 

…
SQL> select 'drop table ' || object_name || ';' from user_objects where object_type = 'TABLE' order by object_name;

'DROPTABLE'||OBJECT_NAME||';'
--------------------------------------------------------------------------------
drop table LOGICAL_LIBRARY;

SQL> drop table LOGICAL_LIBRARY;

Table dropped.

SQL> select 'drop table ' || object_name || ';' from user_objects where object_type = 'TABLE' order by object_name;

no rows selected

SQL>

Looking at all the database objects again you should see that both the tables and their indexes have been dropped: 

SQL> select object_type, object_name from user_objects order by object_type, object_name;

OBJECT_TYPE     OBJECT_NAME
----------------------- ------------------------------
SEQUENCE        ARCHIVE_FILE_ID_SEQ
SEQUENCE        LOGICAL_LIBRARY_ID_SEQ
SEQUENCE        MEDIA_TYPE_ID_SEQ
SEQUENCE        STORAGE_CLASS_ID_SEQ
SEQUENCE        TAPE_POOL_ID_SEQ
SEQUENCE        VIRTUAL_ORGANIZATION_ID_SEQ

6 rows selected.

SQL>

In the example given there are only SEQUENCE objects left. Depending on your database schema there may also be PROCEDURE, SYNONYM and TYPE objects. Delete all 4 object types following the same procedure as for tables in the following order starting with PRCOEDURE objects first:

  1. PROCEDURE.
  2. SYNONYM.
  3. SEQUENCE.
  4. TYPE.

The SELECT statements required to generate the appropriate DROP commands are as follows: 

select 'drop procedure ' || object_name || ';' from user_objects where object_type = 'PROCEDURE' order by object_name;


select 'drop synonym ' || object_name || ';' from user_objects where object_type = 'SYNONYM' order by object_name;

select 'drop sequence ' || object_name || ';' from user_objects where object_type = 'SEQUENCE' order by object_name;

select 'drop type ' || object_name || ';' from user_objects where object_type = 'TYPE' order by object_name;

Check that you now have no databse user objects left at all: 

SQL> select object_type, object_name from user_objects order by object_type, object_name;

no rows selected

SQL>

Finally you can purge the recycle bin of the objects you just dropped: 

SQL> purge recyclebin;

Recyclebin purged.

SQL>


SQL> show recyclebin
SQL>