Shulou(Shulou.com)06/01 Report--

Quickly solve the ORA-00959 in IMP

When importing DMP into the test library with IMP, we encountered the problem that the ORA-00959 tablespace does not exist.

General solution:

1. Create the table first, and add the parameters of ignore=y when importing dmp.

two。 Create a corresponding tablespace

3. Export from new using expdp, and use REMAP_TABLESPACE when impdp

No matter which method is used, it is troublesome and requires a certain amount of work. If you are in oracle10g

This problem can be solved quickly by renaming tablespaces.

SQL > alter tablespace & old_tbsname rename to & new_tbs_name

For example, the default table space of the current user is TEST, and now it is changed to the required table space YWDBS

SQL > alter tablespace TEST rename to YWDBS

/ oracle$imp TEST/TEST file=dev_bak_20110702.dmp tables=PRPDRISK statistics=none

Import: Release 10.2.0.4.0-Production on Wed Jul 6 09:39:25 2011

Copyright (c) 1982, 2007, Oracle. All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0-64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

Export file created by EXPORT:V10.02.01 via conventional path

Warning: the objects were exported by DEV, not by you

Import done in ZHS16GBK character set and AL16UTF16 NCHAR character set

. Importing DEV's objects into REPORTTEST

. Importing DEV's objects into REPORTTEST

IMP-00017: following statement failed with ORACLE error 959:

...

IMP-00003: ORACLE error 959 encountered

ORA-00959: tablespace 'YWDBS' does not exist

Import terminated successfully with warnings.

SQL > alter tablespace TEST rename to YWDBS

After renaming the tablespace, you will see the following information in alert.log:

Tablespace 'TEST' is renamed to' YWDBS'.

Completed: alter tablespace TEST rename to YWDBS

After renaming the tablespace, dmp can import smoothly.

/ oracle$imp TEST/TEST file=dev_bak_20110702.dmp tables=PRPDRISK statistics=none

Import: Release 10.2.0.4.0-Production on Wed Jul 6 10:05:08 2011

Copyright (c) 1982, 2007, Oracle. All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0-64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

Export file created by EXPORT:V10.02.01 via conventional path

Warning: the objects were exported by DEV, not by you

Import done in ZHS16GBK character set and AL16UTF16 NCHAR character set

. Importing DEV's objects into REPORTTEST

. Importing DEV's objects into REPORTTEST

. . Importing table "PRPDRISK" 68 rows imported

Import terminated successfully without warnings.

In order to manage the specification, you can return the tablespace name after the import is successful.

ORA-00959 in IMP typically occurs on a table with a CLOB field.

Original address

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.