Home » SQL & PL/SQL » SQL & PL/SQL » Adding a Partition to an existing partioned table (AIX, UNIX)
Adding a Partition to an existing partioned table [message #304340] Tue, 04 March 2008 23:29 Go to next message
indrajeet23
Messages: 2
Registered: March 2008
Junior Member
Hi
Recently i was asked a question.
If there was a table that is partioned monthly from January to Decemeber, and partions were added to it every month. Later on the DBA realises that one partion is missing (Ex: JULY). How can the DBA add the partition.
a.Does he have to drop the other partitions?
b. Can he jus add a partition. assuming its range partitions.
c. or he has to recreate the entire partition table again.

remember its prod database so pls gimme your answer with a lot of thought.
Anyways Thank You.
-Indra
Re: Adding a Partition to an existing partioned table [message #304348 is a reply to message #304340] Tue, 04 March 2008 23:52 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8636
Registered: November 2002
Location: California, USA
Senior Member
SCOTT@orcl_11g> CREATE TABLE part
  2    (dt  DATE)
  3    PARTITION BY RANGE (dt)
  4    (PARTITION p_jan VALUES LESS THAN (TO_DATE ('02-01-2008', 'mm-dd-yyyy') ),
  5  	PARTITION p_feb VALUES LESS THAN (TO_DATE ('03-01-2008', 'mm-dd-yyyy') ),
  6  	PARTITION p_mar VALUES LESS THAN (TO_DATE ('04-01-2008', 'mm-dd-yyyy') ),
  7  	PARTITION p_apr VALUES LESS THAN (TO_DATE ('05-01-2008', 'mm-dd-yyyy') ),
  8  	PARTITION p_may VALUES LESS THAN (TO_DATE ('06-01-2008', 'mm-dd-yyyy') ),
  9  	PARTITION p_jun VALUES LESS THAN (TO_DATE ('07-01-2008', 'mm-dd-yyyy') ),
 10  	PARTITION p_aug VALUES LESS THAN (TO_DATE ('09-01-2008', 'mm-dd-yyyy') ),
 11  	PARTITION p_sep VALUES LESS THAN (TO_DATE ('10-01-2008', 'mm-dd-yyyy') ),
 12  	PARTITION p_oct VALUES LESS THAN (TO_DATE ('11-01-2008', 'mm-dd-yyyy') ),
 13  	PARTITION p_nov VALUES LESS THAN (TO_DATE ('12-01-2008', 'mm-dd-yyyy') ),
 14  	PARTITION p_dec VALUES LESS THAN (TO_DATE ('01-01-2009', 'mm-dd-yyyy') ))
 15  /

Table created.

SCOTT@orcl_11g> INSERT INTO part VALUES (TO_DATE ('06-02-2008', 'MM-DD-YYYY'))
  2  /

1 row created.

SCOTT@orcl_11g> INSERT INTO part VALUES (TO_DATE ('07-04-2008', 'MM-DD-YYYY'))
  2  /

1 row created.

SCOTT@orcl_11g> INSERT INTO part VALUES (TO_DATE ('08-02-2008', 'MM-DD-YYYY'))
  2  /

1 row created.

SCOTT@orcl_11g> SELECT * FROM part PARTITION (p_jun)
  2  /

DT
---------
02-JUN-08

SCOTT@orcl_11g> SELECT * FROM part PARTITION (p_aug)
  2  /

DT
---------
04-JUL-08
02-AUG-08

SCOTT@orcl_11g> ALTER TABLE part
  2  SPLIT PARTITION p_aug
  3  AT (TO_DATE ('08-01-2008', 'MM-DD-YYYY'))
  4  INTO (PARTITION p_jul, PARTITION p_aug)
  5  /

Table altered.

SCOTT@orcl_11g> SELECT * FROM part PARTITION (p_jun)
  2  /

DT
---------
02-JUN-08

SCOTT@orcl_11g> SELECT * FROM part PARTITION (p_jul)
  2  /

DT
---------
04-JUL-08

SCOTT@orcl_11g> SELECT * FROM part PARTITION (p_aug)
  2  /

DT
---------
02-AUG-08

SCOTT@orcl_11g> 

Re: Adding a Partition to an existing partioned table [message #304602 is a reply to message #304348] Wed, 05 March 2008 16:01 Go to previous message
indrajeet23
Messages: 2
Registered: March 2008
Junior Member
Thanks i appreciate your response wouldnt have occurred to me.
-Indra
Previous Topic: the ZERO didn't appear ..... why ?
Next Topic: ORA-01861: literal does not match format string
Goto Forum:
  


Current Time: Fri Dec 09 07:42:16 CST 2016

Total time taken to generate the page: 0.27131 seconds