OraFAQ Forum: SQL & PL/SQL » import with a default constraint on table

Home » SQL & PL/SQL » SQL & PL/SQL » import with a default constraint on table (oracle 10g)

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

import with a default constraint on table [message #555779]

Mon, 28 May 2012 15:28

bhagyaraj.p
Messages: 5
Registered: May 2012
Location: Bangalore

Junior Member

Hi,

Please consider the below case,

I have a table as below. This table is not partitioned.

create table t1
(
d1 date,
n1 number not null
);

insert into a1(n1) values(1);
insert into a1(n1) values(2);
insert into a1(d1, n1) values(sysdate, 3);

I took an export dump of the above table and after that I renamed the table t1 to t1_old.
Then I recreated the table as below with a default constraint on d1 field.

create table t1
(
d1 date default to_date('01/01/1100','DD/MM/YYYY','NLS_CALENDAR=GREGORIAN'),
n1 number not null
)
PARTITION BY RANGE (d1)
(
PARTITION NULL_DATE VALUES LESS THAN (TO_DATE('1101-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
PARTITION USED_BEFORE_200801 VALUES LESS THAN (TO_DATE('2015-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
)
ENABLE ROW MOVEMENT;

My intention was to seperate the records having the date field and not having the date field in two separate partitions.
When I try to import the dump that I took before I am getting below error,

"ORA-14400: inserted partition key does not map to any partition"

I have done a work arround by creating an additional trigger on the table on insert action.
as below

CREATE OR REPLACE TRIGGER ins_trg_t1
BEFORE INSERT
ON T1 FOR EACH ROW
BEGIN
:NEW.D1:= to_date('01/01/1100','DD/MM/YYYY','NLS_CALENDAR=GREGORIAN');
END ins_trg_t1;
/

But the problem here is the data import is taking too much time than what I expected.
I can't afford a maxvalue partition here as of my DBA team mentioned if you add maxvalue partition adding partition later in a stage is difficult on this table so please suggest me a solution other than this.

Could some one suggest me a solution that I can apply in this scenario and make the import faster.
I am using oracle 10.2.0.1.0 version.

Report message to a moderator

Re: import with a default constraint on table [message #555781 is a reply to message #555779]

Mon, 28 May 2012 15:42

BlackSwan
Messages: 20108
Registered: January 2009

Senior Member

Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/

bcm@bcm-laptop:~$ oerr ora 14400
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

Report message to a moderator

Re: import with a default constraint on table [message #555782 is a reply to message #555779]

Mon, 28 May 2012 16:21

John Watson
Messages: 3110
Registered: January 2010
Location: Global Village

Senior Member

Quote:

my DBA team mentioned if you add maxvalue partition adding partition later in a stage is difficult

You can't add a partition, instead you split the existing partition. No problem.

Report message to a moderator

Previous Topic:	MV refresh error
Next Topic:	Synonym & View

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri May 24 04:38:11 CDT 2013

Total time taken to generate the page: 0.23777 seconds