Skip to Main Content
Forums

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

How to add Subparition for exisiting table in Oracle

user12251389Apr 4 2018 — edited Apr 4 2018

I wanted to add subpartition for 'US' market in SUBPARTITION TEMPLATE for exisitng INT_TMP table. So the subpartition should be like: SUBPARTITION "SP_US" VALUES ( 'US' ) in SUBPARTITION TEMPLATE for exisitng INT_TMP table.

Below is my Create table statment:

CREATE TABLE INT_TMP
       (LOAD_ID NUMBER 
    , SYS_DB_NAME VARCHAR2(50 BYTE) 
    , ENTITY_ID NUMBER 
    , CHK_DATE DATE 
    , VALUE NUMBER 
    , NBR_CHECK VARCHAR2(50 BYTE) 
    , SOURCE VARCHAR2(50 BYTE),
      MONTH_ID NUMBER
       ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 
     NOCOMPRESS 
      STORAGE(
      BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
      TABLESPACE "DATA_INT_TCA_EDW" 
      PARTITION BY LIST ("MONTH_ID") 
      SUBPARTITION BY LIST ("SYS_DB_NAME") 
      SUBPARTITION TEMPLATE ( 
        SUBPARTITION "SP_SO" VALUES ( 'SO' ), 
        SUBPARTITION "SP_UK" VALUES ( 'UK' ), 
        SUBPARTITION "SP_OTHERS" VALUES ( DEFAULT ) ) 
     ( 
     PARTITION P_201801  VALUES (201801) 
    PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 
      STORAGE(
      BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
      TABLESPACE DATA_INT_TCA_EDW , 
     PARTITION P_201802  VALUES (201802) 
    PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 
      STORAGE(
      BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
      TABLESPACE DATA_INT_TCA_EDW , 
     PARTITION P_201803  VALUES (201803) 
    PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 
      STORAGE(
      BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
      TABLESPACE DATA_INT_TCA_EDW , 
     PARTITION P_201804  VALUES (201804) 
    PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 
      STORAGE(
      BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
      TABLESPACE DATA_INT_TCA_EDW , 
     PARTITION P_201805  VALUES (201805) 
    PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 
      STORAGE(
      BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
      TABLESPACE DATA_INT_TCA_EDW , 
     PARTITION P_201806  VALUES (201806) 
    PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 
      STORAGE(
      BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
      TABLESPACE DATA_INT_TCA_EDW ) ;
This post has been answered by unknown-7404 on Apr 4 2018
Jump to Answer

Comments

Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on May 2 2018
Added on Apr 4 2018
#oracle, #table
4 comments
266 views