Skip to Main Content


Question and Answer

Connor McDonald

Thanks for the question.

Asked: February 11, 2017 - 9:48 am UTC

Last updated: February 24, 2022 - 7:25 am UTC

Version: 12c

Viewed 100K+ times! This question is

You Asked

Hi Connor,

The below query will drop all the table which are present in the current user A's schema (normal scenario login using user 'A').

select 'drop '||object_type||' '|| object_name || ';' from user_objects where object_type in ('VIEW','PACKAGE','SEQUENCE', 'PROCEDURE', 'FUNCTION', 'INDEX');

But what if this query is run by the DBA with a SYS or SYSDBA login (what all objects are present in the user_objects view when logged in using sys/sysdba user) ? Will it drop all the tables of all the schemas in the database or the query will throw an error? Intention is to only drop objects of Schema 'A'.

I tried running this query using SYS user, it threw error (In the exception block I had just mentioned 'ERR' string), what's the reason for the error.

and Connor said...

Yeah I dont like scripts that drop things in the *current* schema, because if you are logged in to the wrong schema...very very bad things can happen :-)

Maybe something more like this is safer - where you would prompt for an OWNER

undefine owner
set pages 0
set lines 300
set heading off
spool /tmp/qwe123.sql
select 'drop table '||owner||'.'||table_name||' cascade constraints purge;'
from dba_tables
where owner = upper('&&owner')
union all
select 'drop '||object_type||' '||owner||'.'||object_name||';'
from dba_objects
where object_type not in ('TABLE','INDEX','PACKAGE BODY','TRIGGER','LOB')
and object_type not like '%LINK%'
and object_type not like '%PARTITION%'
and owner = upper('&&owner')
order by 1;
spool off
PROMPT Count of Objects =
select object_type,count(*) from dba_objects where owner = upper('&&owner') group by object_type;


  (5 ratings)

Is this answer out of date? If it is, please let us know via a Comment


why not just drop and re-create the schema?

Rajeshwaran, Jeyabal, February 13, 2017 - 8:03 am UTC

Intention is to only drop objects of Schema 'A'.

Since the intention is to drop all the objects for a schema, why not just drop and recreate that schema with all the required privileges (since they "said" - "we have DBA access" to the database), rather than dropping each and every objects.

drop user <user_name> CASCADE;
then re-create that schema again.

Connor McDonald
February 13, 2017 - 10:43 pm UTC

My general principle is I'd rather drop what I need to, rather than drop more and recreate what I really wanted to keep. (Because if I get that 2nd part wrong...I'm in a bad place).

check for schema name

Paul, February 14, 2017 - 7:43 pm UTC

I'm also not a fan of this kind of script; but have written scripts to do it anyway!
Here is how I mitigated the risk of running it in the wrong database or as the wrong schema. It relies on being run in SQL*Plus (it uses the WHENEVER SQLERROR sql*Plus setting)


  lv_dbname     VARCHAR2(30);
  lv_schemaname VARCHAR2(30) := USER;
  /* You will have to change the below DB name and schema name to match your environment.
     This is for safety.  While you make those changes, you will have some time to think 
     about if you REALLY want to drop all tables, packagese, etc from that schema!  */
  SELECT global_name INTO lv_dbname FROM global_name;

  IF lv_dbname <> 'PDB_1'   -- change to the name of your database
    raise_application_error(-20001, 'Only run this in your local DB.  Dont run it in a shared database! (like prod!!!!!)');

  IF lv_schemaname <> 'ABC'  -- change to the name of your schema
    raise_application_error(-20001, 'Must only be run as abc schema.');



<drop loop goes here>

Its still not completely failsafe; but it helps somewhat.
Connor McDonald
February 15, 2017 - 3:50 am UTC

Nice input.

A reader, May 16, 2017 - 6:46 pm UTC

define owner=<your_owner_name>
select 'drop '||object_type||' &owner..'|| object_name || ';' from dba_objects WHERE OWNER=UPPER('&owner') and object_type in ('VIEW','PACKAGE','SEQUENCE', 'PROCEDURE', 'FUNCTION', 'INDEX');

just a define owner fix that

jose antonio vega ruiz, May 16, 2017 - 6:47 pm UTC

select 'drop '||object_type||' &owner..'|| object_name || ';' from dba_objects WHERE OWNER=UPPER('&owner') and object_type in ('VIEW','PACKAGE','SEQUENCE', 'PROCEDURE', 'FUNCTION', 'INDEX');

A reader, February 18, 2022 - 1:42 pm UTC

For testing purposes I put it into a procedure and placed in a schema

Users can reset the schema themselfs.

create or replace procedure reset_schema
for row in ( select 'drop '||object_type||' '|| object_name || '' as query from user_objects WHERE object_type in ('TABLE', 'VIEW','PACKAGE','SEQUENCE', 'PROCEDURE', 'FUNCTION', 'INDEX') and object_name != 'RESET_SCHEMA')
execute immediate row.query;
Connor McDonald
February 24, 2022 - 7:25 am UTC

"reset" is an interesting choice of term :-)

I would go for "totally_destroy" so people know what's about to happen

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library