<p>I want to import an oracle dump into a different tablespace.</p>
<p>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</p>
<blockquote>
<p>exp a/*** owner=a file=oracledump.DMP log=log.log compress=y</p>
</blockquote>
<p>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:</p>
<blockquote>
<p>imp b/*** file=oracledump.DMP log=import.log fromuser=a touser=b</p>
</blockquote>
<p>The result is a log with lots of errors:</p>
<blockquote>
<p>IMP-00017: following statement failed with ORACLE error 20001: &quot;BEGIN DBMS_STATS.SET_TABLE_STATS
IMP-00003: ORACLE error 20001 encountered
ORA-20001: Invalid or inconsistent input values</p>
</blockquote>
<p>After that, I've tried the same import command but with the option statistics=none. This resulted in the following errors:</p>
<blockquote>
<p>ORA-00959: tablespace 'A_TBLSPACE' does not exist</p>
</blockquote>
<p>How should this be done?</p>
<p>Note: a lot of columns are of type CLOB. It looks like the problems have something to do with that.</p>
<p>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.</p>

[{'answer_id': 62057, 'author': 'Michiel Overeem', 'author_id': 5043, 'author_profile': 'https://Stackoverflow.com/users/5043', 'pm_score': 2, 'selected': False, 'text': '<p>The problem has to do with the CLOB columns. It seems that the imp tool cannot rewrite the create statement to use another tablespace.</p>\n\n<p>Source: <a href="http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:66890284723848" rel="nofollow noreferrer">http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:66890284723848</a></p>\n\n<p>The solution is:\nCreate the schema by hand in the correct tablespace. If you do not have a script to create the schema, you can create it by using the indexfile= of the imp tool.</p>\n\n<p>You do have to disable all constraints your self, the oracle imp tool will not disable them. </p>\n\n<p>After that you can import the data with the following command:</p>\n\n<blockquote>\n  <p>imp b/*** file=oracledump.dmp log=import.log fromuser=a touser=b statistics=none ignore=y</p>\n</blockquote>\n\n<p>Note: I still needed the statistics=none due to other errors.</p>\n\n<p><strong><em>extra info about the data pump</em></strong></p>\n\n<p>As of Oracle 10 the import/export is improved: the data pump tool ([<a href="http://www.oracle-base.com/articles/10g/OracleDataPump10g.php][1]" rel="nofollow noreferrer">http://www.oracle-base.com/articles/10g/OracleDataPump10g.php][1]</a>)</p>\n\n<p>Using this to re-import the data into a new tablespace:</p>\n\n<ol>\n<li><p>First create a directory for the temporary dump:</p>\n\n<blockquote>\n  <p>CREATE OR REPLACE DIRECTORY tempdump AS \'/temp/tempdump/\';<br>\n  GRANT READ, WRITE ON DIRECTORY tempdump TO a;</p>\n</blockquote></li>\n<li><p>Export:</p>\n\n<blockquote>\n  <p>expdp a/* schemas=a directory=tempdump dumpfile=adump.dmp logfile=adump.log</p>\n</blockquote></li>\n<li><p>Import:</p>\n\n<blockquote>\n  <p>impdp b/* directory=tempdump dumpfile=adump.dmp logfile=bdump.log REMAP_SCHEMA=a:b</p>\n</blockquote></li>\n</ol>\n\n<p>Note: the dump files are stored and read from the server disk, not from the local (client) disk</p>\n'}, {'answer_id': 62927, 'author': 'Matthew Watson', 'author_id': 3839, 'author_profile': 'https://Stackoverflow.com/users/3839', 'pm_score': 2, 'selected': False, 'text': '<p>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.</p>\n\n<p><a href="http://www.oracle.com/technology/products/database/utilities/index.html" rel="nofollow noreferrer">Data Pump</a> 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.</p>\n\n<p><a href="http://download.oracle.com/docs/cd/B19306_01/server.102/b14215/part_dp.htm#CEGJCCHC" rel="nofollow noreferrer">Here is the Data Pump docs</a></p>\n'}, {'answer_id': 63823, 'author': 'Community', 'author_id': -1, 'author_profile': 'https://Stackoverflow.com/users/-1', 'pm_score': 4, 'selected': False, 'text': "<p>If you're using Oracle 10g and datapump, you can use the REMAP_TABLESPACE clause.  example:</p>\n\n<pre><code>REMAP_TABLESPACE=A_TBLSPACE:NEW_TABLESPACE_GOES_HERE\n</code></pre>\n"}, {'answer_id': 71826, 'author': 'Andrew', 'author_id': 5662, 'author_profile': 'https://Stackoverflow.com/users/5662', 'pm_score': 6, 'selected': True, 'text': '<p>You\'ve got a couple of issues here.</p>\n\n<p><strong>Firstly</strong>, the different versions of Oracle you\'re using is the reason for the table statistics error - I had the same issue when some of our Oracle 10g Databases got upgraded to Release 2, and some were still on Release 1 and I was swapping .DMP files between them.</p>\n\n<p>The solution that worked for me was to use the same version of <code>exp</code> and <code>imp</code> tools to do the exporting and importing on the different Database instances. This was easiest to do by using the same PC (or Oracle Server) to issue all of the exporting and importing commands.</p>\n\n<p><strong>Secondly</strong>, I suspect you\'re getting the <code>ORA-00959: tablespace \'A_TBLSPACE\' does not exist</code> because you\'re trying to import a .DMP file from a full-blown Oracle Database into the 10g Express Edition (XE) Database, which, by default, creates a single, predefined tablespace called <code>USERS</code> for you.</p>\n\n<p>If that\'s the case, then you\'ll need to do the following..</p>\n\n<ol>\n<li><p>With your .DMP file, create a SQL file containing the structure (Tables):</p>\n\n<p><code>imp &lt;xe_username&gt;/&lt;password&gt;@XE file=&lt;filename.dmp&gt; indexfile=index.sql full=y</code></p></li>\n<li><p>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.. \'):</p>\n\n<p><code>Find: \'REM&lt;space&gt;\'  Replace: &lt;nothing&gt;</code></p>\n\n<p><code>Find: \'"&lt;source_tablespace&gt;"\' Replace: \'"USERS"\'</code></p>\n\n<p><code>Find: \'...\' Replace: \'REM ...\'</code></p>\n\n<p><code>Find: \'CONNECT\' Replace: \'REM CONNECT\'</code></p></li>\n<li><p>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):</p>\n\n<p><code>sqlplus &lt;xe_username&gt;/&lt;password&gt;@XE @index.sql</code></p></li>\n<li><p>Finally run the same .DMP file you created the indexfile with against the same account to import the data, stored procedures, views etc:</p>\n\n<p><code>imp &lt;xe_username&gt;/&lt;password&gt;@XE file=&lt;filename.dmp&gt; fromuser=&lt;original_username&gt; touser=&lt;xe_username&gt; ignore=y</code></p></li>\n</ol>\n\n<p>You may get pages of Oracle errors when trying to create certain objects such as Database Jobs as Oracle will try to use the same Database Identifier, which will most likely fail as you\'re on a different Database.</p>\n'}, {'answer_id': 944637, 'author': 'Michiel Overeem', 'author_id': 5043, 'author_profile': 'https://Stackoverflow.com/users/5043', 'pm_score': 1, 'selected': False, 'text': "<p>The answer is difficult, but doable:</p>\n\n<p>Situation is: user <strong>A</strong> and tablespace <strong>X</strong></p>\n\n<ol>\n<li>import your dump file into a different database (this is only necessary if you need to keep a copy of the original one)</li>\n<li><p>rename tablespace</p>\n\n<p>alter tablespace <strong>X</strong> rename to <strong>Y</strong></p></li>\n<li><p>create a directory for the expdp command en grant rights</p></li>\n<li>create a dump with expdp</li>\n<li>remove the old user and old tablespace (<strong>Y</strong>)</li>\n<li>create the new tablespace (<strong>Y</strong>)</li>\n<li>create the new user (with a new name) - in this case <strong>B</strong> - and grant rights (also to the directory created with step 3)</li>\n<li><p>import the dump with impdp</p>\n\n<p>impdp B/B directory=DIR dumpfile=DUMPFILE.dmp logfile=LOGFILE.log REMAP_SCHEMA=<strong>A</strong>:<strong>B</strong></p></li>\n</ol>\n\n<p>and that's it...</p>\n"}, {'answer_id': 3289277, 'author': 'Grebets Kostyantyn', 'author_id': 396705, 'author_profile': 'https://Stackoverflow.com/users/396705', 'pm_score': 3, 'selected': False, 'text': '<p>For me this work ok (Oracle Database 10g Express Edition Release 10.2.0.1.0):</p>\n\n<pre><code>impdp B/B full=Y dumpfile=DUMP.dmp REMAP_TABLESPACE=OLD_TABLESPACE:USERS\n</code></pre>\n\n<p>But for new restore you need new tablespace</p>\n\n<p>P.S. Maybe useful <a href="http://www.oracle-base.com/articles/10g/OracleDataPump10g.php" rel="noreferrer">http://www.oracle-base.com/articles/10g/OracleDataPump10g.php</a></p>\n'}, {'answer_id': 9619559, 'author': 'Dmitry', 'author_id': 1257270, 'author_profile': 'https://Stackoverflow.com/users/1257270', 'pm_score': 2, 'selected': False, 'text': '<p>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.\nE.g.</p>\n\n<pre><code>gsar -f -s"TSDAT_OV101" -r"USERS      " rm_schema.dump rm_schema.n.dump\ngsar -f -s"TABLESPACE """USERS      """ ENABLE STORAGE IN ROW CHUNK 8192 RETENTION" -r"                                                                   " rm_schema.n1.dump rm_schema.n.dump\ngsar -f -s"TABLESPACE """USERS      """ LOGGING" -r"                                  " rm_schema.n1.dump rm_schema.n.dump\ngsar -f -s"TABLESPACE """USERS      """ " -r"                             " rm_schema.n.dump rm_schema.n1.dump\n</code></pre>\n'}, {'answer_id': 13514792, 'author': 'peter', 'author_id': 1845340, 'author_profile': 'https://Stackoverflow.com/users/1845340', 'pm_score': 2, 'selected': False, 'text': "<p>I wanna improve for two users both in different tablespaces on different servers (databases)</p>\n\n<p><strong>1.\nFirst create a directories for the temporary dump for both servers (databases):</strong></p>\n\n<p><em>server #1:</em></p>\n\n<pre><code>CREATE OR REPLACE DIRECTORY tempdump AS '/temp/old_datapump/';\nGRANT READ, WRITE ON DIRECTORY tempdump TO old_user;\n</code></pre>\n\n<p><em>server #2:</em></p>\n\n<pre><code>CREATE OR REPLACE DIRECTORY tempdump AS '/temp/new_datapump/';\nGRANT READ, WRITE ON DIRECTORY tempdump TO new_user;\n</code></pre>\n\n<p><strong>2.\nExport (server #1):</strong></p>\n\n<pre><code>expdp tables=old_user.table directory=tempdump dumpfile=adump.dmp logfile=adump.log\n</code></pre>\n\n<p><strong>3.\nImport (server #2):</strong></p>\n\n<pre><code>impdp directory=tempdump dumpfile=adump_table.dmp logfile=bdump_table.log\nREMAP_TABLESPACE=old_tablespace:new_tablespace REMAP_SCHEMA=old_user:new_user\n</code></pre>\n"}, {'answer_id': 20057358, 'author': 'Ahmad Shakil', 'author_id': 3006234, 'author_profile': 'https://Stackoverflow.com/users/3006234', 'pm_score': -1, 'selected': False, 'text': "<p>---Create new tablespace:</p>\n\n<p>CREATE TABLESPACE TABLESPACENAME DATAFILE \n  'D:\\ORACL\\ORADATA\\XE\\TABLESPACEFILENAME.DBF' SIZE 350M AUTOEXTEND ON NEXT 2500M MAXSIZE UNLIMITED\nLOGGING\nPERMANENT\nEXTENT MANAGEMENT LOCAL AUTOALLOCATE\nBLOCKSIZE 8K\nSEGMENT SPACE MANAGEMENT MANUAL\nFLASHBACK ON;</p>\n\n<p>---and then import with below command</p>\n\n<p>CREATE USER BVUSER IDENTIFIED BY VALUES 'bvuser' DEFAULT TABLESPACE TABLESPACENAME</p>\n\n<p>-- where D:\\ORACL is path of oracle installation</p>\n"}, {'answer_id': 54484926, 'author': 'masterxilo', 'author_id': 524504, 'author_profile': 'https://Stackoverflow.com/users/524504', 'pm_score': 0, 'selected': False, 'text': "<p>Because I wanted to <code>imp</code>ort (to Oracle 12.1|2) a dump that was <code>exp</code>orted from a local development database (18c xe), and I knew that all my target databases will have an accessible tablespace called <code>DATABASE_TABLESPACE</code>, I just created my schema/user to use a new tablespace of that name instead of the default <code>USERS</code> (to which I have no access on the target databases):</p>\n\n<pre><code>-- don't care about the details\nCREATE TABLESPACE DATABASE_TABLESPACE\n  DATAFILE 'DATABASE_TABLESPACE.dat' \n    SIZE 10M\n    REUSE\n    AUTOEXTEND ON NEXT 10M MAXSIZE 200M;\n\nALTER DATABASE DEFAULT TABLESPACE DATABASE_TABLESPACE;\n\nCREATE USER username\n  IDENTIFIED BY userpassword\n  CONTAINER=all;\n\nGRANT create session TO username;\nGRANT create table TO username;\nGRANT create view TO username;\nGRANT create any trigger TO username;\nGRANT create any procedure TO username;\nGRANT create sequence TO username;\nGRANT create synonym TO username;\nGRANT create synonym TO username;\nGRANT UNLIMITED TABLESPACE TO username;\n\n</code></pre>\n\n<p>An <code>exp</code> created from this makes <code>imp</code> happy on my target.</p>\n"}]
['https://Stackoverflow.com/questions/61963', 'https://Stackoverflow.com', 'https://Stackoverflow.com/users/5043/']