This is probably not new to most of you who checked the definition of DBA_OBJECTS view, but it was new to me, and I wanted to share.
Normally in documentation DBA_OBJECTS is referenced to ALL_OBJECTS view as all other DBA_XXX views, and OBJECT_ID column in ALL_OBJECTS view is NOT_NULL like below, but DBA_OBJECTS does not have any information about nullability
What I was trying was creating a test table created with CTAS from dba_objects and tried to create a primary key on OBJECT_ID but I couldn’t because of having nulls. When I checked I found that DB_LINKS don’t have object_id.
SQL> desc all_objects; Name Null? Type ------------------------------- -------- ------------------ 1 OWNER NOT NULL VARCHAR2(30) 2 OBJECT_NAME NOT NULL VARCHAR2(30) 3 SUBOBJECT_NAME VARCHAR2(30) 4 OBJECT_ID NOT NULL NUMBER 5 DATA_OBJECT_ID NUMBER 6 OBJECT_TYPE VARCHAR2(19) 7 CREATED NOT NULL DATE 8 LAST_DDL_TIME NOT NULL DATE 9 TIMESTAMP VARCHAR2(19) 10 STATUS VARCHAR2(7) 11 TEMPORARY VARCHAR2(1) 12 GENERATED VARCHAR2(1) 13 SECONDARY VARCHAR2(1) 14 NAMESPACE NOT NULL NUMBER 15 EDITION_NAME VARCHAR2(30) SQL> desc dba_objects Name Null? Type ------------------------------- -------- ------------------ 1 OWNER VARCHAR2(30) 2 OBJECT_NAME VARCHAR2(128) 3 SUBOBJECT_NAME VARCHAR2(30) 4 OBJECT_ID NUMBER 5 DATA_OBJECT_ID NUMBER 6 OBJECT_TYPE VARCHAR2(19) 7 CREATED DATE 8 LAST_DDL_TIME DATE 9 TIMESTAMP VARCHAR2(19) 10 STATUS VARCHAR2(7) 11 TEMPORARY VARCHAR2(1) 12 GENERATED VARCHAR2(1) 13 SECONDARY VARCHAR2(1) 14 NAMESPACE NUMBER 15 EDITION_NAME VARCHAR2(30)
When I check the definition of DBA_OBJECTS, I saw that DB_LINKS are added with a union to the view without an object_id
..... ..... ..... union all select u.name, l.name, NULL, to_number(null), to_number(null), 'DATABASE LINK', l.ctime, to_date(null), NULL, 'VALID','N','N', 'N', NULL, NULL from sys.link$ l, sys.user$ u where l.owner# = u.user#;
I check ALL_OBJECTS again and could not find any DB_LINK listed in ALL_OBJECTS. In my understanding DB_LINKS are not even objects, at least they don’t deserve to have an OBJECT_ID from Oracle’s point.
Leave a Reply