Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Oracle Paritioning: alter table

Posted on 2011-09-21
3
Medium Priority
?
341 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
  • 2
3 Comments
 
LVL 74

Accepted Solution

by:
sdstuber earned 2000 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 2000 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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.
Suggested Courses

824 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