Oracle Paritioning: alter table


I have table "A" and it is being list partitioned by column runid - which is a number
column like
PAR_100
PAR_10001
PAR_10090 etc.,

There is one partition made as DEFAULT. Please see below. You can notice that
PAR_100518610 is made as DEFAULT. Can I alter this table and make this PAR_100518610 which allows the values as

PARTITION PAR_100518610 VALUES (100518610) . Kindly suggest

Open in new window


Open in new window

CREATE TABLE cns.A
(
  AGE          NUMBER(5),
  YEARSTART    DATE,
  YEAREND      DATE,
  ATTRIBUTEID  NUMBER(12),
  RUNID        NUMBER(12)
)
TABLESPACE A_DATA
PCTUSED    0
PCTFREE    10
INITRANS   1
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            NEXT             1M
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
           )
LOGGING
PARTITION BY LIST (RUNID)
(  
  PARTITION PAR_100 VALUES (100)
    LOGGING
    COMPRESS FOR DIRECT_LOAD OPERATIONS 
    TABLESPACE A_DATA
    PCTFREE    10
    INITRANS   1
    MAXTRANS   255
    STORAGE    (
                INITIAL          64K
                NEXT             1M
                MINEXTENTS       1
                MAXEXTENTS       UNLIMITED
                BUFFER_POOL      DEFAULT
               ),

  PARTITION PAR_100518610 VALUES (DEFAULT)
    LOGGING
    NOCOMPRESS
    TABLESPACE A_DATA
    PCTFREE    10
    INITRANS   1
    MAXTRANS   255
    STORAGE    (
                INITIAL          64K
                NEXT             1M
                MINEXTENTS       1
                MAXEXTENTS       UNLIMITED
                BUFFER_POOL      DEFAULT
               )

Open in new window

d27m11yAsked:
Who is Participating?
 
sdstuberCommented:
you can't do it directly

you'll have to drop the default partition first, then recreate it as a valued partition.

note, if you have data in the partition it will be lost.  so you may want to save the data first

create table a_backup as select * from a partition (par_100518610);

alter table a drop partition PAR_100518610;

alter table a add partition PAR_100518610  values (100518610);

insert into a select * from a_backup;

Open in new window

0
 
sdstuberCommented:
if you default partition has data other than 100518610,  those records can't be inserted back into the table because there will be no partition to accept them.

to resolve that you can either create new partitions for each value you want to allow in
or create a new DEFAULT partition to hold them
0
 
d27m11yAuthor Commented:
Great, thanks for your help!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.