0

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?

Improve this question
3
  • When you say "and does not need any particular documentation", I do not know how to interpret that. does it mean you don't want to refer to documentation? Because what you are trying to do and what you are doing are two different things. So, I'd recommend RTFM ..
    – Raj
    Commented Jun 15, 2017 at 12:01
  • Hi Raj, I meant "there is no need for me to produce any particular test case to let you better understand the problem", in other words, the problem can be explained in few words without nothing else
    – Nick
    Commented Jun 16, 2017 at 9:48
  • @Raj: by the way RTFM .... already done
    – Nick
    Commented Jun 16, 2017 at 10:07

2 Answers 2

Reset to default
0

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
Improve this answer
1
  • Thanks Balazs Papp ! I think that "built-in" tables are those called "System Tables" too. Right?
    – Nick
    Commented Jun 16, 2017 at 9:52
1

Actually SYS.AUD$ table CAN be exported but only using old export command exp instead of expdp.

exp \"/ as sysdba\" file=aud_table.dmp tables=sys.aud$;

Since 11g or 10 (not sure) AUD$ table is prohibited to be exported.

The most convenient way is what @Balazs Papp suggested, to create table AUD$_BACKUP as select * from SYS.AUD$

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.