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

Thread: Drop schema

  1. #1
    Join Date
    Jul 2001
    Location
    Montreal
    Posts
    218

    Question Drop schema

    Could some please advise what stored procedure or DDL command could I use to remove all objects from a user without dropping the user? Thank you.

  2. #2
    Join Date
    Nov 2002
    Location
    New Delhi, INDIA
    Posts
    1,796


    Oh my, why so much trouble, just drop the user with cascade option and recreate it. Isn't that simple.
    Amar
    "There is a difference between knowing the path and walking the path."

    Amar's Blog  Get Firefox!

  3. #3
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    "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

  4. #4
    Join Date
    Jan 2001
    Posts
    2,828
    Hi

    well here you go..but be careful as you should be knowin what u are doin...


    #!/bin/sh
    #Script Created on 12-03-2001 By Hrishikesh
    drpobjects()
    {
    cat > ${spoolfile}.sql << !EOF
    set pause off
    set head off
    set pagesize 0
    set linesize 80
    set feedback off
    spool ${spoolfile}.sql
    select 'set echo on feedback on termout on' from dual;
    select 'spool drop_user_objects' from dual;
    select 'drop ' || object_type || ' ' || object_name || decode(object_type,
    'CLUSTER', ' including tables cascade constraints;',
    'TABLE', ' cascade constraints;',
    ';')
    from user_objects
    where object_type in ('CLUSTER', 'TABLE', 'VIEW', 'SEQUENCE', 'SYNONYM',
    'FUNCTION',
    'PROCEDURE', 'PACKAGE')
    order by object_type;
    select 'spool off' from dual;
    spool off
    spool ${spoolfile}.err
    @${spoolfile}.sql
    set pause on
    set head on
    set pagesize 30
    set feedback on
    exit
    !EOF
    }
    echo "Enter userid whose objects needs to be dropped : \c"
    read uid
    echo "Enter password for ${uid} [No echo]: \c"
    stty -echo
    read pswd
    stty echo
    #echo "\nEnter Connect String for ${1} : \c"
    #read strng

    pswd=$pswd

    spoolfile="./drpobjects"

    drpobjects
    sqlplus -s ${uid}/${pswd} @${spoolfile}.sql
    rm ./drpobjects.*

    Copy and paste that code in a file say somethin like

    drop-all-user-obj.sh and from the say directory run it as

    ./drop-all-user-obj.sh it will prompt u for the username and password of the schems whose objects you need to drop.The user himself wont be dropped..

    regards
    Hrishy

  5. #5
    Join Date
    Jan 2001
    Posts
    2,828
    Hi CelebGuy_dv

    I dont think so my scipt can drop what you have posted..:-)

    regards
    Hrishy

  6. #6
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    Originally posted by hrishy
    Hi CelebGuy_dv

    I dont think so my scipt can drop what you have posted..:-)

    regards
    Hrishy
    U cud modify to do so
    funky...

    "I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."

    "Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"

  7. #7
    Join Date
    Jan 2001
    Posts
    2,828
    Hi abhay

    well dude ..i must confess i liked all the gurls pics posted by CelebGuy_dv..so i really cant be so cruel to drop those gurls ..:-)

    regards
    Hrishy

  8. #8
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    Originally posted by hrishy
    Hi abhay

    well dude ..i must confess i liked all the gurls pics posted by CelebGuy_dv..so i really cant be so cruel to drop those gurls ..:-)

    regards
    Hrishy

    Huh????????

    I cant belive Hrishy.....
    Hey u get so many to see....i mean guls pics....c Ftv ...
    but dont go to such sites ... its my personel advice.....some have lost job by seeing such sites,some companies are really strict here in INDIA....be careful

    atleast i dont dare to open such sites in office...

    Abhay.
    funky...

    "I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."

    "Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"

  9. #9
    Join Date
    Jan 2001
    Posts
    2,828
    Hi Abhay

    Dude i was just kiddin..:-)..you know just kind of fun..

    regards
    Hrishy

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