Question: I'm testing an import for one
customer and while importing the database dump file with Data Pump I'm
getting the following error:
statement failed with ORACLE error 959:
IMP-00003: ORACLE error 959 encountered
ORA-00959: tablespace '_$deleted$24$0' does not
How do I remove the impdp message that the tablespace does not exist?
Answer: Data Pump impdp will return a
ORA-00959 when a table definition specifies multiple tablespaces (i.e. a
CLOB column stored in a separate tablespace. In these cases, the
solution is to pre-create the table (punching the DDL with
dbms_metadata) and use impdp with ignore=y.
By default, an export only exports the
objects in a tablespace, not the tablespace definition itself.
In this case, the import is complaining because a tablespace with the
same name as the export tablespace does not exist. You can fix the
ORA-00959 error in several ways:
- Create the tablespace and use IGNORE=Y in the impdp syntax
- Import into another user ID with a different default
tablespace name (fromuser touser)
- Use include=tablespace if you want to have the import pre-build
the same tablespaces
Generally, in an Oracle migration, the schema
and DDL are pre-created in the target database, and then the rows are
imported into the new database using the Oracle imp utility with the
IGNORE=Y parameter. The IGNORE=Y parameters tells Oracle to use the new
DDL definitions instead of the DDL inside the import data file.
But in Oracle9i and beyond, you can use the new utility package called
dbms_metadata that will easily display DDL and stored procedures
directly from the data dictionary.
Get the Complete
Oracle Utility Information
The landmark book
Utilities The Definitive Reference" contains over 600 pages of
filled with valuable information on Oracle's secret utilities.
This book includes scripts and tools to hypercharge Oracle 11g
performance and you can
for 30% off directly from the publisher.