script to drop all user objects
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 9 of 9

Thread: script to drop all user objects

Hybrid View

  1. #1
    Join Date
    May 2001
    Posts
    285

    Question script to drop all user objects

    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!

  2. #2
    Join Date
    Dec 2001
    Location
    UK
    Posts
    1,684
    Hi.

    You might want to try this:

    Code:
    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...
    Tim...
    OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
    OCA PL/SQL Developer
    Oracle ACE Director
    My website: www.oracle-base.com
    My blog: www.oracle-base.com/blog

  3. #3
    Join Date
    May 2001
    Posts
    285

    Thumbs up

    Thanks Tim, it worked beautifully for me!

  4. #4
    Join Date
    Jul 2002
    Location
    Lake Worth, FL
    Posts
    1,477

    Cool


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

    DROP USER MySchema CASCADE;

    CREATE USER MySchema ..etc...



    "The person who says it cannot be done should not interrupt the person doing it." --Chinese Proverb

  5. #5
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    Code:
    
    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

  6. #6
    Join Date
    May 2005
    Location
    Boracay
    Posts
    681
    Hi,

    Would it be simplyer to do what LKBrwn said?



  7. #7
    Join Date
    Dec 2001
    Location
    UK
    Posts
    1,684
    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...
    Tim...
    OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
    OCA PL/SQL Developer
    Oracle ACE Director
    My website: www.oracle-base.com
    My blog: www.oracle-base.com/blog

  8. #8
    Join Date
    Aug 2000
    Location
    Chicago IL
    Posts
    586
    When I drop a schema it takes like 20 mins or so to drop it..is there a way to increase that response time?
    "High Salaries = Happiness = Project Success."

  9. #9
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,333
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width