Home » RDBMS Server » Server Utilities » Unable to use REMAP_TABLE (Windows 2007, Oracle 11.1.0.6.0 )
Unable to use REMAP_TABLE [message #548451] Thu, 22 March 2012 05:50 Go to next message
oraranjangmailcom
Messages: 67
Registered: April 2010
Member
Hi,

I have two tables of same structure i.e., DEV & HET.
I need to export from DEV and import into HET, I am using REMAP_TABLE while IMPDP, but data is getting inserted into DEV itself. Below are the syntax for expdp and impdp

Export from Dev:
expdp sunnyworkspace/sunnyworkspace@orcl directory=REMAP_DATAPUMP version = 11.1.0.6.0 dumpfile=dev.dmp tables=sunnyworkspace.dev reuse_dumpfiles=y CONTENT=DATA_ONLY


Import to Het:
impdp sunnyworkspace/sunny
workspace@orcl directory=REMAP_DATAPUMP version = 11.1.0.6.0 dumpfile=dev.dmp tables = sunnyworkspace.dev remap_table=sunnyworkspace.dev:het



Requesting for the help on this

Regards,
Ranjan
Re: Unable to use REMAP_TABLE [message #548452 is a reply to message #548451] Thu, 22 March 2012 05:57 Go to previous messageGo to next message
oraranjangmailcom
Messages: 67
Registered: April 2010
Member
Additional Info,

SQL> show user;
USER is "SUNNYWORKSPACE"
SQL> create table dev as select rownum num from dual connect by rownum <= 800;

Table created.

SQL> select count(*) from dev;

  COUNT(*)
----------
       800

SQL> create table het as select * from dev where 1=2;

Table created.

SQL> select count(*) from het;

  COUNT(*)
----------
         0


After Exp & Impdp

SQL> select count(*) from het;

  COUNT(*)
----------
         0

SQL> select count(*) from dev;

  COUNT(*)
----------
      1600

Re: Unable to use REMAP_TABLE [message #548453 is a reply to message #548452] Thu, 22 March 2012 06:07 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
The answer is in the documentation.
Read the 1st restriction for that parameter. It doesn't do what you think it does.

The tables are in the same schema. Why are you trying to use datapump at all?
A simple insert/select is the correct solution.
Re: Unable to use REMAP_TABLE [message #548457 is a reply to message #548453] Thu, 22 March 2012 06:23 Go to previous messageGo to next message
oraranjangmailcom
Messages: 67
Registered: April 2010
Member
Thia approach is the solution for one of my requirements.
I did drop of HET and reran the impdp

C:\LapTop_Users\SUNNY_PROJECT_WORK\DataBase\Dump_File>impdp sunnyworkspace/sunny
workspace@orcl directory=REMAP_DATAPUMP version = 11.1.0.6.0 dumpfile=dev.dmp re
map_table=sunnyworkspace.dev:het

Import: Release 11.1.0.6.0 - Production on Thursday, 22 March, 2012 16:50:18

Copyright (c) 2003, 2007, Oracle.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Produc
tion
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "SUNNYWORKSPACE"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "SUNNYWORKSPACE"."SYS_IMPORT_FULL_01":  sunnyworkspace/********@orcl di
rectory=REMAP_DATAPUMP version = 11.1.0.6.0 dumpfile=dev.dmp remap_table=sunnywo
rkspace.dev:het
Processing object type TABLE_EXPORT/TABLE/TABLE
ORA-39151: Table "SUNNYWORKSPACE"."DEV" exists. All dependent metadata and data
will be skipped due to table_exists_action of skip
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Job "SUNNYWORKSPACE"."SYS_IMPORT_FULL_01" completed with 1 error(s) at 16:50:20



Is there any probable solution for this ....
Re: Unable to use REMAP_TABLE [message #548459 is a reply to message #548457] Thu, 22 March 2012 06:25 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
Stop using datapump?
Seriously.
Datapump exists to move objects between schemas. Your using it in the same schema. Just stop.
If you want to copy the table use create table as select.
If you want to rename table use alter table.
Re: Unable to use REMAP_TABLE [message #548467 is a reply to message #548459] Thu, 22 March 2012 06:42 Go to previous messageGo to next message
oraranjangmailcom
Messages: 67
Registered: April 2010
Member
I got your approach and I appretiate your help on this....

My Requirement:-
I will be using remap_data while masking table DEV file AND load into table HET.
My analysis was based on below URL.
http://www.sysdbaonline.com/2009/09/remap_table-reuse_dumpfiles-feature-of-11g-datapump/
Re: Unable to use REMAP_TABLE [message #548471 is a reply to message #548467] Thu, 22 March 2012 06:48 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
But your version is 11.1 not 11.2 as in the article, isn't it?

Regards
Michel
Re: Unable to use REMAP_TABLE [message #548475 is a reply to message #548467] Thu, 22 March 2012 06:58 Go to previous messageGo to next message
Littlefoot
Messages: 21806
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
oraranjangmailcom wrote on Thu, 22 March 2012 12:42

My Requirement:-
I will be using remap_data while masking table DEV file AND load into table HET.


What would that be? Why "masking"? Wouldn't it be simpler to
create view het as select * from dev;
Re: Unable to use REMAP_TABLE [message #548476 is a reply to message #548471] Thu, 22 March 2012 07:00 Go to previous messageGo to next message
oraranjangmailcom
Messages: 67
Registered: April 2010
Member
Yes, Remat_table works in both 11.1.0.6.0 & 11.2
Re: Unable to use REMAP_TABLE [message #548477 is a reply to message #548475] Thu, 22 March 2012 07:04 Go to previous messageGo to next message
oraranjangmailcom
Messages: 67
Registered: April 2010
Member
while exporting table DEV, I will mask the data by using remap_data. One more thing, what was wrong with the above syntax i.e., why DEV table is getting imported instead of HET.
Re: Unable to use REMAP_TABLE [message #548489 is a reply to message #548477] Thu, 22 March 2012 07:50 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
The example in the link uses two DBs. I assume the target DB didn't have a table called HET or DEV.
Your test failed because DEV existed in the target schema.
As I said, datapump exists to copy objects between different schemas and/or DBs.
If you are working in the same schema you should not be using datapump.
Re: Unable to use REMAP_TABLE [message #548546 is a reply to message #548489] Thu, 22 March 2012 15:28 Go to previous message
oraranjangmailcom
Messages: 67
Registered: April 2010
Member
Thanks on this ...
Previous Topic: External Table cannot be viewed....
Next Topic: SQL loader
Goto Forum:
  


Current Time: Thu Mar 28 18:26:12 CDT 2024