2

On Oracle 12c, fairly new database with APEX installed. I do a

 expdp \"/ as sysdba\"  directory=dp_dir full=y dumpfile=full.dmp logfile=full.log 

And I am getting just a few tables from SYS and SYSTEM exported:

. . exported "SYS"."KU$_USER_MAPPING_VIEW"               5.882 KB      24 rows
. . exported "SYS"."AUD$"                                    0 KB       0 rows
. . exported "SYS"."DAM_CLEANUP_EVENTS$"                     0 KB       0 rows
. . exported "SYS"."DAM_CLEANUP_JOBS$"                       0 KB       0 rows
. . exported "SYS"."DAM_CONFIG_PARAM$"                   6.523 KB      14 rows
. . exported "SYS"."TSDP_ASSOCIATION$"                       0 KB       0 rows
. . exported "SYS"."TSDP_CONDITION$"                         0 KB       0 rows
. . exported "SYS"."TSDP_FEATURE_POLICY$"                    0 KB       0 rows
. . exported "SYS"."TSDP_PARAMETER$"                     5.945 KB       1 rows
. . exported "SYS"."TSDP_POLICY$"                        5.914 KB       1 rows
. . exported "SYS"."TSDP_PROTECTION$"                        0 KB       0 rows
. . exported "SYS"."TSDP_SENSITIVE_DATA$"                    0 KB       0 rows
. . exported "SYS"."TSDP_SENSITIVE_TYPE$"                    0 KB       0 rows
. . exported "SYS"."TSDP_SOURCE$"                            0 KB       0 rows
. . exported "SYS"."TSDP_SUBPOL$"                        6.320 KB       1 rows
. . exported "SYSTEM"."REDO_DB"                          25.58 KB       1 rows
. . exported "SYSTEM"."REDO_LOG"                             0 KB       0 rows
. . exported "SYS"."FGA_LOG$FOR_EXPORT"                      0 KB       0 rows
. . exported "SYSTEM"."SCHEDULER_JOB_ARGS"               8.671 KB       4 rows
. . exported "SYSTEM"."SCHEDULER_PROGRAM_ARGS"           10.21 KB      22 rows
. . exported "SYS"."AUDTAB$TBS$FOR_EXPORT"               5.953 KB       2 rows
. . exported "SYS"."DBA_SENSITIVE_DATA"                      0 KB       0 rows
. . exported "SYS"."DBA_TSDP_POLICY_PROTECTION"              0 KB       0 rows
. . exported "SYS"."NACL$_ACE_EXP"                       10.14 KB       3 rows
. . exported "SYS"."NACL$_HOST_EXP"                      6.976 KB       2 rows
. . exported "SYS"."NACL$_WALLET_EXP"                        0 KB       0 rows
Master table "SYS"."SYS_EXPORT_FULL_01" successfully loaded/unloaded

Yet I know I have tables and other objects in other schemas:

AtlasProd:SYS@extern01> select owner, 
  2  decode(segment_type, 'TABLE', 'TABLE', '-other-') as segment_type,
  3  count(*)
  4  from dba_segments
  5  group by owner, decode(segment_type, 'TABLE', 'TABLE', '-other-')
  6  order by 1, 2;

OWNER                          SEGMENT_TYPE         COUNT(*)
------------------------------ ------------------ ----------
APEX_050000                    -other-                   646
APEX_050000                    TABLE                     155
AUDSYS                         -other-                     6
DBSNMP                         -other-                     4
DBSNMP                         TABLE                       2
GSMADMIN_INTERNAL              -other-                    15
GSMADMIN_INTERNAL              TABLE                       6
OUTLN                          -other-                     6
OUTLN                          TABLE                       3
SYS                            -other-                  1987
SYS                            TABLE                    1059
SYSTEM                         -other-                   321
SYSTEM                         TABLE                     124
XDB                            -other-                   655
XDB                            TABLE                      49

15 rows selected.

I see a workaround at Export pump all schemas except Sytem related schemas but I know I've used full=y before successfully.

3

This is expected behaviour.

FULL

A full export does not, by default, export system schemas that contain Oracle-managed data and metadata. Examples of system schemas that are not exported by default include SYS, ORDSYS, and MDSYS.

12.1.0.1 EXPDP Does Not Export APEX Schema (Doc ID 1912162.1)

This is the expected behavior in 12.1.0.X version,

This was added, in part, to avoid problem with common objects in a multi-tenant DB, but certainly has been very helpful in eliminating errors report - particularly in full import. The bit is called 'ORACLE_MAINTAINED' in [user,all,dba]_objects dictionary views. Seems pretty reasonable that Oracle maintained objects are not be moved around by datapump.

2

You should be using the system user for full datapump export/import operations. Using "/ as sysdba" or "sys as sysdba" may give you unpredictable results.

Do not start Export as SYSDBA, except at the request of Oracle technical support. SYSDBA is used internally and has specialized functions; its behavior is not the same as for general users.

Source: Oracle 12c Datapump Documentation

  • 2
    This is true, but does not answer the question. And I doubt that by using system, the built-in schemas such as XDB would be exported, as my own tests proved otherwise. – Balazs Papp May 27 '16 at 19:24
  • Correct, I thought SYSTEM would get the APEX_05000 schemas but it didn't; as you noted in your answer. I marked this answer as correct a little too soon :) – Mark Stewart May 27 '16 at 19:54

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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