Can't export table structure with expdp, parameter full=X conflicts with tablespaces=X

All we need is an easy explanation of the problem, so here it is.

I’m trying to export table schema with expdp,

expdp \'sys/XXXX as sysdba\' dumpfile=XXX.dmp logfile=XXXX.log content=METADATA_ONLY tablespaces=XXXX

And it fails with

ORA-31655: no data or metadata objects selected for job

Then I added full=Y and it conflicts with tablespaces parameter:

ORA-31655: no data or metadata objects selected for job

How can I export all schemas from specific tablespaces only?

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

What you posted works fine.

[[email protected] ~]$ expdp \'/ as sysdba\' tablespaces=users content=metadata_only

Export: Release 19.0.0.0.0 - Production on Sat Nov 14 12:48:17 2020
Version 19.9.0.0.0

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

Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Starting "SYS"."SYS_EXPORT_TABLESPACE_01":  "/******** AS SYSDBA" tablespaces=users content=metadata_only
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/TABLE
Master table "SYS"."SYS_EXPORT_TABLESPACE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.SYS_EXPORT_TABLESPACE_01 is:
  /u01/app/oracle/homes/OraDB19Home1/rdbms/log/expdat.dmp
Job "SYS"."SYS_EXPORT_TABLESPACE_01" successfully completed at Sat Nov 14 12:48:43 2020 elapsed 0 00:00:26

[[email protected] ~]$ 

You get this error when the tablespace specirfied does not exist:

[[email protected] ~]$ expdp \'/ as sysdba\' tablespaces=users123456 content=metadata_only

Export: Release 19.0.0.0.0 - Production on Sat Nov 14 12:50:16 2020
Version 19.9.0.0.0

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

Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Starting "SYS"."SYS_EXPORT_TABLESPACE_01":  "/******** AS SYSDBA" tablespaces=users123456 content=metadata_only
ORA-31655: no data or metadata objects selected for job
Job "SYS"."SYS_EXPORT_TABLESPACE_01" completed with 1 error(s) at Sat Nov 14 12:50:19 2020 elapsed 0 00:00:03

[[email protected] ~]$

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply