How to add Subparition for exisiting table in Oracle

user12251389

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 ) ;

Unknown

I wanted to add subpartition for 'US' market in SUBPARTITION TEMPLATE for exisitng INT_TMP table.

Ok - go ahead. What is it that is stopping you from doing that?

Have you taken the first step - RTFM?

The docs have sections for how to change partition and subpartition templates and the effects of having a DEFAULT partition/subpartition.

Unknown

I wanted to add subpartition for 'US' market in SUBPARTITION TEMPLATE for exisitng INT_TMP table.

Ok - go ahead. What is it that is stopping you from doing that?

Have you taken the first step - RTFM?

The docs have sections for how to change partition and subpartition templates and the effects of having a DEFAULT partition/subpartition.

user12251389

which Docs ? I dont know actually how to alter the table and add partition and does it have any effect to my current data of the table ?

Etbin

https://docs.oracle.com/cd/E11882_01/server.112/e41084/statements_3001.htm#SQLRF01001

https://docs.oracle.com/database/121/SQLRF/statements_3001.htm#SQLRF01001

Regards

Etbin

Unknown

which Docs ?

Really? If you are using Oracle you would look in ORACLE DOCS.

Everyone using Oracle should have downloaded ALL of the Oracle docs so they have them readily available

And if you don't know how to find info on the internet then I suggest you forget about Oracle for now and FIRST learn how to search the internet for info.

This SIMPLE search string 'oracle 12c How to add Subparition for exisiting table in Oracle' returns the Oracle doc on the first page.

See the string above? It uses EXACTLY THE SAME WORDS AS THE THREAD SUBJECT that you created.

I dont know actually how to alter the table and add partition

Well - that is EXACTLY what documentation is for - to tell you how to use Oracle functionality.

Another SIMPLE search string 'oracle 12c alter table' returns the FIRST FOUR results to Oracle docs that discuss 'alter table' and how to use it.

and does it have any effect to my current data of the table ?

What happened when you tried it on a test table and test data?

Did you learn to walk or speak by reading a book? No - you learn by DOING. Same with Oracle.

1. create a simple table

2. put data into each partition and subpartition

3. do an ALTER and SEE WHAT HAPPENS

If you current table has data in the DEFAULT partition that belongs to the new partition then, yes, you will need to deal with that.