What is the new view ad_objects and how is it different from dba_objects?

Posted on by
  • Developers often use the user_objects or all_objects data dictionary view to confirm that there are no unexpected invalid objects.
  • Due to a database limitation these dictionary views only return correct object status after running a full compilation procedure (utl_recomp.recomp_parallel or ad_zd.compile).
  • As a workaround, you can check object status using the ad_objects view included with online patching.

select * from ad_objects where status=’INVALID’;

If we compare the total number of objects in ad_objects to dba_objects, we find that ad_objects has one more object than dba_objects:

SQL> select count(*) from ad_objects

COUNT(*)
———-
394774

SQL>  select count(*) from dba_objects

COUNT(*)
———-
394773

So, what’s the difference between ad_objects and dba_objects?
SQL> select object_name, object_type, owner from ad_objects
2  minus
3  select object_name, object_type, owner from dba_objects;
OBJECT_NAME
——————————————————————————–
OBJECT_TYPE        OWNER
——————- ——————————
_default_auditing_options_
TABLE            SYS
How are the columns different between ad_objects  and dba_objects? Compare the following table structures below:

SQL> desc ad_objects
Name                       Null?    Type
—————————————– ——– —————————-
OWNER                            VARCHAR2(30)
OBJECT_NAME                   NOT NULL VARCHAR2(30)
OBJECT_TYPE                        VARCHAR2(18)
ACTUAL                         VARCHAR2(1)
STATUS                         VARCHAR2(7)
CTIME                            DATE
MTIME                            DATE
STIME                            DATE

SQL> desc dba_objects
Name                       Null?    Type
—————————————– ——– —————————-
OWNER                            VARCHAR2(30)
OBJECT_NAME                        VARCHAR2(128)
SUBOBJECT_NAME                     VARCHAR2(30)
OBJECT_ID                        NUMBER
DATA_OBJECT_ID                     NUMBER
OBJECT_TYPE                        VARCHAR2(19)
CREATED                        DATE
LAST_DDL_TIME                        DATE
TIMESTAMP                        VARCHAR2(19)
STATUS                         VARCHAR2(7)
TEMPORARY                        VARCHAR2(1)
GENERATED                        VARCHAR2(1)
SECONDARY                        VARCHAR2(1)
NAMESPACE                        NUMBER
EDITION_NAME                        VARCHAR2(30)