> -----Original Message-----
> From: Jared Still [mailto:jkstill_at_cybcon.com]
>
> I've found that when dropping a schema owner, it's much easier
> to drop their objects individually, then drop the user.
I hope Mr. Still won't mind if I post another script in the same vein, that I used after hearing him recommend this approach. This one also drops public synonyms for objects in the schema being dropped. Please note that their are other types of objects that can belong to a user, this script does not drop all the objects that could be part of schema.
- drop objects in schema
-- (some objects might still be present, depending on the object type;
-- you should still do a "drop user cascade" if you intend on dropping
-- the user.)
- script can fail if:
- 1) cluster in drop_schema contains tables not in drop_schema
- 2) index in drop_schema enforces primary key/unique constraints
-- on tables not in drop_schema
set trimspool on
set pagesize 0
column sf1 noprint
column sf2 noprint
column sf3 noprint
select
decode (a.object_type, 'CLUSTER', 2, 1) as sf1,
a.owner as sf2,
a.object_name as sf3,
'drop ' || lower (a.object_type) || ' "' ||
replace (a.owner, '"', '""') || '"."' ||
replace (a.object_name, '"', '""') ||
decode (a.object_type, 'TABLE', '" cascade constraints ;', '" ;')
as sql
from
dba_objects a
where
a.owner = '&&drop_schema'
and a.object_type in ('CLUSTER', 'FUNCTION', 'PACKAGE', 'PROCEDURE',
'SEQUENCE', 'SYNONYM', 'SNAPSHOT', 'TABLE',
'VIEW')
union
select
3 as sf1,
b.owner as sf2,
b.index_name as sf3,
'drop index ' || ' "' ||
replace (b.owner, '"', '""') || '"."' ||
replace (b.index_name, '"', '""') || '" ;'
as sql
from
dba_indexes b
where
b.owner = '&&drop_schema'
and b.table_owner != '&&drop_schema'
union
select
4 as sf1,
c.owner as sf2,
c.synonym_name as sf3,
'drop public synonym "' || c.synonym_name || '" ;'
as sql
from
dba_synonyms c
where
c.owner = 'PUBLIC'
and c.table_owner = '&&drop_schema'
order by
1,
2,
3 ;
clear columns
undefine drop_schema
set pagesize 24
Jacques R. Kilchoer
(949) 754-8816
Quest Software, Inc.
8001 Irvine Center Drive
Irvine, California 92618
U.S.A.
http://www.quest.com
Received on Fri May 18 2001 - 19:25:23 CDT
