Drop schema

[pascal01]

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.

[adewri]

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

[DaPi]

Been here before . . .

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

[hrishy]

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

[hrishy]

Hi CelebGuy_dv

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

regards
Hrishy

[abhaysk]

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

[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

[abhaysk]

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.

[hrishy]

Hi Abhay

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

regards
Hrishy