cannot clean out schema

I'm having problems cleaning out a schema. i keep getting the following errors:
ORA-02429: cannot drop index used for enforcement of unique/primary key
ORA-02266: unique/primary keys in table referenced by enabled foreign keys

I've run the script severeal times, and it keeps coming up with the same thing.

Is there i was i can forceabily remove these indexes/tables??

disable your constraint first

Try to disable the constraints that are prevented the drop- look in user_constraints I use the following to disable all of a schemas foreign key constraints

select 'alter table '|| r_owner ||'.'||
table_name ||' disable constraint '||
constraint_name ||';'
from user_constraints
where r_owner = upper('SCHEMA_OWNER')
and constraint_type = 'R';

If you want to clean out schema you have to First disable constraints and then drop the objects. Use the following script and login as that schema owner and try running the script.

Easyway is 'drop user xyz CASCADE' and recreate the user. Do this only when you want to remove everything from the schema. Otherwise control objects removal from script.

Another option is drop the data of the tables by using truncate.
and then drop the objects, if you don't want to disable constraints and then drop.


prompt -- !! WARNING !!
prompt --
Prompt -- Don't run script as SYS or SYSTEM.
Prompt --
prompt -- !! WARNING !!

set echo off;
set verify off;
set feedback off;
set pagesize 0;
set head off;

spool druserobjects.sql

select 'drop '||object_type||' '||owner||'.'||object_name||';'
--from dba_objects
--from all_objects
--from user_objects
where object_type in ('INDEX','PACKAGE','SEQUENCE','SYNONYM','TABLE',
'TRIGGER','VIEW','PROCEDURE','FUNCTION')
and owner in
('XXXXXX','YYYYYY') ;
SPOOL OFF;
SET VERIFY ON;
SET FEEDBACK ON;

sukimac,

When i run your script, it doesn't disable the constraint, it's as if nothing happens. ive spooled it into a file and tried to run the file, but when i check for 'enabled constraints' the same ones are still there??

sreddy,

I've been using a very similar script to clean up a schema... but it's just not removing all objects

::::::::::::::
dis_pktemp.sql
::::::::::::::
ALTER TABLE xxxx DISABLE PRIMARY KEY;
ALTER TABLE yyyy DISABLE PRIMARY KEY;

Use the above syntax and disable all the primary keys first. try using sql in sql script and disable all the tables primary keys first.

it seems like all of these are foreign keys
alter table SYSJCS.RWS_OBJ$ disable constraint RWS_OBJ$_FK_FMT$;
alter table SYSJCS.RWS_OBJ$ disable constraint RWS_OBJ$_FK_LCK$;

when i try that it doesn't work...

Isn't quick and easy to truncate tables and get rid of schema objects. Anyway you are cleaning up the schema. Or drop user cascade and recreate that schema owner.

Use 'alter table xyz drop primary key cascade' so that it drops all the forein keys associated with that primary key.