partition table test

create table p
(
order#       number primary key,
order_date date,
data       varchar2(30)
)
enable row movement
partition by range(order_date)
(
partition part_2007 values less than (date '2008-1-1'),
partition part_2008 values less than (date '2009-1-1')
)
/

create table c1
( order#   number not null,   -- the NOT NULL is necessary, even though part of the    line#    number,            -- key
   line#   number,  
      data     varchar2(30),
      constraint c1_pk primary key(order#,line#),
      constraint c1_fk_p foreign key(order#) references p
    )
    enable row movement
    partition by reference(c1_fk_p)
 ;

insert into p (order#, order_date, data)
       values ( 1,date '2007-1-1', 'order data' );
insert into p (order#, order_date, data)
       values ( 2,date '2008-1-1', 'order data' );

SQL> insert into p (order#, order_date, data)
  2         values ( 3,date '2009-1-1', 'order data' );
 
insert into p (order#, order_date, data)
       values ( 3,date '2009-1-1', 'order data' )
 
ORA-14400: inserted partition key does not map to any partition

select * from p;

alter table p truncate partition part_2007;

SQL> select * from p;
 
    ORDER# ORDER_DATE  DATA
---------- ----------- ------------------------------
         2 2008-1-1    order data
 
SQL>

SQL> truncate table p;
 
Table truncated
 
SQL> select * from p;
 
    ORDER# ORDER_DATE  DATA
---------- ----------- ------------------------------
 
SQL>

 

 

 

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/16179598/viewspace-626491/，如需轉載，請註明出處，否則將追究法律責任。