23 November 2011

Solution of "ORA-02303: cannot drop or replace a type with type or table dependents" and "ORA-14452: attempt to create, alter or drop an index on temporary table already in use"




Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.2.0
Connected as mennan

SQL> drop type ACTIVATION_TYP;

drop type ACTIVATION_TYP

ORA-02303: cannot drop or replace a type with type or table dependents

SQL> set serveroutput on;
SQL>
SQL> BEGIN
  2    dbms_utility.get_dependency(TYPE   => 'TYPE',
  3                                SCHEMA => 'MENNAN',
  4                                NAME   => 'ACTIVATION_TYP');
  5  END;
  6  /

-
DEPENDENCIES ON MENNAN.ACTIVATION_TYP
------------------------------------------------------------------
*TYPE MENNAN.ACTIVATION_TYP()
*   TYPE BODY MENNAN.ACTIVATION_TYP()
*   TABLE MENNAN.RLM$SESSRSLTTTAB_97272()

PL/SQL procedure successfully completed


SQL> drop table RLM$SESSRSLTTTAB_97272;

drop table RLM$SESSRSLTTTAB_97272

ORA-14452: attempt to create, alter or drop an index on temporary table already in use


SQL>
SQL> select sid, serial# from v$session where sid in ( select sid from v$lock where id1 =
 ( select object_id from user_objects where object_name='RLM$SESSRSLTTTAB_97272'));---- find  sessions to be killed
SQL> alter system kill session '122,22';--kill sessions sid,serial
SQL> drop table RLM$SESSRSLTTTAB_97272;

Table dropped

SQL>
SQL> drop type ACTIVATION_TYP;

Type dropped

SQL>

1 comment:

Unknown said...

See also http://stackoverflow.com/questions/11674807/ora-02303-cannot-drop-or-replace-a-type-with-type-or-table-dependents

for another solution for replacing a type with type dependencies