Oracle 11g – Incomplete SYSAUX EXPDP Unload

oracleoracle-11g

my question is really simple, and does not need any particular documentation.
When I run expdp command like this using Oracle Database 11g Release 11.2.0.4.0 – 64bit:

expdp system/******* directory=DATA_PUMP_DIR dumpfile=SYSAUX.dmp logfile=sysaux.log TABLESPACES=SYSAUX

I see that no table about SYSTEM schema is present.
I know for sure that tables in SYSTEM schema belong to SYSAUX tablespace, like DBA_SEGMENTS and DBA_EXTENTS tell me, but I don't see any log row like this:

.. exported "SYSTEM"."TABLENAME"                       120.2 GB 8396874 rows

No error message with expdp, just missing tables…..
Could anyone please give me suggestions about this?

Best Answer

Depends on what tables you try to export. Built-in tables are not exported. User-created tables are exported.

SQL> create table system.t1 tablespace sysaux as select * from dba_objects;

Table created.

SQL> select count(*) from dba_segments where tablespace_name = 'SYSAUX';

  COUNT(*)
----------
      2457

SQL> Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
[oracle@o61 ~]$ expdp \'/ as sysdba\' tablespaces=sysaux

Export: Release 11.2.0.4.0 - Production on Thu Jun 15 20:55:08 2017

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
Starting "SYS"."SYS_EXPORT_TABLESPACE_01":  "/******** AS SYSDBA" tablespaces=sysaux
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 2 MB
Processing object type TABLE_EXPORT/TABLE/TABLE
. . exported "SYSTEM"."T1"                               1.420 MB   15715 rows
Master table "SYS"."SYS_EXPORT_TABLESPACE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.SYS_EXPORT_TABLESPACE_01 is:
  /opt/oracle/base/product/db11204ee/rdbms/log/expdat.dmp
Job "SYS"."SYS_EXPORT_TABLESPACE_01" successfully completed at Thu Jun 15 20:55:23 2017 elapsed 0 00:00:08
Related Question