Monday, October 1, 2018

Oracle - ORACLE error 959 encountered when importing with imp


If the dump is exported with imp, you cannot use impdp and you may face issue such as


IMP-00003: ORACLE error 959 encountered
ORA-00959: tablespace 'XXXXXX' does not exist


Below are the steps to work around it

1) Create the user (Note: for simplicity, I had grant TESTER all privileges)

$ sqlplus sys as sysdba
SQL> drop user TESTER cascade;
User dropped.
SQL> create user TESTER identified by TESTER default tablespace USERS;
User created.
SQL> grant all privileges to TESTER;
Grant succeeded.
SQL> exit



2) Generate the index SQL file

$ imp tester/tester file=somedata.dmp log=somedata.log indexfile=somedata.sql full=y


3) Open the indexfile (somedata.sql) in a text editor that can do find and replace over an entire file, and issue the following find and replace statements IN ORDER (ignore the single quotes.. '):

* Find: 'REM'  Replace:
* Find: '"source_tablespace"' Replace: '"USERS"'
* Find: '...' Replace: 'REM ...'
* Find: 'CONNECT' Replace: 'REM CONNECT'

Save the indexfile

4) Perform the dump. But since TESTER is an admin, we need another admin account (some_admin_user) to import the dump

$ imp some_admin_user/some_admin_user_password file=somedata.dmp log=somedata.log grants=n fromuser=SOME_OTHER_USER touser=TESTER
. . importing table                "ABC"          8 rows imported
.....snip.....
IMP-00017: following statement failed with ORACLE error 959:
...... snip.............
CREATE TABLE "SOMETABLE"
...... snip.....
IMP-00003: ORACLE error 959 encountered
ORA-00959: tablespace 'XYZ' does not exist
....snip.........
About to enable constraints...
Import terminated successfully with warnings.


You can see that one of the table (e.g SOMETABLE) has an ORA-00959: tablespace 'XYZ' does not exist

4) Locate the problematic table in the indexfile and load it into the database. This involve manual work such as looking for the create table and alter table SQL statement in the indexFile. After locating all these SQL statement, put them into an sql file (e.g some_table.sql)

Then, run the SQL against the database

$ sqlplus some_admin_user/some_admin_user_password @some_table.sql


5) Import data to specific table


$ imp  some_admin_user/some_admin_user_password file=somedata.dmp log=somedata.log grants=n fromuser=SOME_OTHER_USER touser=TESTER tables=SOMETABLE DATA_ONLY=Y
....snip.......
import done in US7ASCII character set and AL16UTF16 NCHAR character set
import server uses AL32UTF8 character set (possible charset conversion)
. . importing table            "SOMETABLE "          10 rows imported
Import terminated successfully without warnings.


No comments:

Post a Comment

Oracle - ORACLE error 959 encountered when importing with imp

If the dump is exported with imp, you cannot use impdp and you may face issue such as IMP-00003: ORACLE error 959 encountered ORA-009...