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.
