Alex Rivera | Logout

Howto import an oracle dump in an different tablespace

Asked 2008-09-15T07:15:53.803
38

I want to import an oracle dump into a different tablespace.

I have a tablespace A used by User A. I've revoked DBA on this user and given him the grants connect and resource. Then I've dumped everything with the command

exp a/*** owner=a file=oracledump.DMP log=log.log compress=y

Now I want to import the dump into the tablespace B used by User B. So I've given him the grants on connect and resource (no DBA). Then I've executed the following import:

imp b/*** file=oracledump.DMP log=import.log fromuser=a touser=b

The result is a log with lots of errors:

IMP-00017: following statement failed with ORACLE error 20001: "BEGIN DBMS_STATS.SET_TABLE_STATS IMP-00003: ORACLE error 20001 encountered ORA-20001: Invalid or inconsistent input values

After that, I've tried the same import command but with the option statistics=none. This resulted in the following errors:

ORA-00959: tablespace 'A_TBLSPACE' does not exist

How should this be done?

Note: a lot of columns are of type CLOB. It looks like the problems have something to do with that.

Note2: The oracle versions are a mixture of 9.2, 10.1, and 10.1 XE. But I don't think it has to do with versions.

oracle-database oracle-dump
Edit
Report

2 Answers

3

What version of Oracle are you using? If its 10g or greater, you should look at using Data Pump instead of import/export anyway. I'm not 100% sure if it can handle this scenario, but I would expect it could.

Data Pump is the replacement for exp/imp for 10g and above. It works very similar to exp/imp, except its (supposedly, I don't use it since I'm stuck in 9i land) better.

Here is the Data Pump docs

answered 2008-09-15T13:36:29.287
2

my solution is to use GSAR utility to replace tablespace name in the DUMP file. When you do replce, make sure that the size of the dump file unchanged by adding spaces. E.g.

gsar -f -s"TSDAT_OV101" -r"USERS      " rm_schema.dump rm_schema.n.dump
gsar -f -s"TABLESPACE """USERS      """ ENABLE STORAGE IN ROW CHUNK 8192 RETENTION" -r"                                                                   " rm_schema.n1.dump rm_schema.n.dump
gsar -f -s"TABLESPACE """USERS      """ LOGGING" -r"                                  " rm_schema.n1.dump rm_schema.n.dump
gsar -f -s"TABLESPACE """USERS      """ " -r"                             " rm_schema.n.dump rm_schema.n1.dump
answered 2012-03-08T14:53:35.277

Your Answer