Karl about the Oracle Database

Hi reader,

If you copy a schema from a database to another you could check the integrity of the operation via the DBA_OBJECTS View. A good idea but the data dictionary view is incomplete - not all object types are supported.

SELECT DISTINCT Do.Object_Type
  FROM Dba_Objects Do
 ORDER BY Do.Object_Type

;

SQL> SELECT DISTINCT Do.Object_Type
  2    FROM Dba_Objects Do
  3   ORDER BY Do.Object_Type

  4  ;

OBJECT_TYPE
——————-
CHAIN
CLUSTER
CONSUMER GROUP
CONTEXT
DATABASE LINK
DIRECTORY
EVALUATION CONTEXT
FUNCTION
INDEX
INDEX PARTITION
INDEXTYPE
JOB
JOB CLASS
LIBRARY
LOB
LOB PARTITION
MATERIALIZED VIEW
OPERATOR
PACKAGE
PACKAGE BODY

PROCEDURE
PROGRAM
QUEUE
RESOURCE PLAN
RULE
RULE SET
SCHEDULE
SEQUENCE
SYNONYM
TABLE
TABLE PARTITION
TRIGGER
TYPE
TYPE BODY
UNDEFINED
VIEW
WINDOW
WINDOW GROUP
XML SCHEMA

39 rows selected

SQL>

In a schema of the same database i queried all object types of the DBA_OBJECTS view i found object types not listed : 

SELECT Uml.Log_Table
  FROM User_Mview_Logs Uml
;

SELECT Urc.NAME,
              Urc.Refgroup
  FROM User_Refresh_Children Urc
;

SELECT Ur.Refgroup,
       Ur.Rname
  FROM User_Refresh Ur

;

 

SQL> SELECT Uml.Log_Table
  2    FROM User_Mview_Logs Uml

;

LOG_TABLE
——————————
MLOG$_COMPANY

SQL>

SQL> SELECT Ur.Refgroup,
  2         Ur.Rname
  3    FROM User_Refresh Ur

;

  REFGROUP RNAME
———- ——————————
       141 GMRV_FACTS_RGR_IFX
SQL>

SQL> SELECT Urc.NAME,
  2                 Urc.Refgroup
  3    FROM User_Refresh_Children Urc

;

NAME                             REFGROUP
—————————— ———-
GMRV_FACTS_MAX_PARENT_IFX             141
GMRV_FACTS_COUNTPART_IFX              141
GMRV_FACTS_MAX_VAL_IFX                141

SQL>  

As you can see clearly Refresh groups and Materialized View Logs are not supported and documented via the (DBA|ALL|USER)_OBJECTS Views.

HTH KARL