oracletablespaceexpdp

How to export (expdp) a tablespace Oracle express 21c?

I'm currently studying this topic in a databases class, and I was asked to export a tablespace using the expdp command.

This is happening only with tablespaces, I tried executing a data pump of a table, and it worked just fine.

I tried doing it like:

expdp system/password@xe dumpfile = exportfile.dmp tablespaces = biblioteca

But I'm receiving the error:

ORA-31655 No Data Or Metadata Objects Selected For Job

The tablespace does exist, when I execute the query

SELECT tablespace_name FROM dba_tablespaces;

it shows that the tablespace is there:

TABLESPACE_NAME
------------------------------
SYSTEM
SYSAUX
UNDOTBS1
TEMP
USERS
TSCARNET
tienda_tablespace
TBS_TIENDA
biblioteca

But I really don't understand what am I doing wrong here. I think it is also important to mention that the tablespace was not created in a pdb. We haven't actually touched that topic yet in my class, so we are just using the default parameters to establish a connection in Oracle SQL Developer.

Solution

  • According to your query output, your tablespace name is lowercase in the data dictionary, which means it was created with a quoted identifier. Which generally makes life more difficult.

    The documentation has a section about data pump export parameters which includes:

    Case Sensitivity When Specifying Parameter Values

    For tablespace names, schema names, table names, and so on, that you enter as parameter values, Oracle Data Pump by default changes values entered as lowercase or mixed-case into uppercase. For example, if you enter TABLE=hr.employees, then it is changed to TABLE=HR.EMPLOYEES. To maintain case, you must enclose the value within quotation marks. For example, TABLE="hr.employees" would preserve the table name in all lower case. The name you enter must exactly match the name stored in the database.

    and the follow section talks about issues with quotation marks on the command line.

    So depending on your operating system you might need to do:

    tablespaces=\"biblioteca\"

    or

    tablespaces='"biblioteca"'

    or

    tablespaces='\"biblioteca\"'

    or some other variant.

    You would have the same issue exporting the tienda_tablespace as that is also lowercase, but wouldn't need to with the others. (If you are creating your own tablespaces and other objects I'd recommend avoiding using quoted identifiers completely.)

    As also mentioned in the documentation, it's a bit easier to use a parameter file, particularly when having to handle quotes.