0

Trying to export schema using the following command on several Oracle instances: two 19c and one 21c

$ expdp mySchema/myPwd@ORCL
  • Adding CONTENT=ALL or SCHEMA=mySchema or SCHEMA=MYSCHEMA or DUMPFILE=dumpfile.dmp to expdp command arguments does not help.
  • The user used in login is a DBA.
  • Tablespace is Users and temporary is Temp with Unlimited space and disk space has plenty of free space.
  • There is actual data in tables and there are hundreds of tables in the schema. All export jobs succeed without any errors, except that all dumps are 308kb but one 19c it actually dumps schema as dump file is ~100mb. Data Pump Wizard also creates dump 308 kb file. In case, DEFERRED_SEGMENT_CREATION parameter is True.

The export log is:

Export: Release 19.0.0.0.0 - Production on Thu Feb 22 11:22:09 2024
Version 19.3.0.0.0

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

Connected to: Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production

Warning: Oracle Data Pump operations are not typically needed when connected to the root or seed of a container database.

Starting "MYSCHEMA"."SYS_EXPORT_SCHEMA_01":  MYSCHEMA/********@oracle21c SCHEMAS=MYSCHEMA DUMPFILE=ded 
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/TABLESPACE_QUOTA
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Master table "MYSCHEMA"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for MYSCHEMA.SYS_EXPORT_SCHEMA_01 is:
  ...\DPDUMP\DED.DMP
Job "MYSCHEMA "."SYS_EXPORT_SCHEMA_01" successfully completed at Thu Feb 22 11:22:51 2024 elapsed 0 00:00:39

What may be wrong with the schema as other schema export as expected creating hundreds of MB dump file but in this case dump has no content?

4
  • what is in ..\DPDUMP\DED.DMP?
    – nbk
    Feb 22 at 17:29
  • @nbk, it's a path to the dump file, just replaced beginning C:\ora\data\etc with three dots.
    – Vy8
    Feb 22 at 21:23
  • i wanted to know what is in the file, not where it leads
    – nbk
    Feb 22 at 21:29
  • @nbk it is a 308kb dumpfile but no tables/data when importing
    – Vy8
    Feb 23 at 6:31

1 Answer 1

0

I have seen this countless times.

Whoever set up that schema did not know how to properly use a multitenant database.

Warning: Oracle Data Pump operations are not typically needed when connected to the root or seed of a container database. is a dead giveaway.

You DO NOT put user data in the root container. Still, most people just go ahead, search for ORA-65096 error after they tried creating the first user in the root container, click on one the first hits (which is an infamous site that I am not going to paste here because it should be avoided) and follow the incorrect advice given there related to the _ORACLE_SCRIPT parameter. Then they think of it as a silver bullet and use it for everything.

Following that advice, your schema gets created with the ORACLE_MAINTAINED=Y value which you can check in DBA_USERS. Your objects also get created with ORACLE_MAINTAINED=Y value which you can check in DBA_OBJECTS.

And such objects are not exported by Data Pump by design, because only internal objects should have the ORACLE_MAINTAINED=Y value.

I suggest that you reorganize your database ASAP and use the multitenant architecture properly with pluggable databases or use the traditional non-CDB architecture, because what you have now is an unsupported mess.

The traditional export utility (exp) can still export such objects.

5
  • That might explain lots of things, but why on same Oracle instance I was able to export another schema B without issues, where user B was created the same way, but difference is that tables into B were imported using impdp?
    – Vy8
    Feb 22 at 21:21
  • @WesCrawford Did you check ORACLE_MAINTAINED in DBA_OBJECTS? Feb 22 at 21:22
  • but in DBA_OBJECTS for schema ORACLE_MAINTAINED is N whereas for schema A all tables are Y
    – Vy8
    Feb 22 at 21:30
  • could you tell what's the way to know whether it is CDB or PDB architecture used and what would be the way to move from CDB to PDB?
    – Vy8
    Feb 23 at 6:40
  • and exp does not work due to "ORA-01455" 'exporting referential integrity constraints' issue, wasn't a case when using expdp when it was working
    – Vy8
    Feb 23 at 14:57

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.