delete all objects for a particular schema
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 18

Thread: delete all objects for a particular schema

Hybrid View

  1. #1
    Join Date
    Oct 2002
    Posts
    284

    delete all objects for a particular schema

    Hello all..

    I have a user who has created buch of tables/views/indexex/...etc. IS there acommand that will delete all his objects.

    I know by using drop user cascade; will drop the user with tables....but here i want a similar command without dropping the user.

    Thanks
    Ron

  2. #2
    Join Date
    Sep 2001
    Location
    NJ, USA
    Posts
    1,287
    Write very small script like:

    spool del_objects.txt
    select 'drop '||object_type||' '||owner||'.'||object_name||';'
    from all_objects
    where owner = 'UR_USER';
    spool off;

    or use dynamic sql for it.

  3. #3
    Join Date
    Apr 2001
    Location
    Bangalore, India
    Posts
    727

    Objects..

    Shestakov

    Is it all_objects or user_objects..? I think all_objects is not appropriate here.


    Thomas
    Thomas Saviour(royxavier@yahoo.com)
    Technical Lead (Databases)
    Thomson Reuters (Markets)

    http://ora600tom.wordpress.com/

  4. #4
    Join Date
    Sep 2001
    Location
    Düsseldorf, Germany.
    Posts
    588

    Re: Objects..

    Originally posted by Thomasps

    Is it all_objects or user_objects..? I think all_objects is not appropriate here.
    Given script can be executed by any user having necessary priviledges. You can also make use of USER_OBJECTS view, but in that case, you must connect to the user whos objects needs to be dropped and then execute the statement.

    Sameer

  5. #5
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    And of course the above script will have to be run N times (where N is some hared-to-define integer > 1) in order to wipe out all objects from a schema. Provided of course that there are some referential integrity constraints among the tables....
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  6. #6
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    I just "drop user x cascade;" and recreate it.

    OOOPS - just re-read the original post - sorry!
    "The power of instruction is seldom of much efficacy except in those happy dispositions where it is almost superfluous" - Gibbon, quoted by R.P.Feynman

  7. #7
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    And on top of that, there are some objects you can't drop if you are not connected as their owner, not even if you are connected as SYS....

    So the only proper way is to drop user with cascade - no other bullet proof method, unless you can connect as the owner of the schema you want to wipe out.
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  8. #8
    Join Date
    Jul 2000
    Location
    Amsterdam
    Posts
    234
    Hi Jmodic,

    What object would that be than.

    I would run the drop query on dba_objects but before that I would make two meta query to disable constraints. 1st foreign keys than rest of the constraints.

    Tycho

  9. #9
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    Originally posted by tycho
    What object would that be than.
    Private database links. Only the owner himself can drop those.
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  10. #10
    Join Date
    Jul 2000
    Location
    Amsterdam
    Posts
    234
    JModic,

    Ever tried deleting from sys.link$

    Tycho

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