Tony’s Oracle Tips

July 23, 2008

Oracle Error Messages

Filed under: 11g Partitioning — tonyhasler @ 12:56 am

I thought I had written my first blog entry ever the other day but apparently I haven’t;  I simply wrote a page on my blog website.  OK, let’s try again.  I have been burning some midnight oil, along with some of the great and good of Oracle Scene, putting together a last minute article on Oracle 11g Partitioning.  Whilst trying to test my examples I came accross some highly misleading error messages.  Check this out:

CREATE TABLE orders

( id                 NUMBER PRIMARY KEY
, ORDER_date          DATE NOT NULL
) PARTITION BY RANGE (order_date)

(

 PARTITION PRE_2000 VALUES LESS THAN (TO_DATE(‘1-1-2000′,’DD-MM-YYYY’)) TABLESPACE TBS1
,PARTITION P2000_2007 VALUES LESS THAN (TO_DATE(‘1-1-2008′,’DD-MM-YYYY’)) TABLESPACE TBS2
,PARTITION P2008 VALUES LESS THAN (TO_DATE(‘1-2-2009′,’DD-MM-YYYY’)) TABLESPACE TBS3

);


— Now create the child table.  Three partitions will be created
— with the same names as above and placed in tablespaces tbs1,tbs2, and tbs3

CREATE TABLE orders
( id            NUMBER PRIMARY KEY
, order_id      NUMBER NOT NULL
, CONSTRAINT order_items_fk FOREIGN KEY (order_id)
  REFERENCES orders (id)
)

PARTITION BY REFERENCE (order_items_fk);

If you run this you get:

ORA-14651: reference partitioning constraint is not supported

There is a long listed of documented causes for this error message.  None of which include the fact that I have used the name of an existing object to name my table!

My second example, which I also came across in my reseearch, is given with my tongue firmly placed in cheek. 

CREATE TABLE flight_bookings
( id                  NUMBER PRIMARY KEY
, customer_id         NUMBER NOT NULL
, confirmation_number VARCHAR2(12) NOT NULL
, requested_seat_type  VARCHAR2(10) NOT NULL
, reservation_date    DATE NOT NULL
, start_date          DATE NOT NULL
, end_date            DATE
, country as (substr(confirmation_number,9,2))
) PARTITION BY LIST (country)

SUBPARTITION BY HASH (customer_id)

SUBPARTITIONS 16
( PARTITION VALUES (‘US’,’CA’,’MX’)
, PARTITION VALUES (‘BR’,’AR’,’PE’)
, PARTITION VALUES (‘GB’,’DE’,’NL’,’BE’,’FR’,’ES’,’IT’,’CH’)
, PARTITION VALUES (‘NZ’,’AU’,’IN’,’CN’)
) ENABLE ROW MOVEMENT;

CREATE TABLE flight_passengers
( id            NUMBER PRIMARY KEY
, booking_id    NUMBER NOT NULL
, SURNAME       VARCHAR2(100) NOT NULL
, FORENAME      VARCHAR2(100) NOT NULL

, CONSTRAINT flight_passengers_fk FOREIGN KEY (booking_id)
  REFERENCES flight_bookings (id)
)

PARTITION BY REFERENCE (flight_passengers_fk)

ENABLE ROW MOVEMENT
;

alter table flight_bookings merge partitions for(‘CA’),for(‘BR’)
— into partition merged_1
dependent tables (flight_passengers
(partition fp1 tablespace users,
partition fp2,partition fp3,partition fp4,partition fp5,
partition fp6,partition fp7,partition fp8,partition fp9,
partition fp10,partition fp11,partition fp12,partition fp13,
partition fp14,partition fp15,partition fp16
)) ;

It appears that the commented out “into partition merged_1” is mandatory, despite the documentation’s assertions to the contrary.  The ensuing error was

ORA-03113: end-of-file on communication channel

Which was not terribly helpful!

Advertisements

Leave a Comment »

No comments yet.

RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Blog at WordPress.com.

%d bloggers like this: