Wednesday, January 24, 2024

ORA-31603: object "SYS_NT#***" of type TABLE not found in schema"

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.

Does DEALLOCATE UNUSED or SHRINK SPACE will free space occupied by LOB segment?

Lets check how it works. My env is DB 19.20@Linux-x64 1) I created the table with 4 LOB columns of 4 different LOB types: BASICFILE BLOB, BA...