Delete all contents in a schema in Oracle

sql oracle plsql
zerosssa picture zerosssa · Apr 28, 2015 · Viewed 78.6k times · Source

Is possible to delete all contents in scheme in Oracle? I found this script:

Begin
for c in (select table_name from user_tables) loop
execute immediate ('drop table '||c.table_name||' cascade constraints);
end loop;
End;

But I would like to know if are there anything to drop everything in the schema, indexes,tables,contraints... but not the schema (drop user ...).

Thanks.

Answer

venki picture venki · Apr 30, 2015

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 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

Also, just to add, the Pl/sql block in your question will delete only tables, it doesn't delete all other objects.

ps: Copied from some website, was useful to me. Tested and working like a charm.

Related questions

SQL error "ORA-01722: invalid number"

A very easy one for someone, The following insert is giving me the ORA-01722: invalid number why? INSERT INTO CUSTOMER VALUES (1,'MALADY','Claire','27 Smith St Caulfield','0419 853 694'); INSERT INTO CUSTOMER VALUES (2,'GIBSON','Jake','27 Smith St Caulfield','0415 713 598…

Read More
Is there a combination of "LIKE" and "IN" in SQL?

In SQL I (sadly) often have to use "LIKE" conditions due to databases that violate nearly every rule of normalization. I can't change that right now. But that's irrelevant to the question. Further, I often use conditions like WHERE something …

Read More
ORA-06502: PL/SQL: numeric or value error: character string buffer too small

I tried the following code different ways, like by taking out the while or the if, but when I put both together (if and while), I always get the error at the end... undefine numero set serveroutput on accept numero …

Read More