|
Category :
Database
Resources -> Database Administration
DB Version
:
Oracle 8i /
Oracle 9i
OS Details :
Sun Solaris9
One
of the most common errors that one comes across when dealing with
object types is ORA-2303. This error usually occurs when you try to
drop or replace an object type without removing the dependents first.
Here's an example scenario:
SQL> create or replace type
abc as object
2 (col1 number);
3 /
Type created.
SQL> create or replace type abc_array as table of abc;
2 /
Type created.
SQL> drop type abc;
drop type abc
*
ERROR at line 1:
ORA-02303: cannot drop or replace a type with type or table dependents
Fix for the ORA-02303 error is to
drop the dependent objects first before trying to remove or replace
the parent object type. The following queries can be used to find
dependent object types:
-- Find nested table dependencies
select owner, parent_table_name, parent_table_column
from dba_nested_tables
where (table_type_owner, table_type_name) in
(select owner, type_name
from dba_coll_types
where elem_type_owner = '<typeOwner>'
and elem_type_name = '<typeName>');
-- Find VARRAY dependencies
select owner, parent_table_name, parent_table_column
from dba_varrays
where (type_owner, type_name) in
(select owner, type_name
from dba_coll_types
where elem_type_owner = '<typeOwner>'
and elem_type_name = '<typeName');
-- Find object table dependencies
select owner, table_name
from dba_object_tables
where table_type_owner = '<typeOwner>'
and table_type = '<typeName>'
and nested = 'NO';
|
