Skip to content
Home » Oracle Database » Administration » Initialization Parameter » TABLE_EXPORT_OBJECTS, How and Why

TABLE_EXPORT_OBJECTS, How and Why

TABLE_EXPORT_OBJECTS

To INCLUDE or EXCLUDE specific object type in exporting or importing tables, you should know what name of object type should be used.

To know what object type can be used to INCLUDE or EXCLUDE in table mode, you may query the data dictionary view TABLE_EXPORT_OBJECTS to make sure.

OBJECT_PATH

Here is the exhausted list of object path in TABLE_EXPORT_OBJECTS for Oracle 19c.

SQL> set pagesize 10000;
SQL> select object_path from table_export_objects order by 1;

OBJECT_PATH
--------------------------------------------------------------------------------
AUDIT_OBJ
CLUSTERING
COMMENT
CONSTRAINT
CONSTRAINT/REF_CONSTRAINT
END_PLUGTS_BLK
FGA_POLICY
GRANT
GRANT/OWNER_GRANT/OBJECT_GRANT
GRANT/PROCDEPOBJ_GRANT
INDEX
INDEX/STATISTICS
INSTANCE_CALLOUT
MATERIALIZED_ZONEMAP
OBJECT_GRANT
OWNER_GRANT/OBJECT_GRANT
PLUGTS_BEGIN
PLUGTS_BLK
PLUGTS_CHECKPL
PLUGTS_TSNAME_TABLE
POST_INSTANCE/GRANT/PROCDEPOBJ_GRANT
POST_INSTANCE/PROCDEPOBJ
POST_INSTANCE/PROCDEPOBJ_AUDIT
POST_TABLE_ACTION
PRE_TABLE_ACTION
PROCACT_INSTANCE
PROCDEPOBJ
PROCDEPOBJ_AUDIT
PROCDEPOBJ_GRANT
RADM_POLICY
REF_CONSTRAINT
RLS_CONTEXT
RLS_GROUP
RLS_POLICY
RLS_POLICY/RLS_POLICY
STATISTICS
TABLE/AUDIT_OBJ
TABLE/CLUSTERING
TABLE/COMMENT
TABLE/CONSTRAINT
TABLE/CONSTRAINT/REF_CONSTRAINT
TABLE/END_PLUGTS_BLK
TABLE/FGA_POLICY
TABLE/GRANT
TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
TABLE/INDEX
TABLE/INDEX/STATISTICS
TABLE/INSTANCE_CALLOUT
TABLE/MATERIALIZED_ZONEMAP
TABLE/PLUGTS_BEGIN
TABLE/PLUGTS_BLK
TABLE/PLUGTS_CHECKPL
TABLE/PLUGTS_TSNAME_TABLE
TABLE/POST_INSTANCE/GRANT/PROCDEPOBJ_GRANT
TABLE/POST_INSTANCE/PROCDEPOBJ
TABLE/POST_INSTANCE/PROCDEPOBJ_AUDIT
TABLE/POST_TABLE_ACTION
TABLE/PRE_TABLE_ACTION
TABLE/PROCACT_INSTANCE
TABLE/RADM_POLICY
TABLE/RLS_CONTEXT
TABLE/RLS_GROUP
TABLE/RLS_POLICY/RLS_POLICY
TABLE/TRIGGER
TABLE_EXPORT/TABLE/AUDIT_OBJ
TABLE_EXPORT/TABLE/CLUSTERING
TABLE_EXPORT/TABLE/COMMENT
TABLE_EXPORT/TABLE/CONSTRAINT
TABLE_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
TABLE_EXPORT/TABLE/END_PLUGTS_BLK
TABLE_EXPORT/TABLE/FGA_POLICY
TABLE_EXPORT/TABLE/GRANT
TABLE_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
TABLE_EXPORT/TABLE/INDEX
TABLE_EXPORT/TABLE/INDEX/STATISTICS
TABLE_EXPORT/TABLE/INSTANCE_CALLOUT
TABLE_EXPORT/TABLE/MATERIALIZED_ZONEMAP
TABLE_EXPORT/TABLE/PLUGTS_BEGIN
TABLE_EXPORT/TABLE/PLUGTS_BLK
TABLE_EXPORT/TABLE/PLUGTS_CHECKPL
TABLE_EXPORT/TABLE/PLUGTS_TSNAME_TABLE
TABLE_EXPORT/TABLE/POST_INSTANCE/GRANT/PROCDEPOBJ_GRANT
TABLE_EXPORT/TABLE/POST_INSTANCE/PROCDEPOBJ
TABLE_EXPORT/TABLE/POST_INSTANCE/PROCDEPOBJ_AUDIT
TABLE_EXPORT/TABLE/POST_TABLE_ACTION
TABLE_EXPORT/TABLE/PRE_TABLE_ACTION
TABLE_EXPORT/TABLE/PROCACT_INSTANCE
TABLE_EXPORT/TABLE/RADM_POLICY
TABLE_EXPORT/TABLE/RLS_CONTEXT
TABLE_EXPORT/TABLE/RLS_GROUP
TABLE_EXPORT/TABLE/RLS_POLICY/RLS_POLICY
TABLE_EXPORT/TABLE/TRIGGER
TABLE_EXPORT/VIEWS_AS_TABLES
TRIGGER
VIEWS_AS_TABLES

95 rows selected.

There're 95 object paths in table mode of data pump.

In practice, we don't INCLUDE or EXCLUDE specific object types in exporting tables in order to have a complete dump, we usually use them to import desired data in a precise fashion.

The rule is that, once you explicitly include or exclude an object type, all dependent object types in the path will be also included or excluded.

COMMENTS

For example, we'd like to EXCLUDE=STATISTICS in table mode. We can see its meaning (comments) like this:

SQL> select comments from table_export_objects where object_path = 'STATISTICS';

COMMENTS
--------------------------------------------------------------------------------
Precomputed statistics

Which means, table and index statistics will be excluded in table mode.

Next, we perform the import.

[oracle@test ~]$ impdp system/password@orclpdb tables=SH.SALES exclude=STATISTICS dumpfile=tables.dmp logfile=tables.log

Which indicates that it will not import statistics of the table and its indexes, we'll gather database statistics by ourselves after data migration.

To have the ability to export / import the full database, you need more privileges:

Leave a Reply

Your email address will not be published. Required fields are marked *