import an oracle dump in another tablespace

Send Snippet To: Save this snippet to Code Collector Pro -- view all onlin85's snippets
language: Other
license: Other

Code for Snippet:

	1. Precreate tables
If that's the case, then you'll need to do the following..
With your .DMP file, create a SQL file containing the structure (Tables):
# imp <xe_username>/<password>@XE file=<filename.dmp> indexfile=index.sql full=y
Open the indexfile (index.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<space>' Replace: <nothing>
Find: '"<source_tablespace>"' Replace: '"USERS"'
Find: '...' Replace: 'REM ...'
Find: 'CONNECT' Replace: 'REM CONNECT'
Save the indexfile, then run it against your Oracle Express Edition account (I find it's best to create a new, blank XE user account - or drop and recreate if I'm refreshing):
# sqlplus <xe_username>/<password>@XE @index.sql
Finally run the same .DMP file you created the indexfile with against the same account to import the data, stored procedures, views etc:
# imp <xe_username>/<password>@XE file=<filename.dmp> fromuser=<original_username> touser=<xe_username> ignore=y
	2. Change default table space
Revoke the "UNLIMITED TABLESPACE" privilege from the user
#sqlplus> revoke unlimited tablespace from user
Revoke the user's quota from the tablespace from where the object was exported. This forces the import utility to create tables in the user's default tablespace.
#sqlplus> alter user test_two quota 0 on test_one quota unlimited on test_two;
Make the tablespace to which you want to import the default tablespace for the user
#sqlplus> create tablespace test_one datafile '/apps/oracle/oradata/acs816/test_one.dbf' size 4M;
#sqlplus> create tablespace test_two datafile '/apps/oracle/oradata/acs816/test_two.dbf' size 4M;
Import the table
imp test_two/test_two file=test_one.dmp fromuser=test_one touser=test_two
comments powered by Disqus


Tags: oracle import/export bash-shell

Link to this snippet:

Download to Code Collector

To use the direct link to your snippet on either copy the html from the above section or drag the Download to Code Collector to where you would like to use it.

More Info:

Times Viewed: 518
Date Added: 2013-01-26 05:00:02
Last Modified: 2014-05-05 20:39:30

Web Analytics