Home » RDBMS Server » Server Utilities » Issue while importing tables when source tablespaces are unknown (Oracle 11.2.0.4 on Linux)
Issue while importing tables when source tablespaces are unknown [message #631842] Tue, 20 January 2015 11:47 Go to next message
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 #631843 is a reply to message #631842] Tue, 20 January 2015 11:53 Go to previous messageGo to next message
BlackSwan
Messages: 26594
Registered: January 2009
Location: SoCal
Senior Member
impdp help=yes

SQLFILE
Write all the SQL DDL to a specified file.

Re: Issue while importing tables when source tablespaces are unknown [message #631844 is a reply to message #631843] Tue, 20 January 2015 12:00 Go to previous messageGo to next message
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

Re: Issue while importing tables when source tablespaces are unknown [message #631845 is a reply to message #631844] Tue, 20 January 2015 12:04 Go to previous message
orapratap
Messages: 134
Registered: November 2011
Location: Canada
Senior Member
Hello BlackSwan

After granting DBA role to the user it worked

Thanks for your quick response

Kind Regards
Oraratap
Previous Topic: Export of data through Query in oracle standard edition
Next Topic: EXPDP/IMPDP
Goto Forum:
  


Current Time: Sun Sep 15 07:36:07 CDT 2019