Is possible to delete all contents in scheme in Oracle? I found this script:
for c in (select table_name from user_tables) loop
execute immediate ('drop table '||c.table_name||' cascade constraints);
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:
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.