Skip to Main Content
  • Questions
  • Fastest way to delete a large schema

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Dieter.

Asked: November 03, 2020 - 9:26 am UTC

Last updated: July 03, 2025 - 12:47 pm UTC

Version: 19.3

Viewed 10K+ times! This question is

You Asked

In my CI workflow I must often remove a complete schema with a very large number of objects in it.
Simply using "drop user <user> cascade" works but takes a long time of up to 5 minutes.
Starting by first removing constraints and tables before using "drop user <user> cascade" improves the performance a little but is still quite slow.
I was wondering what the fastest way to remove a large schema might be?
Thank you,
Dieter

and Connor said...

drop user cascade can often take a long time because we must do it atomically (ie, if it crashes we need to roll it all back).

I often do the following to get a speed boost:

----------
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','JOB')
and object_type not like '%LINK%'
and object_type not like '%PARTITION%'
and owner = upper('&&owner')
union all
select 'exec dbms_scheduler.drop_job('''||owner||'.'||object_name||''',force=>true);'
from dba_objects
where object_type = 'JOB'
and owner = upper('&&owner')
order by 1;
spool off
@/tmp/qwe123
PROMPT Count of Objects =
select object_type,count(*) from dba_objects where owner = upper('&&owner') group by object_type;
-----------------

which gets rid of most of the common object types, and then once that is done, run your drop user cascade

Rating

  (5 ratings)

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

Comments

A reader, November 04, 2020 - 4:07 am UTC


It worked for me.

A reader, July 26, 2023 - 1:39 pm UTC

This query worked for me. User dropped in just few minutes
Thanks you very much
Connor McDonald
August 03, 2023 - 10:30 am UTC

glad we could help

Ha, October 30, 2023 - 1:23 am UTC

great solution. Thanks very much
Connor McDonald
October 30, 2023 - 5:42 am UTC

glad we could help

Drop schema Vs drop objects....

Rajeshwaran, July 01, 2025 - 2:16 pm UTC

But Tom was suggesting the other way here,

do the drop schema rather than dropping individual object types

https://asktom.oracle.com/ords/asktom.search?tag=dropping-objects-or-user

and

https://asktom.oracle.com/ords/asktom.search?tag=dropping-objects-or-user#362079300346062551

any comments to those ?
Chris Saxon
July 03, 2025 - 12:47 pm UTC

It seems to me Tom was more focused on simplicity than speed - DROP USER ... CASCADE is undeniably simpler.

I would still say dropping a schema is still uncommon. The rise of CI/CD pipelines that build the whole schema from scratch means it's more common now. Whether it's common enough to be worth putting development effort into optimizing drop user depends on how pressing a problem this is for customers.

More to Explore

Administration

Need more information on Administration? Check out the Administrators guide for the Oracle Database