Mir Sayeed Hassan – Oracle Blog

How to drop all object from Schema in Oracle Database.

In this scenario., we are going to drop all the objects under the schema

Check the status of database.

[oracle@ora21cdb ~]$ sqlplus / as sysdba

SQL*Plus: Release 21.0.0.0.0 - Production on Wed Apr 26 16:43:19 2023
Version 21.3.0.0.0

Copyright (c) 1982, 2021, Oracle. All rights reserved.
Connected to:
Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
Version 21.3.0.0.0

SQL> show pdbs

CON_ID          CON_NAME     OPEN MODE      RESTRICTED
---------- ------------------------------------------
2                PDB$SEED    READ ONLY         NO
3                PDB1        READ WRITE        NO

SQL> alter session set container=pdb1;
Session altered.

SQL> select instance_name, status, open_mode from v$instance, v$database;

INSTANCE_NAME STATUS OPEN_MODE
---------------- ------------ --------------------
ora21c OPEN READ WRITE

Example: Connect to the schema with respective database

SQL> conn hassan1@pdb1
Enter password: **********
Connected.

Check any tables are exist init.

SQL> select table_name from user_tables;
no rows selected

Hence no tables are exist., Let us create some tables & insert data inside the “hassan1” schema

SQL> create table test1 (eno number(10));
Table created.

SQL> insert into test1 values(1);
1 row created.

SQL> create table test2(eno number(10));
Table created.

SQL> insert into test2 values(1);
1 row created.

SQL> create table test3 (eno number(10));
Table created.

SQL> insert into test3 values(10);
1 row created.

SQL> commit;
Commit complete.

Verify the tables or any other objects;

SQL> select table_name from user_tables;

TABLE_NAME
--------------------------------------------------------------------------------
TEST1
TEST2
TEST3

Create a script to identify all the object under the schema

SQL> select 'drop '||object_type||' '|| object_name|| DECODE(OBJECT_TYPE,'TABLE',' CASCADE CONSTRAINTS;',';') from user_objects;

Execute the script

SQL> @/u01/dropall_schema_objects.sql;

'DROP'||OBJECT_TYPE||''||OBJECT_NAME||DECODE(OBJECT_TYPE,'TABLE','CASCADECONSTRA
--------------------------------------------------------------------------------
drop TABLE TEST1 CASCADE CONSTRAINTS;
drop TABLE TEST2 CASCADE CONSTRAINTS;
drop TABLE TEST3 CASCADE CONSTRAINTS;

Create a script to delete all the above listed tables

[oracle@ora21cdb u01]$ vi dropall_objects.sql

drop TABLE TEST1 CASCADE CONSTRAINTS;
drop TABLE TEST2 CASCADE CONSTRAINTS;
drop TABLE TEST3 CASCADE CONSTRAINTS;

Run the script to drop all objects.

SQL> @/u01/dropall_objects.sql;

Table dropped.
Table dropped.
Table dropped.

Now check the deleted object places in recyclebin in “hassan1” schemas

SQL> select * from tab;

TNAME TABTYPE CLUSTERID
-----------------------------------------------------------
BIN$+kDWceCsXL3gUzUcICURIA==$0 TABLE
BIN$+kDWceCtXL3gUzUcICURIA==$0 TABLE
BIN$+kDWceCuXL3gUzUcICURIA==$0 TABLE

Purge the recyclebin objects.

SQL> purge recyclebin;
Recyclebin purged.

SQL> show user
USER is "HASSAN1"

Therefore no objects are exist in this schema.

SQL> select * from user_objects;
no rows selected

Note: In the above scenario., we can drop all the objects from the schemas such as tables, view, sequences, synonym, indexes.

=====Hence tested & verified in our test env=====