Oracle Database 12.1.0.2
Red Hat Linux 7.2
Hello,
on a production database, every month partitions are created using monthly interval partitioning.
The partitions are assigned a default tablespace MOBILE_DATA_009. Is is possible to assign a new tablespace for every partitions which are created? For example, for next partition which is created, a tablespace MOBILE_DATA_010 with a default size of 100g is assigned.
CREATE TABLE "ARCHICOM"."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 "MOBILE_DATA_2017_01"
PCTFREE 10 INITRANS 2 MAXTRANS 255 LOGGING
STORAGE( INITIAL 4294967296
FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "MOBILE_DATA_005" ) ENABLE
) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 LOGGING
STORAGE(
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "MOBILE_DATA_009"
PARTITION BY RANGE ("TIME") INTERVAL (NUMTOYMINTERVAL('1','MONTH'))
(PARTITION "MOBILE_DATA_2017_01" VALUES LESS THAN (TO_DATE(' 2016-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( INITIAL 4294967296
FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "MOBILE_DATA_005" ) ;
Regards,
Joe