How to Delete All Objects for a User in Oracle?

Normally, it is simplest to drop and add the user. This is the preferred method if you have system or sysdba access to the database. If you need to have us drop and re-create your user please open support ticket and we will complete this for you.

If you don't have system level access, and want to scrub your schema, the following sql will produce a series of drop statments, which can then be executed.

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

Then, I normally purge the recycle bin to really clean things up. To be honest, I don't see a lot of use for oracle's recycle bin, and wish i could disable it... but anyway:

purge recyclebin;

This will produce a list of drop statements. Not all of them will execute - if you drop with cascade, dropping the PK_* indices will fail. But in the end, you will have a pretty clean schema. Confirm with:

select * from user_objects
  • 14 Users Found This Useful
Was this answer helpful?

Related Articles

How to create an Oracle Wallet?

In order to secure your domain you need to the following:  We complete all necessary...

Can I use my domain name with Apex?

Absolutely, you will have full ftp/sftp access to the server as well.

Do you support SSL/https with APEX Hosting package?

Yes, we do. We can create temporary ssl certificate and when you are ready for a production you...

How do I send e-mail messages from PL/SQL?

Please follow this greate example: below: ( rem...