Drop user username cascade and error ORA-02429: cannot drop index used for enforcement of unique/primary key
=============================================================================================================
Today in our test environment, i am trying to drop a user but i can't success. why ?
SQL>
SQL>
SQL> drop user mybank cascade ;
drop user mybank cascade
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-02429: cannot drop index used for enforcement of unique/primary key
SQL> alter session set tracefile_identifier = 'drop user mybank cascade_1';
Session altered.
SQL> ALTER SESSION SET EVENTS '10046 trace name context forever, level 8';
Session altered.
location of the trace file :-
SELECT s.sid,
s.serial#,
pa.value || '\' || LOWER(SYS_CONTEXT('userenv','instance_name')) ||
'_ora_' || p.spid || '.trc' AS trace_file
FROM v$session s,
v$process p,
v$parameter pa
WHERE pa.name = 'user_dump_dest'
AND s.paddr = p.addr
AND s.audsid = SYS_CONTEXT('USERENV', 'SESSIONID');
in the trace file i found :-
--------------------------------------content of text file-----------------------------
=====================
PARSING IN CURSOR #10 len=402 dep=2 uid=0 oct=3 lid=0 tim=676591965 hv=3607805727 ad='50a6fba0'
select parttype, partcnt, partkeycols, flags, defts#, defpctfree, defpctused, definitrans, defmaxtrans, deftiniexts, defextsize, defminexts, defmaxexts, defextpct, deflists, defgroups, deflogging, spare1, mod(spare2, 256) subparttype, mod(trunc(spare2/256), 256) subpartkeycols, mod(trunc(spare2/65536), 65536) defsubpartcnt, mod(trunc(spare2/4294967296), 256) defhscflags from partobj$ where obj# = :1
END OF STMT
PARSE #10:c=0,e=18,p=0,cr=0,cu=0,mis=0,r=0,dep=2,og=4,tim=676591962
EXEC #10:c=0,e=22,p=0,cr=0,cu=0,mis=0,r=0,dep=2,og=4,tim=676592040
FETCH #10:c=0,e=7,p=0,cr=1,cu=0,mis=0,r=0,dep=2,og=4,tim=676592064
STAT #10 id=1 cnt=0 pid=0 pos=1 obj=262 op='TABLE ACCESS BY INDEX ROWID PARTOBJ$ (cr=1 pr=0 pw=0 time=10 us)'
STAT #10 id=2 cnt=0 pid=1 pos=1 obj=263 op='INDEX UNIQUE SCAN I_PARTOBJ$ (cr=1 pr=0 pw=0 time=6 us)'
=====================
PARSING IN CURSOR #11 len=33 dep=1 uid=0 oct=10 lid=0 tim=676592272 hv=0 ad='8ac3194'
drop index "MYBANK"."PK_STTRNINT"END OF STMT
PARSE #11:c=15625,e=7271,p=0,cr=68,cu=0,mis=1,r=0,dep=1,og=4,tim=676592269
=====================
PARSING IN CURSOR #12 len=652 dep=2 uid=39 oct=47 lid=39 tim=676592536 hv=3055982128 ad='43eec67c'
BEGIN
BEGIN
IF (xdb.DBMS_XDBZ0.is_hierarchy_enabled_internal(sys.dictionary_obj_owner, sys.dictionary_obj_name, sys.dictionary_obj_owner)) THEN
xdb.XDB_PITRIG_PKG.pitrig_truncate(sys.dictionary_obj_owner, sys.dictionary_obj_name);
END IF;
EXCEPTION
WHEN OTHERS THEN
null;
END;
BEGIN
IF (xdb.DBMS_XDBZ0.is_hierarchy_enabled_internal(sys.dictionary_obj_owner, sys.dictionary_obj_name, sys.dictionary_obj_owner, xdb.DBMS_XDBZ.IS_ENABLED_RESMETADATA)) THEN
xdb.XDB_PITRIG_PKG.pitrig_dropmetadata(sys.dictionary_obj_owner, sys.dictionary_obj_name);
END IF;
EXCEPTION
WHEN OTHERS THEN
null;
END;
END;
END OF STMT
PARSE #12:c=0,e=31,p=0,cr=0,cu=0,mis=0,r=0,dep=2,og=1,tim=676592532
EXEC #3:c=0,e=22,p=0,cr=0,cu=0,mis=0,r=0,dep=3,og=1,tim=676592790
FETCH #3:c=0,e=39,p=0,cr=7,cu=0,mis=0,r=1,dep=3,og=1,tim=676592848
EXEC #13:c=0,e=91,p=0,cr=0,cu=0,mis=0,r=0,dep=3,og=1,tim=676592986
FETCH #13:c=0,e=374,p=0,cr=58,cu=0,mis=0,r=1,dep=3,og=1,tim=676593377
EXEC #14:c=0,e=21,p=0,cr=0,cu=0,mis=0,r=0,dep=3,og=1,tim=676593444
FETCH #14:c=0,e=30,p=0,cr=8,cu=0,mis=0,r=1,dep=3,og=1,tim=676593492
=====================
===================================end content of tracefile=============================================================
=================================================================================================
Solution:-
at last i identify that, droping user are holding an unique index(Primary key) with referencing another user's table .
index script like below's :-
CREATE UNIQUE INDEX MYBANK.PK_STTRNINT ON ISLBAS.STTRNINT
(COMPCODE, INDOCTYP, INSUBTYP, INDOCNUM, INDOCDAT,
SERLNUMB)
After this identification, i just drop the referencing user first. then i succed to drop my target user.
OR
alter table
islbas.STTRNINT disable constraint PK_STTRNINT ;
drop index
"MYBANK"."PK_STTRNINT"
CREATE UNIQUE INDEX
islbas.PK_STTRNINT ON ISLBAS.STTRNINT
(COMPCODE, INDOCTYP, INSUBTYP, INDOCNUM, INDOCDAT,
SERLNUMB)
