OraFAQ Forum: Server Utilities » is it possible to use impdp for a table without using REMAP

Home » RDBMS Server » Server Utilities » is it possible to use impdp for a table without using REMAP_TABLESPACE (Oracle-10.2 / Linux-AS)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

is it possible to use impdp for a table without using REMAP_TABLESPACE [message #432789]

Thu, 26 November 2009 05:17

sbmk_design
Messages: 88
Registered: April 2007
Location: CHENNAI

Member

I am having a export-unix script to export from SERVER-1/ SCOTT Schema/ EMP Table-with 40 million rows(Default Tablespace is USERS)
then FTP's to SERVER-2. then I will get a intimation mail,
Based on this mail, ANOTHER PERSON will run import-UNIX script @ SERVER-2 / HR Schema (Default Tablespace is EXAMPLE )
Now we are using exp / imp command.It imports into default tablespace without any problem.
Weekly once we run these scripts, to transfer data from SERVER-1 to SERVER-2
Now We are developing expdp /impdp scripts

My problem is :-while using impdp command it throws error
ORA-39083: Object type TABLE failed to create with error:
ORA-01950: no privileges on tablespace 'USERS'
Failing sql is:
CREATE TABLE "HR"."EMP"(...........

then I used REMAP_TABLESPACE=USERS:EXAMPLE option, now it works fine....

Assume
In SERVER-1 / EMP table is moved to some other tablespace for table re organization purpose. then my impdp script fails
That is why I am asking is it possible to use impdp for a table without using REMAP_TABLESPACE option? Like exp/imp command.

Thanks in advance
sbmk

Report message to a moderator

Re: is it possible to use impdp for a table without using REMAP_TABLESPACE [message #432803 is a reply to message #432789]

Thu, 26 November 2009 06:24

surachart
Messages: 19
Registered: December 2008

Junior Member

you have to use REMAP_TABLESPACE anyway...

if you have to import table table ...

you may truncate table before and impdp CONTENT=DATA_ONLY

or use "TABLE_EXISTS_ACTION" option:
TABLE_EXISTS_ACTION Action to take if imported object already exists. Valid keywords: (SKIP), APPEND, REPLACE and TRUNCATE.

Report message to a moderator

Re: is it possible to use impdp for a table without using REMAP_TABLESPACE [message #432821 is a reply to message #432803]

Thu, 26 November 2009 07:42

sbmk_design
Messages: 88
Registered: April 2007
Location: CHENNAI

Member

Problem is Source Database is @ one place, Target Database is @ another place, Target dba only knows Source_Schema name he may not know if any Source_Tablespace changes occurs.

Because the source_table is very big they may frequently move the table to some other tablespace for performance improvement.

Thanks in advance
sbmk

Report message to a moderator

Re: is it possible to use impdp for a table without using REMAP_TABLESPACE [message #432843 is a reply to message #432821]

Thu, 26 November 2009 10:38

surachart
Messages: 19
Registered: December 2008

Junior Member

truncate table (target) and impdp CONTENT=DATA_ONLY

that may help

Report message to a moderator

Previous Topic:	Will Import
Next Topic:	Oracle-10g /Linux-AS

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Apr 26 03:32:13 CDT 2024