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\u00a0appropriate\u00a0to store user objects in a tablespace dedicated to that schema.<\/p>\n
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.<\/p>\n
While the\u00a0impdp\u00a0<\/em>tool includes a remap<\/em> option to take care of such a scenario, the traditional\u00a0imp<\/em> 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.<\/p>\n As an example, I have a user\u00a0blog_data<\/em> on\u00a0OLDDB\u00a0<\/em>with it’s objects (in this case, a single table with a single row, and the associated primary key index) stored in the users<\/em> tablespace.<\/p>\n I’ll export the schema using something like:<\/p>\n exp blog_data\/secret@OLDDB file=blog_data.dmp<\/p><\/blockquote>\n Then on the destination database, I need to create a user with\u00a0appropriate 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:<\/p>\n CREATE TABLESPACE users2 DATAFILE SIZE 100M;<\/p>\n ALTER DATABASE DEFAULT TABLESPACE users2;<\/p>\n DROP TABLESPACE USERS;<\/p>\n CREATE TABLESPACE blog_data DATAFILE SIZE 1M;<\/p>\n CREATE USER blog_data IDENTIFIED BY secretpassword DEFAULT TABLESPACE blog_data QUOTA UNLIMITED ON blog_data;<\/p>\n GRANT connect,resource TO blog_data;<\/p><\/blockquote>\n Now I can simply import the schema into my new user, forcing imp to place all data in the default tablespace:<\/p>\n [oracle@oratest1 impexptest]$ imp blog_data\/secretpassword@NEWDB file=blog_data.dmp<\/p>\n Import: Release 11.2.0.3.0 – Production on Fri Oct 12 23:15:44 2012<\/p>\n …<\/p>\n Export file created by EXPORT:V11.02.00 via conventional path The import completes without warnings, and we can then see that the table is in the right place:<\/p>\n [oracle@oratest1 impexptest]$ sqlplus “blog_data\/secretpassword”<\/p>\n …<\/p>\n SQL> select table_name,tablespace_name from user_tables;<\/p>\n TABLE_NAME TABLESPACE_NAME 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\u00a0appropriate\u00a0to store user objects in a tablespace dedicated to that schema. So, as I was transferring … Continue reading
\nimport done in AL32UTF8 character set and AL16UTF16 NCHAR character set
\n. importing BLOG_DATA’s objects into BLOG_DATA
\n. . importing table “PEOPLE” 1 rows imported
\nImport terminated successfully without warnings.<\/p><\/blockquote>\n
\n—————————— ——————————
\nPEOPLE BLOG_DATA<\/p><\/blockquote>\n","protected":false},"excerpt":{"rendered":"