Drop schema objects
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 7 of 7

Thread: Drop schema objects

Hybrid View

  1. #1
    Join Date
    Jan 2002
    Posts
    152
    Is there any way to drop all schema objects at once?
    Thanks

  2. #2
    Join Date
    Sep 2001
    Posts
    62
    Hi,

    If you trully mean all schema objects (including sys schema)
    then just delete the database by shutting down and deleting all relevant files(datafiles,control files, redo logs etc)

  3. #3
    Join Date
    Jan 2002
    Posts
    152
    I want to drop tables and indexes overall...

  4. #4
    Join Date
    Feb 2002
    Location
    Dallas , Texas
    Posts
    158
    Hi,
    Do you want to do it for a particular user then the code is:

    select ' DROP '||object_type ,object_name||';'|| from user_objects ;

    Hope it works,
    Take Care.

  5. #5
    Join Date
    Sep 2001
    Posts
    62
    There isn't a command that will drop all schema objects (not one that i've come across anyway).
    You could just drop the tablespaces containing the indexes and tables assuming that you have different tablespaces for each type of object.



  6. #6
    Join Date
    Jan 2002
    Location
    India
    Posts
    105
    dear sir ,
    Simply drop the all users with command
    drop user username cascade ;
    and next time create the users
    While creating tablespaces again use "reuse" option

    virajvk
    A Wise Man Knows How much he doesn't know !!!

  7. #7
    Join Date
    Mar 2001
    Location
    New York , New York
    Posts
    577
    Execute the following script first . This will generate a spooled output which you can execute to drop all the objects for a particular schema.

    set linesize 200
    set termout off
    set heading off
    set feedback off
    set echo off

    spool dropobj.sql
    Select 'drop ' || OBJECT_TYPE , OBJECT_NAME || ' CASCADE CONSTRAINTS;' FROM USER_OBJECTS WHERE OBJECT_TYPE LIKE 'TABLE';
    Select 'drop ' || OBJECT_TYPE , OBJECT_NAME || ';' FROM USER_OBJECTS WHERE
    OBJECT_TYPE LIKE 'SEQUENCE';
    Select 'drop ' || OBJECT_TYPE , OBJECT_NAME ||';' FROM USER_OBJECTS where object_type not in ('TABLE' ,'INDEX','SEQUENCE','PACKAGE BODY');
    spool off

    set heading on
    set feedback on
    set termout on
    set echo on

    Ronnie
    ronnie_yours@yahoo.com

    You can if you think you can.

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