Home » RDBMS Server » Server Utilities » Exporting/Importing partitioned table (Oracle 11g)
Exporting/Importing partitioned table [message #594766] Mon, 02 September 2013 11:12 Go to next message
srivaths
Messages: 60
Registered: June 2005
Location: France
Member

Hi,

I am trying to export a partition of a table and import it to another database. I get the below error when I try to import.
ORA-14400: inserted partition key does not map to any partition


If I export the table(for that particular partition) and import the table(after dropping the table) in destination, the partitions and subpartitions are created without any problem.

The table is Range Partitioned and Subpartitioned in List.

So I had to perform the below operation if I want to retain other data in the Destination table.
1. Drop the existing partition
2. Create the partition and subpartition, same as source
3. Execute imp

Infact I had to perform step#2, as if I split the parition also, the subpartition gets replicated in the new partition, which again throws the same error.

Is there better way of managing the partitions and subpartition in destination with exp/imp utility, so that I need not perform step#1 and step#2 manually.

Regards
Srivaths
Re: Exporting/Importing partitioned table [message #594767 is a reply to message #594766] Mon, 02 September 2013 11:18 Go to previous messageGo to next message
BlackSwan
Messages: 22789
Registered: January 2009
Senior Member
14400, 00000, "inserted partition key does not map to any partition"
// *Cause:  An attempt was made to insert a record into, a Range or Composite
//          Range object, with a concatenated partition key that is beyond 
//          the concatenated partition bound list of the last partition -OR-
//          An attempt was made to insert a record into a List object with
//          a partition key that did not match the literal values specified
//          for any of the partitions. 
// *Action: Do not insert the key. Or, add a partition capable of accepting
//          the key, Or add values matching the key to a partition specification
Re: Exporting/Importing partitioned table [message #594768 is a reply to message #594767] Mon, 02 September 2013 11:26 Go to previous messageGo to next message
srivaths
Messages: 60
Registered: June 2005
Location: France
Member

Smile

Yes my point was

1. If we imp to a new table which is created by imp, it imports it with the partition and subparition information
2. But if we want to perform the same action in already existing table, it fails to import with this error.
I expected to atleast work, if we have created a partition(with a subpartition template) for the importing data. And import should have the imported the data with its subpartition info.
Re: Exporting/Importing partitioned table [message #594769 is a reply to message #594768] Mon, 02 September 2013 11:33 Go to previous messageGo to next message
BlackSwan
Messages: 22789
Registered: January 2009
Senior Member
>I expected to atleast work, if we have created a partition(with a subpartition template) for the importing data. And import should have the imported the data with its subpartition info.

We can't impact your expectations or how Oracle behaves.
You need to submit a Service Request if different results is occur in the future.
Re: Exporting/Importing partitioned table [message #594770 is a reply to message #594766] Mon, 02 September 2013 11:37 Go to previous messageGo to next message
Michel Cadot
Messages: 59118
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
with exp/imp utility,


Use Data Pump instead.

Regards
Michel
Re: Exporting/Importing partitioned table [message #594771 is a reply to message #594770] Mon, 02 September 2013 11:54 Go to previous messageGo to next message
srivaths
Messages: 60
Registered: June 2005
Location: France
Member

Yes Michel, but actually the data pump creates the dump file in the source database. So I have to again ftp the dump file to the destination and load it.

Actually this is a data refresh script what we have developed.
Re: Exporting/Importing partitioned table [message #594772 is a reply to message #594771] Mon, 02 September 2013 12:33 Go to previous messageGo to next message
Michel Cadot
Messages: 59118
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You can use Data Pump across a database link.

Regards
Michel
Re: Exporting/Importing partitioned table [message #594975 is a reply to message #594772] Wed, 04 September 2013 09:56 Go to previous messageGo to next message
srivaths
Messages: 60
Registered: June 2005
Location: France
Member

Michel,

But the problem seem to be that we cannot import(impdp) a partition of the table with NETWORK_LINK parameter set(only the full table).

Regards
Srivaths

edited : included impdp

[Updated on: Wed, 04 September 2013 09:59]

Report message to a moderator

Re: Exporting/Importing partitioned table [message #594976 is a reply to message #594975] Wed, 04 September 2013 09:59 Go to previous messageGo to next message
BlackSwan
Messages: 22789
Registered: January 2009
Senior Member
Why not just use plain SQL & DBLINK and avoid external utility completely?
Re: Exporting/Importing partitioned table [message #594977 is a reply to message #594976] Wed, 04 September 2013 10:05 Go to previous messageGo to next message
srivaths
Messages: 60
Registered: June 2005
Location: France
Member

Yes, but the problem is with DBLINK, partition based access/hints are not possible on the source tables. Sometimes the INSERTs are just hung for hours, which just creates contention. But if we export(exp) for the same table(partition), its very quick(atleast by 80%). Thats why we are probing on easier partition management for destination table.
Re: Exporting/Importing partitioned table [message #594979 is a reply to message #594977] Wed, 04 September 2013 10:15 Go to previous messageGo to next message
BlackSwan
Messages: 22789
Registered: January 2009
Senior Member
So rather than solve the problem with plain SQL, you are are producing Rube Goldberg "work around"; which now presents additional problem & another kludge fix

http://en.wikipedia.org/wiki/Rube_Goldberg

CLASSIC!
Re: Exporting/Importing partitioned table [message #594980 is a reply to message #594979] Wed, 04 September 2013 10:23 Go to previous message
srivaths
Messages: 60
Registered: June 2005
Location: France
Member

We have already achieved around 20% gain from exp/imp for the same set of data performed over DBLINK. But I am trying to find a better way of handling the partitions in the destination table.

As Oracle does manage the partitions/subpartitions when a full table is imported, but NOT when I am trying to import a particular partition.
Previous Topic: loading vertical data using sqlldr
Next Topic: getting count of objects after schema refresh
Goto Forum:
  


Current Time: Wed Sep 17 23:40:00 CDT 2014

Total time taken to generate the page: 0.09080 seconds