Solved

Oracle Paritioning: alter table

Posted on 2011-09-21
3
332 Views
Last Modified: 2012-05-12

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

0
Comment
Question by:d27m11y
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 
LVL 74

Accepted Solution

by:
sdstuber earned 500 total points
ID: 36574733
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
 
LVL 74

Assisted Solution

by:sdstuber
sdstuber earned 500 total points
ID: 36574743
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
 

Author Closing Comment

by:d27m11y
ID: 36574764
Great, thanks for your help!
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
This video shows how to recover a database from a user managed backup
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

734 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question