create partition oracle

[Roshan]

Hi Erman,


 
As shown above, MOBILE_DATA_2017_06 is to be created at the beginning of month June.
untitled.png

How can I automatically create new partition for the table mobile_data? Should I modify the sql for each partitioned table in sqldeveloper?
 
Regards,
Roshan

[ErmanArslansOracleBlog]

Hi Joe,

Please use your own account.
At least for the next time.

Your answer is in here https://docs.oracle.com/cd/E11882_01/server.112/e25523/part_admin002.htm

Section: Adding Partitions

This section describes how to manually add new partitions to a partitioned table and explains why partitions cannot be specifically added to most partitioned indexes.

[Roshan]

Hi Erman,

I managed to create the new partition monthly.

As example:


  CREATE TABLE "ARCHICOM"."INTERVAL_TAB"
   ( "ID" NUMBER,
        "CODE" VARCHAR2(10 BYTE),
        "DESCRIPTION" VARCHAR2(50 BYTE),
        "CREATED_DATE" DATE
   ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
  STORAGE(
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "USERS"
  PARTITION BY RANGE ("CREATED_DATE") INTERVAL (NUMTOYMINTERVAL(1,'MONTH'))
 (PARTITION "PART_02"  VALUES LESS THAN (TO_DATE(' 2000-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) SEGMENT CREATION DEFERRED
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
 NOCOMPRESS LOGGING
  STORAGE(partitionmobile_data.txt
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "USERS" ) ;


I have a table mobile_data. I have to insert the partition command shown above(bold) and recreate the table. The sql is attached.
Can you please help plugging it. I am getting error in red.
partitionmobile_data.rar

Thanks

[ErmanArslansOracleBlog]

Hi Roshan,

what error you are getting? I don't see any errors --(I am getting error in red?)

[ErmanArslansOracleBlog]

Anyways,

I modified the script as seen below;
--change the tablespace names according to your needs

Please test it and update me Roshan.

CREATE TABLE "MOBILE_DATA"

   (    "SEQ_NUM" VARCHAR2(17 BYTE) NOT NULL ENABLE,

    "TYPE" VARCHAR2(18 BYTE),

    "WAY" VARCHAR2(7 BYTE),

    "RECTYPE" NUMBER(3,0),

    "SERVICE" VARCHAR2(17 BYTE),

    "POSTPRE" VARCHAR2(3 BYTE),

    "IMSI" VARCHAR2(17 BYTE),

    "MSISDN" VARCHAR2(32 BYTE),

    "CALLINGNUM" VARCHAR2(32 BYTE) NOT NULL ENABLE,

    "DISP_CALLINGNUM" VARCHAR2(32 BYTE),

    "CALLEDNUM" VARCHAR2(32 BYTE),

    "DISP_CALLEDNUM" VARCHAR2(32 BYTE),

    "DATADATE" DATE NOT NULL ENABLE,

    "TIME" DATE,

    "DURATION" NUMBER(16,0),

    "VOLIN" NUMBER(20,0),

    "VOLOUT" NUMBER(20,0),

    "CHARGE" NUMBER(16,0),

    "TYPENUM" VARCHAR2(2 BYTE),

    "CDRTYPE" VARCHAR2(20 BYTE),

    "CONTYPE" VARCHAR2(10 BYTE),

    "CMLSRVLVL" VARCHAR2(20 BYTE),

    "CMLSRVKEY" VARCHAR2(20 BYTE),

    "RECID" VARCHAR2(40 BYTE),

    "CALLREF" VARCHAR2(15 BYTE),

    "LAC" VARCHAR2(10 BYTE),

    "CELLID" VARCHAR2(20 BYTE),

    "APNNI" VARCHAR2(63 BYTE),

    "APNOI" VARCHAR2(37 BYTE),

    "CDRFILENAME" VARCHAR2(256 BYTE),

    "IMEI" VARCHAR2(16 BYTE),

    "CMLLEGINFO" VARCHAR2(20 BYTE),

    "CMLINITCF" VARCHAR2(20 BYTE),

    "CALLTYPE" VARCHAR2(4 BYTE),

    "INROUTE" VARCHAR2(12 BYTE),

    "OUTROUTE" VARCHAR2(12 BYTE),

    "SUPPSRVS" VARCHAR2(16 BYTE),

    "SYSTEMTYPE" VARCHAR2(32 BYTE),

    "NETELEMENT" VARCHAR2(32 BYTE),

    "FU" VARCHAR2(256 BYTE),

     CONSTRAINT "MOBILE_DATA_PK" PRIMARY KEY ("SEQ_NUM", "CALLINGNUM", "TIME")

  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255  LOGGING

  STORAGE(

  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) LOCAL

    )

   PARTITION BY RANGE ("TIME") INTERVAL (NUMTOYMINTERVAL(1,'MONTH'))

 (PARTITION "PART_02"  VALUES LESS THAN (TO_DATE(' 2000-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) SEGMENT CREATION DEFERRED

  STORAGE(INITIAL 4294967296 NEXT 29360128 MINEXTENTS 1 MAXEXTENTS 2147483645

  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1

  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)

  TABLESPACE "APPS_TS_TX_DATA" ) ;

[Roshan]

Thanks. It's working now