My Blog List

Monday, March 4, 2013

Oracle Range Partition

// create partiton table

create table pt_test(
NO NUMBER NOT NULL,
NAME VARCHAR2(10) NULL
)
PARTITION BY RANGE(no)
(
PARTITION PT_DUMMY VALUES LESS THAN (-1)
);


//add partition
alter table PT_TEST ADD PARTITION PT_1 VALUES LESS THAN (5);

alter table PT_TEST ADD PARTITION PT_2 VALUES LESS THAN (11);

//TEST DATA INSERT
INSERT INTO PT_TEST VALUES (1 , 'A');
INSERT INTO PT_TEST VALUES (2 , 'B');
INSERT INTO PT_TEST VALUES (3 , 'C');
INSERT INTO PT_TEST VALUES (4 , 'D');
INSERT INTO PT_TEST VALUES (5 , 'E');
INSERT INTO PT_TEST VALUES (6 , 'F');
INSERT INTO PT_TEST VALUES (7 , 'G');
INSERT INTO PT_TEST VALUES (8 , 'H');
INSERT INTO PT_TEST VALUES (9 , 'I');
INSERT INTO PT_TEST VALUES (10 , 'J');
INSERT INTO PT_TEST VALUES (11 , 'K');



//Tset

SELECT * FROM PT_TEST
SELECT * FROM PT_TEST PARTITION (PT_1);
SELECT * FROM PT_TEST PARTITION (PT_2);



//drop partition

ALTER TABLE pt_test DROP PARTITION pt_1;



// change partition name

ALTER TABLE pt_test RENAME PARTITION pt_1 TO pt_one;



//  TRUNCATE partition data

ALTER TABLE pt_test TRUNCATE PARTITION pt_1;

No comments:

Post a Comment