Home » SQL & PL/SQL » SQL & PL/SQL » ORA-02303: cannot drop or replace a type with type or table dependents (Oracle 11g R2, Windows Server 2003 sp2)
- ORA-02303: cannot drop or replace a type with type or table dependents [message #541592] Wed, 01 February 2012 05:00 Go to next message
Andrey_R
Messages: 441
Registered: January 2012
Location: Israel
Senior Member
Hi all,

i am trying to run a script in which a command tries to create or replace a type.

i get this error:

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





Code: [Select all] [Show/ hide]
SQL>
SQL> --create a test user:
SQL>
SQL> create user tuser identified by tuser
  2  /

User created.

SQL>
SQL> --grant some privileges:
SQL>
SQL> grant create session,resource, select any table, create any type,create any tab
  2  /

Grant succeeded.

SQL>
SQL> conn tuser/tuser@service_name
Connected.
SQL>
SQL> --create the type:
SQL>
SQL> create or replace type t1 as object (obj_type varchar(15))
  2  /

Type created.

SQL>
SQL>
SQL> --create a table dependant on the type:
SQL>
SQL> create table dpntnt_table
  2  (
  3  id1 number(7),
  4  name1 t1
  5  )
  6  /

Table created.

SQL>
SQL> --then change the type:
SQL>
SQL> create or replace type t1 as object (obj_type number(15))
  2  /
create or replace type t1 as object (obj_type number(15))
*
ERROR at line 1:
ORA-02303: cannot drop or replace a type with type or table dependents


SQL>
SQL> --if i'll do FORCE action on the type - it'll corrupt my depandant table:
SQL>
SQL>  drop type t1 FORCE
  2  /

Type dropped.

SQL>
SQL>
SQL>
SQL>
SQL> desc dpntnt_table
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ID1                                                NUMBER(7)

SQL>
SQL>
SQL>
SQL> --if i re-create it - my table is still corrupted:
SQL>
SQL>
SQL> create or replace type t1 as object (obj_type number(15))
  2  /

Type created.

SQL>
SQL>
SQL>
SQL> desc dpntnt_table
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ID1                                                NUMBER(7)

SQL>



--if i re-create it - my table is still corrupted:


create or replace type t1 as object (obj_type number(15))
/



desc dpntnt_table
[/code]


1. If i'll do drop type FORCE what will happen to the dependent object(might be a table for example) ?


2. I understand that this type is already assigned to some object, but i can't seem to find out which one.
how do i find out which object is depending on the type i want to create or replace?

any advice?

Thanks and Regards,


Andrey

[Updated on: Wed, 01 February 2012 05:02]

Report message to a moderator

- Re: ORA-02303: cannot drop or replace a type with type or table dependents [message #541616 is a reply to message #541592] Wed, 01 February 2012 05:49 Go to previous messageGo to next message
Michel Cadot
Messages: 68758
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Just one advice do NOT use FORCE in Oracle statement.
FORCE (in any statement were it is allowed) was put to be able to drop objects with circular dependencies and ONLY in this case.
Oracle always recommend to NOT use it in any other case.

Quote:
how do i find out which object is depending on the type i want to create or replace?


DBA_DEPENDENCIES

Regards
Michel

Report message to a moderator

- Re: ORA-02303: cannot drop or replace a type with type or table dependents [message #541668 is a reply to message #541616] Wed, 01 February 2012 11:45 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9105
Registered: November 2002
Location: California, USA
Senior Member
The proper way to do this would have been to drop the column in the table, then recreate the type, then add the column back to the table. If you have data in the column, then you need to do some copying and renaming to avoid loss of data.

Report message to a moderator

- Re: ORA-02303: cannot drop or replace a type with type or table dependents [message #541669 is a reply to message #541668] Wed, 01 February 2012 11:53 Go to previous messageGo to next message
Andrey_R
Messages: 441
Registered: January 2012
Location: Israel
Senior Member
Thanks for replying.

I didn't understand how i can find the table or object which is depandant on my type in DBA_DEPENDENCIES.

in my example i know how it's called, but in my real problem - i only get the error ora-02303 and don't know which objects are depandant and therefore could be affected, and should be handled.


can you shed some light on this?

Thank you,

Andrey

Report message to a moderator

- Re: ORA-02303: cannot drop or replace a type with type or table dependents [message #541670 is a reply to message #541669] Wed, 01 February 2012 12:23 Go to previous message
Barbara Boehmer
Messages: 9105
Registered: November 2002
Location: California, USA
Senior Member
When you use FORCE, it creates a mess. The dependent object is still the dpntnt_table. The following demonstrates what you originally had, how to show the dependent objects, and what you should have done. Now that you made a mess of things, you need to add the column with the type back to the table. If that does not work, then you may need to drop and recreate the table or even drop and recreate the user to thoroughly clean up the mess that using FORCE leaves behind. You might also need to empty your recycle bin.

-- what you had originally:
Code: [Select all] [Show/ hide]
SCOTT@orcl_11gR2> create user tuser identified by tuser
  2  /

User created.

SCOTT@orcl_11gR2> grant create session, resource to tuser
  2  /

Grant succeeded.

SCOTT@orcl_11gR2> conn tuser/tuser
Connected.
TUSER@orcl_11gR2> create or replace type t1 as object
  2    (obj_type  varchar(15))
  3  /

Type created.

TUSER@orcl_11gR2> create table dpntnt_table
  2    (id1	  number(7),
  3  	name1	  t1)
  4  /

Table created.

TUSER@orcl_11gR2> desc dpntnt_table
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ID1                                                NUMBER(7)
 NAME1                                              T1


-- dependent objects:
Code: [Select all] [Show/ hide]
TUSER@orcl_11gR2> select owner || '.' || name
  2  	    as dependent_objects
  3  from   all_dependencies
  4  where  referenced_owner = 'TUSER'
  5  and    referenced_name = 'T1'
  6  /

DEPENDENT_OBJECTS
-------------------------------------------------------------
TUSER.DPNTNT_TABLE

1 row selected.


-- what you should have done:
Code: [Select all] [Show/ hide]
TUSER@orcl_11gR2> alter table dpntnt_table drop column name1
  2  /

Table altered.

TUSER@orcl_11gR2> desc dpntnt_table
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ID1                                                NUMBER(7)

TUSER@orcl_11gR2> create or replace type t1 as object
  2    (obj_type  number(15))
  3  /

Type created.

TUSER@orcl_11gR2> alter table dpntnt_table add (name1  t1)
  2  /

Table altered.

TUSER@orcl_11gR2> desc dpntnt_table
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ID1                                                NUMBER(7)
 NAME1                                              T1

TUSER@orcl_11gR2>

Report message to a moderator

Previous Topic: Converting data from English to Arabic (merged 3)
Next Topic: oracle sample schemas(hr,oe)
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Fri Jun 06 00:33:36 CDT 2025
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.