Oracle Export – Fixing Export Table Structure Conflict with expdp Parameters

oracle

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?

Best Answer

What you posted works fine.

[oracle@o81 ~]$ 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

[oracle@o81 ~]$

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

[oracle@o81 ~]$ 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

[oracle@o81 ~]$
Related Question