adding a partition 2

[jaxtell]

Hi,
I have a some tables set up like so

CREATE TABLE ACTIVITY
(
.
.
.
  CLOSED_DATE          DATE,
.
.
.
)
TABLESPACE ACTIVITY_MAX_D
PCTUSED    80
PCTFREE    10
INITRANS   4
MAXTRANS   255
STORAGE    (
            PCTINCREASE      0
            BUFFER_POOL      KEEP
           )
LOGGING
PARTITION BY RANGE (CLOSED_DATE)
(  
  PARTITION ACTIVITY_2008 VALUES LESS THAN (TO_DATE(' 2009-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    LOGGING
    NOCOMPRESS
    TABLESPACE ACTIVITY_2008_D
    PCTUSED    80
    PCTFREE    2
    INITRANS   4
    MAXTRANS   255
    STORAGE    (
                INITIAL          64K
                MINEXTENTS       1
                MAXEXTENTS       UNLIMITED
                FREELISTS        1
                FREELIST GROUPS  1
                BUFFER_POOL      KEEP
               ),  
  PARTITION ACTIVITY_2009 VALUES LESS THAN (TO_DATE(' 2010-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    LOGGING
    NOCOMPRESS
    TABLESPACE ACTIVITY_2009_D
    PCTUSED    80
    PCTFREE    2
    INITRANS   4
    MAXTRANS   255
    STORAGE    (
                INITIAL          64K
                MINEXTENTS       1
                MAXEXTENTS       UNLIMITED
                FREELISTS        1
                FREELIST GROUPS  1
                BUFFER_POOL      KEEP
               ),   
  PARTITION ACTIVITY_MAX VALUES LESS THAN (MAXVALUE)
    LOGGING
    NOCOMPRESS
    TABLESPACE ACTIVITY_MAX_D
    PCTUSED    80
    PCTFREE    10
    INITRANS   4
    MAXTRANS   255
    STORAGE    (
                INITIAL          64K
                MINEXTENTS       1
                MAXEXTENTS       UNLIMITED
                FREELISTS        1
                FREELIST GROUPS  1
                BUFFER_POOL      KEEP
               )
)
NOCOMPRESS 
CACHE
NOPARALLEL
MONITORING
ENABLE ROW MOVEMENT;

Now I need to add a new partition for 2010. I can't figure out the best way to do that. I thought I could just add a partition, but that doesn't seem to work. Is there a good way to add partitions to a table, or will I need to rename and recreate it?

-----------------------------------------
I cannot be bought. Find leasing information at

http://www.joshaxtell.com/

 

[karluk]

You don't give the error message from your attempt to add a partition for 2010, but it appears that the problem is that the rows you intend to store in the new partition are already included in your ACTIVITY_MAX partition. Instead of adding a completely new partition, I suggest you try splitting ACTIVITY_MAX. Something like the following should work:

alter table activity split partition activity_max at (TO_DATE(' 2011-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS'))
into
(
  PARTITION ACTIVITY_2010
    LOGGING
    NOCOMPRESS
    TABLESPACE ACTIVITY_2010_D
    PCTUSED    80
    PCTFREE    2
    INITRANS   4
    MAXTRANS   255
    STORAGE    (
                INITIAL          64K
                MINEXTENTS       1
                MAXEXTENTS       UNLIMITED
                FREELISTS        1
                FREELIST GROUPS  1
                BUFFER_POOL      KEEP
               ),
  PARTITION ACTIVITY_MAX
    LOGGING
    NOCOMPRESS
    TABLESPACE ACTIVITY_MAX_D
    PCTUSED    80
    PCTFREE    10
    INITRANS   4
    MAXTRANS   255
    STORAGE    (
                INITIAL          64K
                MINEXTENTS       1
                MAXEXTENTS       UNLIMITED
                FREELISTS        1
                FREELIST GROUPS  1
                BUFFER_POOL      KEEP
               )
);

 

[jaxtell]

Yes, thats exactly what I needed. Thank You.

-----------------------------------------
I cannot be bought. Find leasing information at

http://www.joshaxtell.com/

 

[SantaMufasa]

Excellent posting, Karl. Havanuthuh

!

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”

 

[jaxtell]

I noticed some problems with partitioned indexes on tables where I split a partition. Recreating those indexes solved the problem. I think it is necessary to rebuild or recreate indexes after splitting a partition. But you probably already knew that.

-----------------------------------------
I cannot be bought. Find leasing information at

http://www.joshaxtell.com/

 

[Dagon]

You are probably talking about global indexes on the table. Global indexes are not partitioned and will therefore need to be rebuilt if any of the table partitions are changed. Local indexes are tied to the table partitions and should therefore automatically be handled by the table partition split.

 

[jaxtell]

No, I'm talking about a local index, tied to the table partitions. I started getting erroneous results from a view that used the MAX partition of a table when it was using a non-unique local index containing the two columns I was querying against. I was able to recreate the same issue with this query

select * from object_request PARTITION (object_request_max) where object_type_id = 2 and object_id = 1338161

This was returning incorrect results and the object_type_id in the results was so large it was breaking the java code calling it, the value wouldn't fit in an int.

java.sql.SQLException: Numeric Overflow

Thats how I found the issue.
It was using this index

CREATE INDEX OBJECT_REQUEST_N1 ON OBJECT_REQUEST
(OBJECT_ID, OBJECT_TYPE_ID)
  INITRANS   2
  MAXTRANS   255
  STORAGE    (
              MINEXTENTS       1
              MAXEXTENTS       UNLIMITED
              PCTINCREASE      0
              FREELISTS        2
              FREELIST GROUPS  2
             )
LOGGING
LOCAL (  
  PARTITION OBJECT_REQUEST_2000
    LOGGING
    NOCOMPRESS
    TABLESPACE OBJECT_REQUEST_2000_X
    PCTFREE    10
    INITRANS   2
    MAXTRANS   255
    STORAGE    (
                INITIAL          64K
                MINEXTENTS       1
                MAXEXTENTS       UNLIMITED
                FREELISTS        1
                FREELIST GROUPS  1
                BUFFER_POOL      DEFAULT
               ),  
.
.
.  
  PARTITION OBJECT_REQUEST_MAX
    LOGGING
    NOCOMPRESS
    TABLESPACE OBJECT_REQUEST_MAX_X
    PCTFREE    10
    INITRANS   2
    MAXTRANS   255
    STORAGE    (
                INITIAL          64K
                MINEXTENTS       1
                MAXEXTENTS       UNLIMITED
                FREELISTS        1
                FREELIST GROUPS  1
                BUFFER_POOL      DEFAULT
               )
)
NOPARALLEL;

-----------------------------------------
I cannot be bought. Find leasing information at

http://www.joshaxtell.com/