script to drop all user objects

[elaine3839]

I remember seeing a script to recursively drop all user objects (for a specific schema), so if the user want to refresh their own schema, they can use this script to drop all objects they have first, and then using an dump file to refresh.

The script I was seeing uses a 'for cursor' loop to find all objects use owns and drop them on the fly (use 'execute immediate'). But I can't really it anymore, so I wonder anybody can help me on this?

Thanks!

[TimHall]

Hi.

You might want to try this:

SET SERVEROUTPUT ON SIZE 1000000
BEGIN
  FOR cur_rec IN (SELECT object_name, object_type 
                  FROM   user_objects
                  WHERE  object_type IN ('TABLE', 'VIEW', 'PACKAGE', 'PROCEDURE', 'FUNCTION', 'SEQUENCE')) LOOP
    BEGIN
      IF cur_rec.object_type = 'TABLE' THEN
        EXECUTE IMMEDIATE 'DROP ' || cur_rec.object_type || ' "' || cur_rec.object_name || '" CASCADE CONSTRAINTS';
      ELSE
        EXECUTE IMMEDIATE 'DROP ' || cur_rec.object_type || ' "' || cur_rec.object_name || '"';
      END IF;
    EXCEPTION
      WHEN OTHERS THEN
        DBMS_OUTPUT.put_line('FAILED: DROP ' || cur_rec.object_type || ' "' || cur_rec.object_name || '"');
    END;
  END LOOP;
END;
/

You might want to play around with it a bit.

Also, remember to do a "purge recyclebin" if you are using 10g or you will fill the recyclebin with all the dropped tables.

Cheers

Tim...

[elaine3839]

Thanks Tim, it worked beautifully for me!

[LKBrwn_DBA]

How about just DROP'ping and CREATE'ing the user::

DROP USER MySchema CASCADE;

CREATE USER MySchema ..etc...

[pando]

set trimspool on
set pagesize 0
set line 1000
set feed off
set verify off

spool genera_dropall.sql
select 'prompt Conectando como &&OWNER' || chr(10) || 'connect &&OWNER' DROP_OBJECTS
from dual
union all
select 'spool dropall.log' DROP_OBJECTS
from dual
union all
Select 
trim(case 
     when object_type = 'TABLE' then 'drop ' || object_type 
          || ' ' || owner || '.' || object_name ||' cascade constraints'
     when object_type = 'PACKAGE BODY' then 'prompt PACKAGES BODY'
     when object_type = 'INDEX' then 'prompt INDEXES'
     when object_type = 'DATABASE LINK' then 'drop ' || object_type 
          || ' ' || object_name   
     else 'drop ' || object_type || ' ' || owner || '.' || object_name
     end || ';')
from dba_objects
where owner = '&&OWNER'
and object_type not like '%PARTITION%'
union all
select 'drop public synonym '||synonym_name||';'
from dba_synonyms
where table_owner = '&&OWNER'
and owner = 'PUBLIC'
union all
select 'spool off'
from dual
;
spool off

exit

It drops public synonyms as well so be careful

[yxez]

Hi,

Would it be simplyer to do what LKBrwn said?

[TimHall]

Yes, dropping the user with the CASCADE option is quicker, but there are a number of reasons why I might not pick that option:

1) I might want to only drop certain objects. If you see my script, I limit the object types that I reference.

2) If there are only a few objects I might clean up with a drop, rather than trash the whole user.

3) Some of my users have loads of privileges that I don't want to recreate, especially all the administrative ones granted via packages like the AQ and Java. For these I may decide to do a drop of the objects, rather than the user.

If of course you make a habit of regularly clearing out the contents of users, I guess doing a full drop and recreate of the user is a better option. Horses for courses!

Alot of this is just preference, not science, but it's not something you do all the time, so I don't get precious about it

Cheers

Tim...

[jlakhani]

When I drop a schema it takes like 20 mins or so to drop it..is there a way to increase that response time?

[davey23uk]

yes, have less objects in their schemas.

Seriously, how many object do you have to drop - what are you waiting on when it is happening