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

Thread: delete all objects for a particular schema

  1. #11
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    Originally posted by tycho
    JModic,

    Ever tried deleting from sys.link$

    Tycho
    As a matter of fact, I did (among other database "hacking" games), however I can't see how this is relevant here. And BTW, I would not be too comfortable with publicly suggesting to mess up with database dictionary on an open forum like this one - some inexperienced user might even take it seriously and try something simmilar on the production system.....

    The isue is not if something is possible at all (if it would not be, how would oracle then do it during "DROP USER" action), the isue is if you can do it with supported actions, like legal DDL statements and PL/SQL APIs. With this in mind I say again: It is not possible to create bullet-proof script to drop all the objects from user schema if you can not connect as the schema owner during the script execution.
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  2. #12
    Join Date
    Sep 2001
    Location
    NJ, USA
    Posts
    1,287
    Originally posted by jmodic
    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....
    Yes this is absolutly correct.

    But somebody may try to write "real" script that has to bulid tree(s)
    of object's dependences then pass this tree from leafs to roots
    and generate right order of "DROP..." commands.
    But I guess that executing the "small" version of script 2-3-4... times a little bit easy.

  3. #13
    Join Date
    Jul 2000
    Location
    Amsterdam
    Posts
    234
    JMdodic,

    Dropping and recreating the user can have some messy side effects as well.

    All object privileges granted by sys for the user are gone so I am also not happy with this suggestion.


    Tycho

  4. #14
    Join Date
    Feb 2000
    Location
    New York,U.S.A.
    Posts
    245
    I would find out the roles and privileges the user has. Drop the user and recreate the user as exactly as the dropped one.

  5. #15
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,448
    Originally posted by tycho
    JMdodic,

    Dropping and recreating the user can have some messy side effects as well.

    All object privileges granted by sys for the user are gone so I am also not happy with this suggestion.


    Tycho
    if you like delete data dictionary go ahead and do so if that makes you happier

  6. #16
    Join Date
    Feb 2001
    Location
    UAE
    Posts
    304
    Never mess up with data dictionary objects. Even I feel dropping and recreating the user is the safest bet.
    Agasimani
    OCP(10g/9i/8i/8)

  7. #17
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    stuff that you might want to preserve -- role memebership, passwords, grants etc. -- are all easy things to save should you want to drop the user.
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  8. #18
    Join Date
    Dec 2001
    Location
    Brazil
    Posts
    282
    export user/pass file=blah.dump grants=y

    drop user errr cascade;

    create user errr identified by errr password expire;

    import user/pass file=blah.dump grants=y




    Hummmmmmm.
    Hummmmmmm.

    F.

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