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.

:D

Oh my, why so much trouble, just drop the user with cascade option and recreate it. Isn't that simple.

Been here before . . .

http://www.dbasupport.com/forums/sho...threadid=33679

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

Quote:

---

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;)

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

Quote:

---

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:rolleyes:

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

Abhay.

Hi Abhay

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

regards
Hrishy