In the old days, it was common to store objects from assorted users in the USERS tablespace. In more modern days, it sometimes seems more appropriate to store user objects in a tablespace dedicated to that schema.
So, as I was transferring data to a new Oracle server, I found myself wishing to import data into a different tablespace than on the source database.
While the impdp tool includes a remap option to take care of such a scenario, the traditional imp tool does not. A workaround is to force all user objects into the default tablespace of that user, however this only works if the source tablespace does not exist.
As an example, I have a user blog_data on OLDDB with it’s objects (in this case, a single table with a single row, and the associated primary key index) stored in the users tablespace.
I’ll export the schema using something like:
exp blog_data/secret@OLDDB file=blog_data.dmp
Then on the destination database, I need to create a user with appropriate privileges, default tablespace and make sure the USERS tablespace does not exist. Assuming this is a new installation, I can simply set a new default permanent tablespace for the database and delete the empty “USERS” tablespace:
CREATE TABLESPACE users2 DATAFILE SIZE 100M;
ALTER DATABASE DEFAULT TABLESPACE users2;
DROP TABLESPACE USERS;
CREATE TABLESPACE blog_data DATAFILE SIZE 1M;
CREATE USER blog_data IDENTIFIED BY secretpassword DEFAULT TABLESPACE blog_data QUOTA UNLIMITED ON blog_data;
GRANT connect,resource TO blog_data;
Now I can simply import the schema into my new user, forcing imp to place all data in the default tablespace:
[oracle@oratest1 impexptest]$ imp blog_data/secretpassword@NEWDB file=blog_data.dmp
Import: Release 11.2.0.3.0 – Production on Fri Oct 12 23:15:44 2012
…
Export file created by EXPORT:V11.02.00 via conventional path
import done in AL32UTF8 character set and AL16UTF16 NCHAR character set
. importing BLOG_DATA’s objects into BLOG_DATA
. . importing table “PEOPLE” 1 rows imported
Import terminated successfully without warnings.
The import completes without warnings, and we can then see that the table is in the right place:
[oracle@oratest1 impexptest]$ sqlplus “blog_data/secretpassword”
…
SQL> select table_name,tablespace_name from user_tables;
TABLE_NAME TABLESPACE_NAME
—————————— ——————————
PEOPLE BLOG_DATA
Faced with a single instance’s failure of a legacy task that does “imp” into another tablesspace despite existence of the source tables space name in the destination, I found that according to “expert one” at https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:66890284723848 the “imp” tool has around 8 pages of code which can result in loading data taken from one tablespace name into another name.
In my case “permissions” were stopping “CREATE TABLE” in the same way “dropping” the tablespace did for Roy – resulting in the destination user’s tablespace being [desirably] used.
For those unable to drop the tablespace in the destination, and unable to switch to the “improved” tool “impdp” (which doesn’t support export to the client’s disk), you can instead prefix the “imp” with steps that require no knowledge of the schemas if you have access to “sed” and “sqlplus”. Frankly, with this complex undocumented, and thus unreliable, “feature” of “imp”, I recommend the following as “proper”. Reliance on missing or protected tablespace as a way to get imp to use a new tablespace just messes with your [my] future.
I used the following “imp” with “indexfile” to “pre-create” the tables in the right table space, and then added “ignore=y” to my normal “imp”. The steps extract the “CREATE” statements from the “exp” file. Next “sed” is used to remove CREATE INDEX lines, uncomment the CREATE TABLE lines, remove notes about row data ignored by this “imp” run, remove a superfluous connect line, and then change the tablespace name. After that the SQL is run to create the tables in the right table space. Finally the normal “imp” is run and told to “ignore” that the tables already exist.
Hope someone else presented with some unique case finds this useful, wasting less time than I did.
imp u/p@DB file=x.dmp fromUser=old toUser=u show=y rows=n indexfile=x.sql
sed –in-line -r ‘/CREATE (UNIQUE |)INDEX/,/;/d’ x.sql
sed –in-line -e ‘s/^REM //’ -e ‘s/^… [0-9]* rows//’ x.sql
sed –in-line -e ‘s/^… [0-9]* rows//’ x.sql
sed –in-line -e ‘s/^CONNECT [A-Z0-9]*;//’ x.sql
sed –in-line -e ‘s/TABLESPACE “OLD”/TABLESPACE “NEW”/’ x.sql
sqlplus u/p@DB @x.sql
imp u/p@DB file=x.dmp ignore=y fromUser=old toUser=u
Sorry, the above was “bash” script under Red Hat, and I added “exit” to the sqlplus using “Here Document” type “Here String” syntax (http://en.wikipedia.org/wiki/Here_document):
sqlplus u/p@DB @x.sql <<<exit
Excellent Answer i din’t get this type of answer from atleast one website.thank you help.