Home » RDBMS Server » Server Utilities » is it possible to use impdp for a table without using REMAP_TABLESPACE (Oracle-10.2 / Linux-AS)
is it possible to use impdp for a table without using REMAP_TABLESPACE [message #432789] Thu, 26 November 2009 05:17 Go to next message
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
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 Go to previous messageGo to next message
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.

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 Go to previous messageGo to next message
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
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 Go to previous message
surachart
Messages: 19
Registered: December 2008
Junior Member
truncate table (target) and impdp CONTENT=DATA_ONLY

that may help
Previous Topic: Will Import
Next Topic: Oracle-10g /Linux-AS
Goto Forum:
  


Current Time: Sat Sep 24 19:47:40 CDT 2016

Total time taken to generate the page: 0.07459 seconds