Re: create partition oracle

Posted by ErmanArslansOracleBlog on May 04, 2017; 7:31am
URL: http://erman-arslan-s-oracle-forum.124.s1.nabble.com/create-partition-oracle-tp2629p2638.html

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