Issue while importing tables when source tablespaces are unknown [message #631842] |
Tue, 20 January 2015 11:47 |
|
orapratap
Messages: 134 Registered: November 2011 Location: Canada
|
Senior Member |
|
|
Hello
Data is exported from schema1 on database db01 on server1
I want to import the tables into schema2 on database db02 on server2
Now I don't know what tablespaces were existed on db01
After first failure Quote:"ORA-00959: tablespace 'TBS_LARGE' does not exist , I remapped the tablespace as following
impdp schema2/schema2 directory=impdir dumpfile=exp_schema1_db01.dmp logfile=imp_schema2_db02.log tables=schema1.table01 remap_schema=schema1:schema2 remap_tablespace=TBS_LARGE:MG_STR_02
However now the import fails as various indexes on the tables on source system were created on various tablespaces for which I don't know the tablenames until the errors is produced. Thus I can't use remap_tablespace in advance at once
Now there are 435 tables with various tablespaces for various objects for which I want to handle this issue
I granted 'resource' role to schema2 user, also made MG_STR_02 tablespace as it's default still the issue persists
Could anybody please suggest how can I import tables in another schema when source tablespaces are unknown?
Kind Regards
Oraratap
|
|
|
|
Re: Issue while importing tables when source tablespaces are unknown [message #631844 is a reply to message #631843] |
Tue, 20 January 2015 12:00 |
|
orapratap
Messages: 134 Registered: November 2011 Location: Canada
|
Senior Member |
|
|
Hello BlackSwan
Tried exactly same things just after sending the previous message.. but the results don't give any clue
Am I missing anything?
ORA-31655: no data or metadata objects selected for job
ORA-39154: Objects from foreign schemas have been removed from import
Master table "SCHEMA2"."SYS_SQL_FILE_FULL_01" successfully loaded/unloaded
Starting "SCHEMA2"."SYS_SQL_FILE_FULL_01": dummy_cid/******** directory=impdir dumpfile=exp_schema1_db01.dmp logfile=imp_schema2_db02.log sqlfile=ddl_dump.txt
Job "SCHEMA2"."SYS_SQL_FILE_FULL_01" successfully completed at Tue Jan 20 17:51:22 2015 elapsed 0 00:00:02
[oracle@SERVER2 ~]$ vi /u04/Dexp/ddl_dump.txt
[oracle@SERVER2 ~]$ cat /u04/Dexp/ddl_dump.txt
-- CONNECT SCHEMA2
ALTER SESSION SET EVENTS '10150 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '10904 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '25475 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '10407 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '10851 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '22830 TRACE NAME CONTEXT FOREVER, LEVEL 192 ';
kind Regards
Oraratap
[Updated on: Tue, 20 January 2015 12:01] Report message to a moderator
|
|
|
|