1

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?

Improve this question
1
  • 1
    Note that you should not be using expdp or impdp 'as sysdba'. The documentation very specifically says " Note: 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. ". Reference docs.oracle.com/en/database/oracle/oracle-database/18/sutil/…
    – EdStevens
    Commented Nov 14, 2020 at 13:16

1 Answer 1

Reset to default
2

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 ~]$
Improve this answer
1
  • You're right, the tablespace does not exist. The error message itself is quite misleading.
    – daisy
    Commented Nov 14, 2020 at 12:12

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.