Tuesday, May 1, 2012

Rename tablespace

I was migrating an Oracle database 10gr2 to 11gr2
In the 10gr2 database the tablespaces had still our
Old standard name’s(SVM_D_001)

In our new database 11gr2 the tablespace name used
Our new standards(SVM4_D)

If you our trying to import you’ll get an error because
The SVM_D_001 doesn’t exisist in the oracle 11gr2

A simple solution is to rename the tablespace name’s
In the 10gr2 database

Login as sys user

SQL> alter tablespace SMV4_D_001 rename to SMV4_D;

Tablespace altered.

SQL> alter tablespace SMV4_I_001 rename to SMV4_I;

Thats it.
Of course there are also other solutions as for example
Use the remap_tablespace option in datapump

$ impdp  gda001@testdb remap_tablespace= REMAP_TABLESPACE='SVM_D_001':'SVM4_D'
   Remap_tablespace= REMAP_TABLESPACE='SVM_l_001':'SVM4_I'
   Directory=data_pump dumpfile=expdat.dmp logfile=import.log

Thats it

No comments:

Post a Comment