Trying to get the DDL for a table we obtain the error:
SQL> select dbms_metadata.get_ddl('TABLE','SYS_NT#Y85_UAGY5$7BMV2SL63J7JM','&owner') from dual;
ERROR:
ORA-31603: object "SYS_NT#Y85_UAGY5$7BMV2SL63J7JM" of type TABLE not found in schema "&owner"
ORA-06512: at "SYS.DBMS_METADATA", line 6737
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 105
ORA-06512: at "SYS.DBMS_METADATA", line 6724
ORA-06512: at "SYS.DBMS_METADATA", line 9740
ORA-06512: at line 1
no rows selected
Is the table exists?
SQL> select OBJECT_NAME Name, OBJECT_TYPE, STATUS from dba_objects where owner='...' and OBJECT_NAME like '%SYS_%';
NAME OBJECT_TYPE STATUS
------------------------------ ----------- ----------
SYS_NT#Y85_UAGY5$7BMV2SL63J7JM TABLE INVALID
Explanation: objects with name starting from SYS_NT# is "system generated nested tables ID"
Run the 2 queries, they show the PARENT_TABLE:
set lines 400
col TABLE_NAME for a18
col TABLE_TYPE_OWNER for a12
col TABLE_TYPE_NAME for a24
col PARENT_TABLE_NAME for a24
col PARENT_TABLE_COLUMN for a24
select TABLE_NAME,TABLE_TYPE_OWNER,TABLE_TYPE_NAME,PARENT_TABLE_NAME,PARENT_TABLE_COLUMN from dba_nested_tables where owner='&owner';
col owner for a12
col nested_table for a24
select dio.owner,
dio.object_name as nested_table,
dio.status,
nt.obj#,
do.owner,
do.object_name as parent_table
from dba_invalid_objects dio,
ntab$ nt,
dba_objects do
where dio.object_id=nt.ntab#
and nt.obj#=do.object_id
order by dio.object_id;
No comments:
Post a Comment
Note: Only a member of this blog may post a comment.