-
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.
-
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."
-
"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
-
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
-
Hi CelebGuy_dv
I dont think so my scipt can drop what you have posted..:-)
regards
Hrishy
-
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"
-
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
-
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"
-
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|