CREATE TABLE 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 ICON_LOB_DATA_TEST
ENABLE STORAGE IN ROW
CHUNK 32768
RETENTION
NOCACHE
LOGGING)
NOCOMPRESS
TABLESPACE ICON_LOG_DATA_TEST
PCTUSED 0
PCTFREE 10
INITRANS 1
MAXTRANS 255
STORAGE (
BUFFER_POOL DEFAULT
)
LOGGING
PARTITION BY RANGE (CREATE_DATE)
(
PARTITION ZZT_SERVICE_LOG_2014MAR VALUES LESS THAN ('20140401000000')
LOGGING
NOCOMPRESS
TABLESPACE ICON_LOG_DATA
LOB (USER_DATA) STORE AS (
TABLESPACE ICON_LOB_DATA
ENABLE STORAGE IN ROW
CHUNK 32768
PCTVERSION 0
NOCACHE
LOGGING
STORAGE (
INITIAL 64K
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
BUFFER_POOL DEFAULT
))
PCTFREE 10
INITRANS 1
MAXTRANS 255
STORAGE (
INITIAL 64K
MINEXTENTS 1
MAXEXTENTS UNLIMITED
BUFFER_POOL DEFAULT
),
PARTITION ZZT_SERVICE_LOG_2014APR VALUES LESS THAN ('20140501000000')
LOGGING
NOCOMPRESS
TABLESPACE ICON_LOG_DATA
LOB (USER_DATA) STORE AS (
TABLESPACE ICON_LOB_DATA
ENABLE STORAGE IN ROW
CHUNK 32768
PCTVERSION 0
NOCACHE
LOGGING
STORAGE (
INITIAL 64K
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
BUFFER_POOL DEFAULT
))
PCTFREE 10
INITRANS 1
MAXTRANS 255
STORAGE (
INITIAL 64K
MINEXTENTS 1
MAXEXTENTS UNLIMITED
BUFFER_POOL DEFAULT
),
PARTITION ZZT_SERVICE_LOG_2014MAY VALUES LESS THAN ('20140601000000')
LOGGING
NOCOMPRESS
TABLESPACE ICON_LOG_DATA
LOB (USER_DATA) STORE AS (
TABLESPACE ICON_LOB_DATA
ENABLE STORAGE IN ROW
CHUNK 32768
PCTVERSION 0
NOCACHE
LOGGING
STORAGE (
INITIAL 64K
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
BUFFER_POOL DEFAULT
))
PCTFREE 10
INITRANS 1
MAXTRANS 255
STORAGE (
INITIAL 64K
MINEXTENTS 1
MAXEXTENTS UNLIMITED
BUFFER_POOL DEFAULT
),
PARTITION ZZT_SERVICE_LOG_2014JUN VALUES LESS THAN ('20140701000000')
LOGGING
NOCOMPRESS
TABLESPACE ICON_LOG_DATA
LOB (USER_DATA) STORE AS (
TABLESPACE ICON_LOB_DATA
ENABLE STORAGE IN ROW
CHUNK 32768
PCTVERSION 0
NOCACHE
LOGGING
STORAGE (
INITIAL 64K
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
BUFFER_POOL DEFAULT
))
PCTFREE 10
INITRANS 1
MAXTRANS 255
STORAGE (
INITIAL 64K
MINEXTENTS 1
MAXEXTENTS UNLIMITED
BUFFER_POOL DEFAULT
),
PARTITION ZZT_SERVICE_LOG_2014JUL VALUES LESS THAN ('20140801000000')
LOGGING
NOCOMPRESS
TABLESPACE ICON_LOG_DATA
LOB (USER_DATA) STORE AS (
TABLESPACE ICON_LOB_DATA
ENABLE STORAGE IN ROW
CHUNK 32768
PCTVERSION 0
NOCACHE
LOGGING
STORAGE (
INITIAL 64K
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
BUFFER_POOL DEFAULT
))
PCTFREE 10
INITRANS 1
MAXTRANS 255
STORAGE (
INITIAL 64K
MINEXTENTS 1
MAXEXTENTS UNLIMITED
BUFFER_POOL DEFAULT
),
PARTITION ZZT_SERVICE_LOG_2014AUG VALUES LESS THAN ('20140901000000')
LOGGING
NOCOMPRESS
TABLESPACE ICON_LOG_DATA
LOB (USER_DATA) STORE AS (
TABLESPACE ICON_LOB_DATA
ENABLE STORAGE IN ROW
CHUNK 32768
PCTVERSION 0
NOCACHE
LOGGING
STORAGE (
INITIAL 64K
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
BUFFER_POOL DEFAULT
))
PCTFREE 10
INITRANS 1
MAXTRANS 255
STORAGE (
INITIAL 64K
MINEXTENTS 1
MAXEXTENTS UNLIMITED
BUFFER_POOL DEFAULT
),
PARTITION ZZT_SERVICE_LOG_2014SEP VALUES LESS THAN ('20141001000000')
LOGGING
NOCOMPRESS
TABLESPACE ICON_LOG_DATA
LOB (USER_DATA) STORE AS (
TABLESPACE ICON_LOB_DATA
ENABLE STORAGE IN ROW
CHUNK 32768
PCTVERSION 0
NOCACHE
LOGGING
STORAGE (
INITIAL 64K
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
BUFFER_POOL DEFAULT
))
PCTFREE 10
INITRANS 1
MAXTRANS 255
STORAGE (
INITIAL 64K
MINEXTENTS 1
MAXEXTENTS UNLIMITED
BUFFER_POOL DEFAULT
),
PARTITION ZZT_SERVICE_LOG_2014OCT VALUES LESS THAN ('20141101000000')
LOGGING
NOCOMPRESS
TABLESPACE ICON_LOG_DATA
LOB (USER_DATA) STORE AS (
TABLESPACE ICON_LOB_DATA
ENABLE STORAGE IN ROW
CHUNK 32768
PCTVERSION 0
NOCACHE
LOGGING
STORAGE (
INITIAL 64K
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
BUFFER_POOL DEFAULT
))
PCTFREE 10
INITRANS 1
MAXTRANS 255
STORAGE (
INITIAL 64K
MINEXTENTS 1
MAXEXTENTS UNLIMITED
BUFFER_POOL DEFAULT
),
PARTITION ZZT_SERVICE_LOG_2014NOV VALUES LESS THAN ('20141201000000')
LOGGING
NOCOMPRESS
TABLESPACE ICON_LOG_DATA
LOB (USER_DATA) STORE AS (
TABLESPACE ICON_LOB_DATA
ENABLE STORAGE IN ROW
CHUNK 32768
PCTVERSION 0
NOCACHE
LOGGING
STORAGE (
INITIAL 64K
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
BUFFER_POOL DEFAULT
))
PCTFREE 10
INITRANS 1
MAXTRANS 255
STORAGE (
INITIAL 64K
MINEXTENTS 1
MAXEXTENTS UNLIMITED
BUFFER_POOL DEFAULT
),
PARTITION ZZT_SERVICE_LOG_2014MAX VALUES LESS THAN (MAXVALUE)
LOGGING
NOCOMPRESS
TABLESPACE ICON_LOG_DATA
LOB (USER_DATA) STORE AS (
TABLESPACE ICON_LOB_DATA
ENABLE STORAGE IN ROW
CHUNK 32768
PCTVERSION 0
NOCACHE
LOGGING
STORAGE (
INITIAL 64K
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
BUFFER_POOL DEFAULT
))
PCTFREE 10
INITRANS 1
MAXTRANS 255
STORAGE (
INITIAL 64K
MINEXTENTS 1
MAXEXTENTS UNLIMITED
BUFFER_POOL DEFAULT
)
)
NOCACHE
NOPARALLEL
MONITORING
ENABLE ROW MOVEMENT;
CREATE UNIQUE INDEX ZIP_SERVICELOG ON ZZT_SERVICE_LOG
(HOST_NAME, PORT, CONTEXT_ID)
LOGGING
TABLESPACE ICON_LOG_INDEX
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 64K
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
BUFFER_POOL DEFAULT
)
NOPARALLEL;
CREATE INDEX ZX1_SERVICE_LOG ON ZZT_SERVICE_LOG
(ROOT_CONTEXT_ID)
LOGGING
TABLESPACE ICON_LOG_INDEX
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 64K
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
BUFFER_POOL DEFAULT
)
NOPARALLEL;
CREATE INDEX ZX4_SERVICE_LOG ON ZZT_SERVICE_LOG
(CREATE_DATE)
LOGGING
TABLESPACE ICON_DATA
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 64K
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
BUFFER_POOL DEFAULT
)
NOPARALLEL;
ALTER TABLE ZZT_SERVICE_LOG ADD (
CONSTRAINT ZIP_SERVICELOG
PRIMARY KEY
(HOST_NAME, PORT, CONTEXT_ID)
USING INDEX ZIP_SERVICELOG
ENABLE VALIDATE);
GRANT SELECT ON ZZT_SERVICE_LOG TO ICON_SELECT;
BEGIN
FOR v_LoopCounter IN 1..1000 LOOP
INSERT INTO srav (id)
VALUES (v_LoopCounter);
END LOOP;
END;
/
alter table srav rename to srav1;
create table srav(id number primary key) partition by range(id)
(partition pt_1 values less than ('10000'),
partition pt_2 values less than ('20000'),
partition pt_3 values less than ('30000'),
partition pt_4 values less than ('40000'),
partition pt_5 values less than ('50000'),
partition pt_6 values less than ('60000'),
partition pt_7 values less than ('70000'),
partition pt_8 values less than ('80000'),
partition pt_9 values less than ('90000'),
partition pt_10 values less than ('100000'));
insert into srav select * from srav1;
select * from srav partition(pt_7);
alter table srav drop partition pt_1;
dropping partition deletes data as well.. So if you should use merge to combine the partitions to a different name..
To partition exisitng table we can use dbms_redefinition package
create a new table with partitions and use the package to transfer contents
or
best method is to exchange partition which can be seen in oraclebase website in the following link
http://oracle-base.com/articles/misc/partitioning-an-existing-table-using-exchange-partition.php
ALTER TABLE HMA_TM_PROD_GEN2_MONITOR.ZZT_SERVICE_LOG
SPLIT PARTITION ZZT_SERVICE_LOG_2015MAX AT
('20160201000000')
INTO (PARTITION ZZT_SERVICE_LOG_2016JAN
TABLESPACE HMA_TM_PROD_GEN2_MONITOR_DATA
PCTFREE 10
INITRANS 1
MAXTRANS 255
STORAGE (
MAXSIZE UNLIMITED
BUFFER_POOL DEFAULT
FLASH_CACHE DEFAULT
CELL_FLASH_CACHE DEFAULT
),
PARTITION ZZT_SERVICE_LOG_2015MAX)
UPDATE GLOBAL INDEXES;
ALTER TABLE HMA_TM_PROD_GEN2_MONITOR.ZZT_SERVICE_LOG
RENAME PARTITION ZZT_SERVICE_LOG_2015MAX
TO ZZT_SERVICE_LOG_2016MAX;
Better use toad to split partition.
Select the table, goto partitions tab and alter it . Select partition you want to alter.. click on split and give name and new upper bound values
ALTER TABLE HMA_TM_PROD_GEN2_MONITOR.ZZT_SERVICE_LOG
SPLIT PARTITION ZZT_SERVICE_LOG_2016MAX AT
('20160201000000')
INTO (PARTITION ZZT_SERVICE_LOG_2016JAN
TABLESPACE HMA_TM_PROD_GEN2_MONITOR_DATA
PCTFREE 10
INITRANS 1
MAXTRANS 255
STORAGE (
MAXSIZE UNLIMITED
BUFFER_POOL DEFAULT
FLASH_CACHE DEFAULT
CELL_FLASH_CACHE DEFAULT
),
PARTITION ZZT_SERVICE_LOG_2016MAX)
UPDATE GLOBAL INDEXES;
ALTER TABLE HMA_TM_PROD_GEN2_MONITOR.ZZT_SERVICE_LOG
SPLIT PARTITION ZZT_SERVICE_LOG_2016MAX AT
('20160301000000')
INTO (PARTITION ZZT_SERVICE_LOG_2016FEB
TABLESPACE HMA_TM_PROD_GEN2_MONITOR_DATA
PCTFREE 10
INITRANS 1
MAXTRANS 255
STORAGE (
MAXSIZE UNLIMITED
BUFFER_POOL DEFAULT
FLASH_CACHE DEFAULT
CELL_FLASH_CACHE DEFAULT
),
PARTITION ZZT_SERVICE_LOG_2016MAX)
UPDATE GLOBAL INDEXES;
ALTER TABLE HMA_TM_PROD_GEN2_MONITOR.ZZT_SERVICE_LOG
SPLIT PARTITION ZZT_SERVICE_LOG_2016MAX AT
('20160401000000')
INTO (PARTITION ZZT_SERVICE_LOG_2016MAR
TABLESPACE HMA_TM_PROD_GEN2_MONITOR_DATA
PCTFREE 10
INITRANS 1
MAXTRANS 255
STORAGE (
MAXSIZE UNLIMITED
BUFFER_POOL DEFAULT
FLASH_CACHE DEFAULT
CELL_FLASH_CACHE DEFAULT
),
PARTITION ZZT_SERVICE_LOG_2016MAX)
UPDATE GLOBAL INDEXES;
ALTER TABLE HMA_TM_PROD_GEN2_MONITOR.ZZT_SERVICE_LOG
SPLIT PARTITION ZZT_SERVICE_LOG_2016MAX AT
('20160501000000')
INTO (PARTITION ZZT_SERVICE_LOG_2016APR
TABLESPACE HMA_TM_PROD_GEN2_MONITOR_DATA
PCTFREE 10
INITRANS 1
MAXTRANS 255
STORAGE (
MAXSIZE UNLIMITED
BUFFER_POOL DEFAULT
FLASH_CACHE DEFAULT
CELL_FLASH_CACHE DEFAULT
),
PARTITION ZZT_SERVICE_LOG_2016MAX)
UPDATE GLOBAL INDEXES;
ALTER TABLE HMA_TM_PROD_GEN2_MONITOR.ZZT_SERVICE_LOG
SPLIT PARTITION ZZT_SERVICE_LOG_2016MAX AT
('20160601000000')
INTO (PARTITION ZZT_SERVICE_LOG_2016MAY
TABLESPACE HMA_TM_PROD_GEN2_MONITOR_DATA
PCTFREE 10
INITRANS 1
MAXTRANS 255
STORAGE (
MAXSIZE UNLIMITED
BUFFER_POOL DEFAULT
FLASH_CACHE DEFAULT
CELL_FLASH_CACHE DEFAULT
),
PARTITION ZZT_SERVICE_LOG_2016MAX)
UPDATE GLOBAL INDEXES;
ALTER TABLE HMA_TM_PROD_GEN2_MONITOR.ZZT_SERVICE_LOG
SPLIT PARTITION ZZT_SERVICE_LOG_2016MAX AT
('20160701000000')
INTO (PARTITION ZZT_SERVICE_LOG_2016JUN
TABLESPACE HMA_TM_PROD_GEN2_MONITOR_DATA
PCTFREE 10
INITRANS 1
MAXTRANS 255
STORAGE (
MAXSIZE UNLIMITED
BUFFER_POOL DEFAULT
FLASH_CACHE DEFAULT
CELL_FLASH_CACHE DEFAULT
),
PARTITION ZZT_SERVICE_LOG_2016MAX)
UPDATE GLOBAL INDEXES;
ALTER TABLE HMA_TM_PROD_GEN2_MONITOR.ZZT_SERVICE_LOG
SPLIT PARTITION ZZT_SERVICE_LOG_2016MAX AT
('20160801000000')
INTO (PARTITION ZZT_SERVICE_LOG_2016JUL
TABLESPACE HMA_TM_PROD_GEN2_MONITOR_DATA
PCTFREE 10
INITRANS 1
MAXTRANS 255
STORAGE (
MAXSIZE UNLIMITED
BUFFER_POOL DEFAULT
FLASH_CACHE DEFAULT
CELL_FLASH_CACHE DEFAULT
),
PARTITION ZZT_SERVICE_LOG_2016MAX)
UPDATE GLOBAL INDEXES;
ALTER TABLE HMA_TM_PROD_GEN2_MONITOR.ZZT_SERVICE_LOG
SPLIT PARTITION ZZT_SERVICE_LOG_2016MAX AT
('20160901000000')
INTO (PARTITION ZZT_SERVICE_LOG_2016AUG
TABLESPACE HMA_TM_PROD_GEN2_MONITOR_DATA
PCTFREE 10
INITRANS 1
MAXTRANS 255
STORAGE (
MAXSIZE UNLIMITED
BUFFER_POOL DEFAULT
FLASH_CACHE DEFAULT
CELL_FLASH_CACHE DEFAULT
),
PARTITION ZZT_SERVICE_LOG_2016MAX)
UPDATE GLOBAL INDEXES;
ALTER TABLE HMA_TM_PROD_GEN2_MONITOR.ZZT_SERVICE_LOG
SPLIT PARTITION ZZT_SERVICE_LOG_2016MAX AT
('20161001000000')
INTO (PARTITION ZZT_SERVICE_LOG_2016SEP
TABLESPACE HMA_TM_PROD_GEN2_MONITOR_DATA
PCTFREE 10
INITRANS 1
MAXTRANS 255
STORAGE (
MAXSIZE UNLIMITED
BUFFER_POOL DEFAULT
FLASH_CACHE DEFAULT
CELL_FLASH_CACHE DEFAULT
),
PARTITION ZZT_SERVICE_LOG_2016MAX)
UPDATE GLOBAL INDEXES;
ALTER TABLE HMA_TM_PROD_GEN2_MONITOR.ZZT_SERVICE_LOG
SPLIT PARTITION ZZT_SERVICE_LOG_2016MAX AT
('20161101000000')
INTO (PARTITION ZZT_SERVICE_LOG_2016OCT
TABLESPACE HMA_TM_PROD_GEN2_MONITOR_DATA
PCTFREE 10
INITRANS 1
MAXTRANS 255
STORAGE (
MAXSIZE UNLIMITED
BUFFER_POOL DEFAULT
FLASH_CACHE DEFAULT
CELL_FLASH_CACHE DEFAULT
),
PARTITION ZZT_SERVICE_LOG_2016MAX)
UPDATE GLOBAL INDEXES;
ALTER TABLE HMA_TM_PROD_GEN2_MONITOR.ZZT_SERVICE_LOG
SPLIT PARTITION ZZT_SERVICE_LOG_2016MAX AT
('20161201000000')
INTO (PARTITION ZZT_SERVICE_LOG_2016NOV
TABLESPACE HMA_TM_PROD_GEN2_MONITOR_DATA
PCTFREE 10
INITRANS 1
MAXTRANS 255
STORAGE (
MAXSIZE UNLIMITED
BUFFER_POOL DEFAULT
FLASH_CACHE DEFAULT
CELL_FLASH_CACHE DEFAULT
),
PARTITION ZZT_SERVICE_LOG_2016MAX)
UPDATE GLOBAL INDEXES;
ALTER TABLE HMA_TM_PROD_GEN2_MONITOR.ZZT_SERVICE_LOG
SPLIT PARTITION ZZT_SERVICE_LOG_2016MAX AT
('20170101000000')
INTO (PARTITION ZZT_SERVICE_LOG_2016DEC
TABLESPACE HMA_TM_PROD_GEN2_MONITOR_DATA
PCTFREE 10
INITRANS 1
MAXTRANS 255
STORAGE (
MAXSIZE UNLIMITED
BUFFER_POOL DEFAULT
FLASH_CACHE DEFAULT
CELL_FLASH_CACHE DEFAULT
),
PARTITION ZZT_SERVICE_LOG_2016MAX)
UPDATE GLOBAL INDEXES;
select index_name,status from dba_indexes where table_name='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 ICON_LOB_DATA_TEST
ENABLE STORAGE IN ROW
CHUNK 32768
RETENTION
NOCACHE
LOGGING)
NOCOMPRESS
TABLESPACE ICON_LOG_DATA_TEST
PCTUSED 0
PCTFREE 10
INITRANS 1
MAXTRANS 255
STORAGE (
BUFFER_POOL DEFAULT
)
LOGGING
PARTITION BY RANGE (CREATE_DATE)
(
PARTITION ZZT_SERVICE_LOG_2014MAR VALUES LESS THAN ('20140401000000')
LOGGING
NOCOMPRESS
TABLESPACE ICON_LOG_DATA
LOB (USER_DATA) STORE AS (
TABLESPACE ICON_LOB_DATA
ENABLE STORAGE IN ROW
CHUNK 32768
PCTVERSION 0
NOCACHE
LOGGING
STORAGE (
INITIAL 64K
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
BUFFER_POOL DEFAULT
))
PCTFREE 10
INITRANS 1
MAXTRANS 255
STORAGE (
INITIAL 64K
MINEXTENTS 1
MAXEXTENTS UNLIMITED
BUFFER_POOL DEFAULT
),
PARTITION ZZT_SERVICE_LOG_2014APR VALUES LESS THAN ('20140501000000')
LOGGING
NOCOMPRESS
TABLESPACE ICON_LOG_DATA
LOB (USER_DATA) STORE AS (
TABLESPACE ICON_LOB_DATA
ENABLE STORAGE IN ROW
CHUNK 32768
PCTVERSION 0
NOCACHE
LOGGING
STORAGE (
INITIAL 64K
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
BUFFER_POOL DEFAULT
))
PCTFREE 10
INITRANS 1
MAXTRANS 255
STORAGE (
INITIAL 64K
MINEXTENTS 1
MAXEXTENTS UNLIMITED
BUFFER_POOL DEFAULT
),
PARTITION ZZT_SERVICE_LOG_2014MAY VALUES LESS THAN ('20140601000000')
LOGGING
NOCOMPRESS
TABLESPACE ICON_LOG_DATA
LOB (USER_DATA) STORE AS (
TABLESPACE ICON_LOB_DATA
ENABLE STORAGE IN ROW
CHUNK 32768
PCTVERSION 0
NOCACHE
LOGGING
STORAGE (
INITIAL 64K
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
BUFFER_POOL DEFAULT
))
PCTFREE 10
INITRANS 1
MAXTRANS 255
STORAGE (
INITIAL 64K
MINEXTENTS 1
MAXEXTENTS UNLIMITED
BUFFER_POOL DEFAULT
),
PARTITION ZZT_SERVICE_LOG_2014JUN VALUES LESS THAN ('20140701000000')
LOGGING
NOCOMPRESS
TABLESPACE ICON_LOG_DATA
LOB (USER_DATA) STORE AS (
TABLESPACE ICON_LOB_DATA
ENABLE STORAGE IN ROW
CHUNK 32768
PCTVERSION 0
NOCACHE
LOGGING
STORAGE (
INITIAL 64K
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
BUFFER_POOL DEFAULT
))
PCTFREE 10
INITRANS 1
MAXTRANS 255
STORAGE (
INITIAL 64K
MINEXTENTS 1
MAXEXTENTS UNLIMITED
BUFFER_POOL DEFAULT
),
PARTITION ZZT_SERVICE_LOG_2014JUL VALUES LESS THAN ('20140801000000')
LOGGING
NOCOMPRESS
TABLESPACE ICON_LOG_DATA
LOB (USER_DATA) STORE AS (
TABLESPACE ICON_LOB_DATA
ENABLE STORAGE IN ROW
CHUNK 32768
PCTVERSION 0
NOCACHE
LOGGING
STORAGE (
INITIAL 64K
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
BUFFER_POOL DEFAULT
))
PCTFREE 10
INITRANS 1
MAXTRANS 255
STORAGE (
INITIAL 64K
MINEXTENTS 1
MAXEXTENTS UNLIMITED
BUFFER_POOL DEFAULT
),
PARTITION ZZT_SERVICE_LOG_2014AUG VALUES LESS THAN ('20140901000000')
LOGGING
NOCOMPRESS
TABLESPACE ICON_LOG_DATA
LOB (USER_DATA) STORE AS (
TABLESPACE ICON_LOB_DATA
ENABLE STORAGE IN ROW
CHUNK 32768
PCTVERSION 0
NOCACHE
LOGGING
STORAGE (
INITIAL 64K
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
BUFFER_POOL DEFAULT
))
PCTFREE 10
INITRANS 1
MAXTRANS 255
STORAGE (
INITIAL 64K
MINEXTENTS 1
MAXEXTENTS UNLIMITED
BUFFER_POOL DEFAULT
),
PARTITION ZZT_SERVICE_LOG_2014SEP VALUES LESS THAN ('20141001000000')
LOGGING
NOCOMPRESS
TABLESPACE ICON_LOG_DATA
LOB (USER_DATA) STORE AS (
TABLESPACE ICON_LOB_DATA
ENABLE STORAGE IN ROW
CHUNK 32768
PCTVERSION 0
NOCACHE
LOGGING
STORAGE (
INITIAL 64K
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
BUFFER_POOL DEFAULT
))
PCTFREE 10
INITRANS 1
MAXTRANS 255
STORAGE (
INITIAL 64K
MINEXTENTS 1
MAXEXTENTS UNLIMITED
BUFFER_POOL DEFAULT
),
PARTITION ZZT_SERVICE_LOG_2014OCT VALUES LESS THAN ('20141101000000')
LOGGING
NOCOMPRESS
TABLESPACE ICON_LOG_DATA
LOB (USER_DATA) STORE AS (
TABLESPACE ICON_LOB_DATA
ENABLE STORAGE IN ROW
CHUNK 32768
PCTVERSION 0
NOCACHE
LOGGING
STORAGE (
INITIAL 64K
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
BUFFER_POOL DEFAULT
))
PCTFREE 10
INITRANS 1
MAXTRANS 255
STORAGE (
INITIAL 64K
MINEXTENTS 1
MAXEXTENTS UNLIMITED
BUFFER_POOL DEFAULT
),
PARTITION ZZT_SERVICE_LOG_2014NOV VALUES LESS THAN ('20141201000000')
LOGGING
NOCOMPRESS
TABLESPACE ICON_LOG_DATA
LOB (USER_DATA) STORE AS (
TABLESPACE ICON_LOB_DATA
ENABLE STORAGE IN ROW
CHUNK 32768
PCTVERSION 0
NOCACHE
LOGGING
STORAGE (
INITIAL 64K
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
BUFFER_POOL DEFAULT
))
PCTFREE 10
INITRANS 1
MAXTRANS 255
STORAGE (
INITIAL 64K
MINEXTENTS 1
MAXEXTENTS UNLIMITED
BUFFER_POOL DEFAULT
),
PARTITION ZZT_SERVICE_LOG_2014MAX VALUES LESS THAN (MAXVALUE)
LOGGING
NOCOMPRESS
TABLESPACE ICON_LOG_DATA
LOB (USER_DATA) STORE AS (
TABLESPACE ICON_LOB_DATA
ENABLE STORAGE IN ROW
CHUNK 32768
PCTVERSION 0
NOCACHE
LOGGING
STORAGE (
INITIAL 64K
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
BUFFER_POOL DEFAULT
))
PCTFREE 10
INITRANS 1
MAXTRANS 255
STORAGE (
INITIAL 64K
MINEXTENTS 1
MAXEXTENTS UNLIMITED
BUFFER_POOL DEFAULT
)
)
NOCACHE
NOPARALLEL
MONITORING
ENABLE ROW MOVEMENT;
CREATE UNIQUE INDEX ZIP_SERVICELOG ON ZZT_SERVICE_LOG
(HOST_NAME, PORT, CONTEXT_ID)
LOGGING
TABLESPACE ICON_LOG_INDEX
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 64K
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
BUFFER_POOL DEFAULT
)
NOPARALLEL;
CREATE INDEX ZX1_SERVICE_LOG ON ZZT_SERVICE_LOG
(ROOT_CONTEXT_ID)
LOGGING
TABLESPACE ICON_LOG_INDEX
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 64K
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
BUFFER_POOL DEFAULT
)
NOPARALLEL;
CREATE INDEX ZX4_SERVICE_LOG ON ZZT_SERVICE_LOG
(CREATE_DATE)
LOGGING
TABLESPACE ICON_DATA
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 64K
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
BUFFER_POOL DEFAULT
)
NOPARALLEL;
ALTER TABLE ZZT_SERVICE_LOG ADD (
CONSTRAINT ZIP_SERVICELOG
PRIMARY KEY
(HOST_NAME, PORT, CONTEXT_ID)
USING INDEX ZIP_SERVICELOG
ENABLE VALIDATE);
GRANT SELECT ON ZZT_SERVICE_LOG TO ICON_SELECT;
BEGIN
FOR v_LoopCounter IN 1..1000 LOOP
INSERT INTO srav (id)
VALUES (v_LoopCounter);
END LOOP;
END;
/
alter table srav rename to srav1;
create table srav(id number primary key) partition by range(id)
(partition pt_1 values less than ('10000'),
partition pt_2 values less than ('20000'),
partition pt_3 values less than ('30000'),
partition pt_4 values less than ('40000'),
partition pt_5 values less than ('50000'),
partition pt_6 values less than ('60000'),
partition pt_7 values less than ('70000'),
partition pt_8 values less than ('80000'),
partition pt_9 values less than ('90000'),
partition pt_10 values less than ('100000'));
insert into srav select * from srav1;
select * from srav partition(pt_7);
alter table srav drop partition pt_1;
dropping partition deletes data as well.. So if you should use merge to combine the partitions to a different name..
To partition exisitng table we can use dbms_redefinition package
create a new table with partitions and use the package to transfer contents
or
best method is to exchange partition which can be seen in oraclebase website in the following link
http://oracle-base.com/articles/misc/partitioning-an-existing-table-using-exchange-partition.php
Splitting partition
ALTER TABLE HMA_TM_PROD_GEN2_MONITOR.ZZT_SERVICE_LOG
SPLIT PARTITION ZZT_SERVICE_LOG_2015MAX AT
('20160201000000')
INTO (PARTITION ZZT_SERVICE_LOG_2016JAN
TABLESPACE HMA_TM_PROD_GEN2_MONITOR_DATA
PCTFREE 10
INITRANS 1
MAXTRANS 255
STORAGE (
MAXSIZE UNLIMITED
BUFFER_POOL DEFAULT
FLASH_CACHE DEFAULT
CELL_FLASH_CACHE DEFAULT
),
PARTITION ZZT_SERVICE_LOG_2015MAX)
UPDATE GLOBAL INDEXES;
Renaming Partition
ALTER TABLE HMA_TM_PROD_GEN2_MONITOR.ZZT_SERVICE_LOG
RENAME PARTITION ZZT_SERVICE_LOG_2015MAX
TO ZZT_SERVICE_LOG_2016MAX;
Better use toad to split partition.
Select the table, goto partitions tab and alter it . Select partition you want to alter.. click on split and give name and new upper bound values
SPLITTING PARTION SCRIPT RUN EVERY YEAR
(CHANGE YEARS in the script everytime you run) Also rename max depending on your scenarioALTER TABLE HMA_TM_PROD_GEN2_MONITOR.ZZT_SERVICE_LOG
SPLIT PARTITION ZZT_SERVICE_LOG_2016MAX AT
('20160201000000')
INTO (PARTITION ZZT_SERVICE_LOG_2016JAN
TABLESPACE HMA_TM_PROD_GEN2_MONITOR_DATA
PCTFREE 10
INITRANS 1
MAXTRANS 255
STORAGE (
MAXSIZE UNLIMITED
BUFFER_POOL DEFAULT
FLASH_CACHE DEFAULT
CELL_FLASH_CACHE DEFAULT
),
PARTITION ZZT_SERVICE_LOG_2016MAX)
UPDATE GLOBAL INDEXES;
ALTER TABLE HMA_TM_PROD_GEN2_MONITOR.ZZT_SERVICE_LOG
SPLIT PARTITION ZZT_SERVICE_LOG_2016MAX AT
('20160301000000')
INTO (PARTITION ZZT_SERVICE_LOG_2016FEB
TABLESPACE HMA_TM_PROD_GEN2_MONITOR_DATA
PCTFREE 10
INITRANS 1
MAXTRANS 255
STORAGE (
MAXSIZE UNLIMITED
BUFFER_POOL DEFAULT
FLASH_CACHE DEFAULT
CELL_FLASH_CACHE DEFAULT
),
PARTITION ZZT_SERVICE_LOG_2016MAX)
UPDATE GLOBAL INDEXES;
ALTER TABLE HMA_TM_PROD_GEN2_MONITOR.ZZT_SERVICE_LOG
SPLIT PARTITION ZZT_SERVICE_LOG_2016MAX AT
('20160401000000')
INTO (PARTITION ZZT_SERVICE_LOG_2016MAR
TABLESPACE HMA_TM_PROD_GEN2_MONITOR_DATA
PCTFREE 10
INITRANS 1
MAXTRANS 255
STORAGE (
MAXSIZE UNLIMITED
BUFFER_POOL DEFAULT
FLASH_CACHE DEFAULT
CELL_FLASH_CACHE DEFAULT
),
PARTITION ZZT_SERVICE_LOG_2016MAX)
UPDATE GLOBAL INDEXES;
ALTER TABLE HMA_TM_PROD_GEN2_MONITOR.ZZT_SERVICE_LOG
SPLIT PARTITION ZZT_SERVICE_LOG_2016MAX AT
('20160501000000')
INTO (PARTITION ZZT_SERVICE_LOG_2016APR
TABLESPACE HMA_TM_PROD_GEN2_MONITOR_DATA
PCTFREE 10
INITRANS 1
MAXTRANS 255
STORAGE (
MAXSIZE UNLIMITED
BUFFER_POOL DEFAULT
FLASH_CACHE DEFAULT
CELL_FLASH_CACHE DEFAULT
),
PARTITION ZZT_SERVICE_LOG_2016MAX)
UPDATE GLOBAL INDEXES;
ALTER TABLE HMA_TM_PROD_GEN2_MONITOR.ZZT_SERVICE_LOG
SPLIT PARTITION ZZT_SERVICE_LOG_2016MAX AT
('20160601000000')
INTO (PARTITION ZZT_SERVICE_LOG_2016MAY
TABLESPACE HMA_TM_PROD_GEN2_MONITOR_DATA
PCTFREE 10
INITRANS 1
MAXTRANS 255
STORAGE (
MAXSIZE UNLIMITED
BUFFER_POOL DEFAULT
FLASH_CACHE DEFAULT
CELL_FLASH_CACHE DEFAULT
),
PARTITION ZZT_SERVICE_LOG_2016MAX)
UPDATE GLOBAL INDEXES;
ALTER TABLE HMA_TM_PROD_GEN2_MONITOR.ZZT_SERVICE_LOG
SPLIT PARTITION ZZT_SERVICE_LOG_2016MAX AT
('20160701000000')
INTO (PARTITION ZZT_SERVICE_LOG_2016JUN
TABLESPACE HMA_TM_PROD_GEN2_MONITOR_DATA
PCTFREE 10
INITRANS 1
MAXTRANS 255
STORAGE (
MAXSIZE UNLIMITED
BUFFER_POOL DEFAULT
FLASH_CACHE DEFAULT
CELL_FLASH_CACHE DEFAULT
),
PARTITION ZZT_SERVICE_LOG_2016MAX)
UPDATE GLOBAL INDEXES;
ALTER TABLE HMA_TM_PROD_GEN2_MONITOR.ZZT_SERVICE_LOG
SPLIT PARTITION ZZT_SERVICE_LOG_2016MAX AT
('20160801000000')
INTO (PARTITION ZZT_SERVICE_LOG_2016JUL
TABLESPACE HMA_TM_PROD_GEN2_MONITOR_DATA
PCTFREE 10
INITRANS 1
MAXTRANS 255
STORAGE (
MAXSIZE UNLIMITED
BUFFER_POOL DEFAULT
FLASH_CACHE DEFAULT
CELL_FLASH_CACHE DEFAULT
),
PARTITION ZZT_SERVICE_LOG_2016MAX)
UPDATE GLOBAL INDEXES;
ALTER TABLE HMA_TM_PROD_GEN2_MONITOR.ZZT_SERVICE_LOG
SPLIT PARTITION ZZT_SERVICE_LOG_2016MAX AT
('20160901000000')
INTO (PARTITION ZZT_SERVICE_LOG_2016AUG
TABLESPACE HMA_TM_PROD_GEN2_MONITOR_DATA
PCTFREE 10
INITRANS 1
MAXTRANS 255
STORAGE (
MAXSIZE UNLIMITED
BUFFER_POOL DEFAULT
FLASH_CACHE DEFAULT
CELL_FLASH_CACHE DEFAULT
),
PARTITION ZZT_SERVICE_LOG_2016MAX)
UPDATE GLOBAL INDEXES;
ALTER TABLE HMA_TM_PROD_GEN2_MONITOR.ZZT_SERVICE_LOG
SPLIT PARTITION ZZT_SERVICE_LOG_2016MAX AT
('20161001000000')
INTO (PARTITION ZZT_SERVICE_LOG_2016SEP
TABLESPACE HMA_TM_PROD_GEN2_MONITOR_DATA
PCTFREE 10
INITRANS 1
MAXTRANS 255
STORAGE (
MAXSIZE UNLIMITED
BUFFER_POOL DEFAULT
FLASH_CACHE DEFAULT
CELL_FLASH_CACHE DEFAULT
),
PARTITION ZZT_SERVICE_LOG_2016MAX)
UPDATE GLOBAL INDEXES;
ALTER TABLE HMA_TM_PROD_GEN2_MONITOR.ZZT_SERVICE_LOG
SPLIT PARTITION ZZT_SERVICE_LOG_2016MAX AT
('20161101000000')
INTO (PARTITION ZZT_SERVICE_LOG_2016OCT
TABLESPACE HMA_TM_PROD_GEN2_MONITOR_DATA
PCTFREE 10
INITRANS 1
MAXTRANS 255
STORAGE (
MAXSIZE UNLIMITED
BUFFER_POOL DEFAULT
FLASH_CACHE DEFAULT
CELL_FLASH_CACHE DEFAULT
),
PARTITION ZZT_SERVICE_LOG_2016MAX)
UPDATE GLOBAL INDEXES;
ALTER TABLE HMA_TM_PROD_GEN2_MONITOR.ZZT_SERVICE_LOG
SPLIT PARTITION ZZT_SERVICE_LOG_2016MAX AT
('20161201000000')
INTO (PARTITION ZZT_SERVICE_LOG_2016NOV
TABLESPACE HMA_TM_PROD_GEN2_MONITOR_DATA
PCTFREE 10
INITRANS 1
MAXTRANS 255
STORAGE (
MAXSIZE UNLIMITED
BUFFER_POOL DEFAULT
FLASH_CACHE DEFAULT
CELL_FLASH_CACHE DEFAULT
),
PARTITION ZZT_SERVICE_LOG_2016MAX)
UPDATE GLOBAL INDEXES;
ALTER TABLE HMA_TM_PROD_GEN2_MONITOR.ZZT_SERVICE_LOG
SPLIT PARTITION ZZT_SERVICE_LOG_2016MAX AT
('20170101000000')
INTO (PARTITION ZZT_SERVICE_LOG_2016DEC
TABLESPACE HMA_TM_PROD_GEN2_MONITOR_DATA
PCTFREE 10
INITRANS 1
MAXTRANS 255
STORAGE (
MAXSIZE UNLIMITED
BUFFER_POOL DEFAULT
FLASH_CACHE DEFAULT
CELL_FLASH_CACHE DEFAULT
),
PARTITION ZZT_SERVICE_LOG_2016MAX)
UPDATE GLOBAL INDEXES;
Once completed check indexes to see if anything is invalidated
select index_name,status from dba_indexes where table_name='ZZT_SERVICE_LOG';
No comments:
Post a Comment