cannot clean out schema
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 8 of 8

Thread: cannot clean out schema

  1. #1
    Join Date
    Oct 2000
    Posts
    56
    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??

  2. #2
    Join Date
    Nov 2000
    Posts
    245

    disable your constraint first

  3. #3
    Join Date
    Aug 2000
    Posts
    132
    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';

  4. #4
    Join Date
    Feb 2000
    Location
    Washington DC
    Posts
    1,843
    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.

    <font face=courier>
    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;
    </font>

  5. #5
    Join Date
    Oct 2000
    Posts
    56
    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

  6. #6
    Join Date
    Feb 2000
    Location
    Washington DC
    Posts
    1,843
    ::::::::::::::
    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.

  7. #7
    Join Date
    Oct 2000
    Posts
    56
    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...

  8. #8
    Join Date
    Feb 2000
    Location
    Washington DC
    Posts
    1,843
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width