Create a partitioned table from a non partitioned table
Non Partitioned table - zzt_test
CREATE UNIQUE INDEX SVADAPALLI.ZIP_SERVICELOG1 ON SVADAPALLI.ZZT_TEST
(HOST_NAME, PORT, CONTEXT_ID);
ALTER TABLE SVADAPALLI.ZZT_TEST ADD (
CONSTRAINT ZIP_SERVICELOG1
PRIMARY KEY
(HOST_NAME, PORT, CONTEXT_ID)
USING INDEX SVADAPALLI.ZIP_SERVICELOG1
ENABLE VALIDATE);
Create an empty partitioned table
CREATE TABLE SVADAPALLI.ZZT_SERVICE_LOG
(
SERVICE_PATH_NAME VARCHAR2(200 BYTE) NOT NULL,
SERVICE_NAME VARCHAR2(50 BYTE) NOT NULL,
SUBSERVICE_PATH_NAME VARCHAR2(200 BYTE),
SENDER VARCHAR2(50 BYTE),
RECORD_COUNT NUMBER(30),
DATA_SIZE NUMBER(30),
STATUS VARCHAR2(10 BYTE),
INSTANCE_TYPE VARCHAR2(10 BYTE),
START_DATE_TIME VARCHAR2(20 BYTE) NOT NULL,
END_DATE_TIME VARCHAR2(20 BYTE) NOT NULL,
USER_INFO VARCHAR2(100 BYTE),
SERVICE_INFO VARCHAR2(100 BYTE),
HOST_NAME VARCHAR2(50 BYTE) NOT NULL,
PORT VARCHAR2(10 BYTE) NOT NULL,
USER_DATA CLOB DEFAULT EMPTY_CLOB(),
ERROR_MESSAGE VARCHAR2(4000 BYTE),
CONTEXT_ID VARCHAR2(50 BYTE) NOT NULL,
PARENT_CONTEXT_ID VARCHAR2(50 BYTE),
ROOT_CONTEXT_ID VARCHAR2(50 BYTE),
LOG_DATA CHAR(1 BYTE) DEFAULT 'N',
CREATE_DATE VARCHAR2(20 BYTE) DEFAULT SYSDATE NOT NULL
)
LOB (USER_DATA) STORE AS LOBSEGMENT_SERVICE_LOG (
TABLESPACE HMA_TM_PROD_GEN2_MONITOR_DATA
ENABLE STORAGE IN ROW
CHUNK 32768
PCTVERSION 0
NOCACHE
LOGGING)
NOCOMPRESS
TABLESPACE HMA_TM_PROD_GEN2_MONITOR_DATA
RESULT_CACHE (MODE DEFAULT)
PCTUSED 0
PCTFREE 10
INITRANS 1
MAXTRANS 255
STORAGE (
BUFFER_POOL DEFAULT
FLASH_CACHE DEFAULT
CELL_FLASH_CACHE DEFAULT
)
LOGGING
PARTITION BY RANGE (CREATE_DATE)
(
PARTITION ZZT_SERVICE_LOG_2016MAX VALUES LESS THAN ('MAXVALUE')
LOGGING
NOCOMPRESS
TABLESPACE USERS
LOB (USER_DATA) STORE AS (
TABLESPACE USERS
ENABLE STORAGE IN ROW
CHUNK 8192
RETENTION
NOCACHE
LOGGING
STORAGE (
INITIAL 64K
NEXT 1M
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
BUFFER_POOL DEFAULT
FLASH_CACHE DEFAULT
CELL_FLASH_CACHE DEFAULT
))
PCTFREE 10
INITRANS 1
MAXTRANS 255
STORAGE (
INITIAL 64K
NEXT 1M
MAXSIZE UNLIMITED
MINEXTENTS 1
MAXEXTENTS UNLIMITED
BUFFER_POOL DEFAULT
FLASH_CACHE DEFAULT
CELL_FLASH_CACHE DEFAULT
)
)
NOCACHE
NOPARALLEL
MONITORING
ENABLE ROW MOVEMENT;
CREATE UNIQUE INDEX SVADAPALLI.ZIP_SERVICELOG ON SVADAPALLI.ZZT_SERVICE_LOG
(HOST_NAME, PORT, CONTEXT_ID)
LOGGING
TABLESPACE HMA_TM_PROD_GEN2_MONITOR_INDEX
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 64K
NEXT 1M
MAXSIZE UNLIMITED
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
BUFFER_POOL DEFAULT
FLASH_CACHE DEFAULT
CELL_FLASH_CACHE DEFAULT
)
NOPARALLEL;
CREATE INDEX SVADAPALLI.ZX4_SERVICE_LOG ON SVADAPALLI.ZZT_SERVICE_LOG
(CREATE_DATE)
LOGGING
TABLESPACE HMA_TM_PROD_GEN2_MONITOR_DATA
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 64K
NEXT 1M
MAXSIZE UNLIMITED
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
BUFFER_POOL DEFAULT
FLASH_CACHE DEFAULT
CELL_FLASH_CACHE DEFAULT
)
NOPARALLEL;
CREATE INDEX SVADAPALLI.ZX1_SERVICE_LOG ON SVADAPALLI.ZZT_SERVICE_LOG
(ROOT_CONTEXT_ID)
LOGGING
TABLESPACE HMA_TM_PROD_GEN2_MONITOR_INDEX
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 64K
NEXT 1M
MAXSIZE UNLIMITED
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
BUFFER_POOL DEFAULT
FLASH_CACHE DEFAULT
CELL_FLASH_CACHE DEFAULT
)
NOPARALLEL;
ALTER TABLE SVADAPALLI.ZZT_SERVICE_LOG ADD (
CONSTRAINT ZIP_SERVICELOG
PRIMARY KEY
(HOST_NAME, PORT, CONTEXT_ID)
USING INDEX SVADAPALLI.ZIP_SERVICELOG
ENABLE VALIDATE);
Verify DBMS redefinition
EXEC DBMS_REDEFINITION.can_redef_table('svadapalli','zzt_test');
Once the primary keys match you can exchange partition
ALTER TABLE zzt_service_log
EXCHANGE PARTITION zzt_service_log_2016max
WITH TABLE zzt_test
WITHOUT VALIDATION
UPDATE GLOBAL INDEXES;
After exchange you can split and create new partitions from split partition document
Example for partition with range date( splitting from max partition)
ALTER TABLE SVADAPALLI.SUB_EVENT_MASTER_SALES_CLOSE SPLIT PARTITION PART_MAXI AT(TO_DATE(' 2012-01-31 23:59:59', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) INTO (PARTITION JAN_2012 TABLESPACE HMA_TM_PROD_DATA, PARTITION PART_MAXI) UPDATE GLOBAL INDEXES;
