Coskan’s Approach to Oracle

January 8, 2010

Object without object_id

Filed under: Diary — coskan @ 4:44 pm

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.

About these ads

Leave a Comment »

No comments yet.

RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

The Silver is the New Black Theme. Create a free website or blog at WordPress.com.

Follow

Get every new post delivered to your Inbox.

Join 199 other followers

%d bloggers like this: