Solved

Oracle Paritioning: alter table

Posted on 2011-09-21
3
329 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 73

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 73

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Different synchronization delay settings for individual Insert/Update/Delete operations? 7 66
null value 15 102
history tablespace temp usage 2 42
pl/sql - query very slow 26 60
Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
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…
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines

778 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