Solved

Oracle Paritioning: alter table

Posted on 2011-09-21
3
327 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
Comment Utility
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
Comment Utility
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
Comment Utility
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.

Join & Write a Comment

Subquery in Oracle: Sub queries are one of advance queries in oracle. Types of advance queries: •      Sub Queries •      Hierarchical Queries •      Set Operators Sub queries are know as the query called from another query or another subquery. It can …
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, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.

772 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now