Thursday, September 15, 2011

MANAGING PARTITIONS

CREATING PARTITIONS

CREATE TABLESPACE smpp_log5 DATAFILE ‘E:\ORACLE\PRODUCT\10.2.0\ORADATA\VMS\ smpp_log5.DBF’ SIZE 10G AUTOEXTEND ON NEXT 512M EXTENT MANAGEMENT LOCAL;

CREATE TABLE TBL_SMPP_LOGS_BACKUP1
( "DATE_TIME" DATE,
"ANI" VARCHAR2(15),
"DNIS" VARCHAR2(20),
"MESSAGE" VARCHAR2(160),
"PROMO_TYPE" VARCHAR2(10),
"RESPONSE" VARCHAR2(20),
"CIRCLE_ID" NUMBER(*,0))
PARTITION BY RANGE(date_time)
(
PARTITION P1 VALUES LESS THAN(TO_DATE('04/03/2009','DD/MM/YYYY')) TABLESPACE SMPP_LOGS1,
PARTITION P2 VALUES LESS THAN(TO_DATE('06/03/2009','DD/MM/YYYY')) TABLESPACE SMPP_LOG2,
PARTITION P3 VALUES LESS THAN(TO_DATE('08/03/2009','DD/MM/YYYY')) TABLESPACE SMPP_LOG3,
PARTITION P4 VALUES LESS THAN(TO_DATE('10/03/2009','DD/MM/YYYY')) TABLESPACE SMPP_LOG4
);

CREATE INDEX DATE1_idx ON TBL_SMPP_LOGS_BACKUP1(date_time) LOCAL
(PARTITION P1 TABLESPACE SMPP_LOGS1,
PARTITION P2 TABLESPACE SMPP_LOG2,
PARTITION P3 TABLESPACE SMPP_LOG3,
PARTITION P4 TABLESPACE SMPP_LOG4);

CREATE TABLE TBL_SMPP_LOGS1_BACKUP1
( "DATE_TIME" TIMESTAMP (6),
"ANI" VARCHAR2(15),
"DNIS" VARCHAR2(10),
"MESSAGE" VARCHAR2(160),
"PROMO_TYPE" VARCHAR2(10),
"RESPONSE" VARCHAR2(20))
PARTITION BY RANGE(date_time)
(
PARTITION P1 VALUES LESS THAN(TO_DATE('04/03/2009','DD/MM/YYYY')) TABLESPACE SMPP_LOGS1,
PARTITION P2 VALUES LESS THAN(TO_DATE('06/03/2009','DD/MM/YYYY')) TABLESPACE SMPP_LOG2,
PARTITION P3 VALUES LESS THAN(TO_DATE('08/03/2009','DD/MM/YYYY')) TABLESPACE SMPP_LOG3,
PARTITION P4 VALUES LESS THAN(TO_DATE('10/03/2009','DD/MM/YYYY')) TABLESPACE SMPP_LOG4
);

ADDING PARTITIONS
SQL> CREATE TABLESPACE smpp_log6 DATAFILE 'E:\ORACLE\PRODUCT\10.2.0\ORADATA\VMS\ smpp_log6.DBF' SIZE 10G AUTOEXTEND ON NEXT 512M EXTENT MANAGEMENT LOCAL;
Tablespace created.
SQL> ALTER TABLE TBL_SMPP_LOGS1_BACKUP1 ADD PARTITION P6 VALUES LESS THAN (TO_DATE('17/03/2009','DD/MM/YYYY')) TABLESPACE smpp_log6;
Table altered.
SQL> ALTER TABLE TBL_SMPP_LOGS_BACKUP1 ADD PARTITION P6 VALUES LESS THAN (TO_DATE('17/03/2009','DD/MM/YYYY')) TABLESPACE smpp_log6;
Table altered.

SQL> SELECT INDEX_NAME,PARTITION_NAME,STATUS,TABLESPACE_NAME,NUM_ROWS FROM DBA_IND_PARTITIONS WHERE INDEX_NAME='DATE2_IDX';
ALTER INDEX DATE2_IDX REBUILD PARTITION P5
TABLESPACE SMPP_LOG5;
SQL> ALTER INDEX DATE2_IDX REBUILD PARTITION P5
2 TABLESPACE SMPP_LOG5;
Index altered.
SQL> ALTER INDEX DATE2_IDX REBUILD PARTITION P3 TABLESPACE SMPP_LOG3;
Index altered.
SQL> ALTER INDEX DATE2_IDX REBUILD PARTITION P2 TABLESPACE SMPP_LOG2;
Index altered.
SQL> ALTER INDEX DATE2_IDX REBUILD PARTITION P1 TABLESPACE SMPP_LOGS1;
Index altered.
SQL> SELECT TABLE_NAME,PARTITION_NAME,TABLESPACE_NAME,NUM_ROWS,BLOCKS, LAST_ANALYZED FROM DBA_TAB_PARTITIONS WHERE TABLE_NAME='TBL_SMPP_LOGS1_BACKUP1';


CREATE TABLESPACE smpp_log5 DATAFILE ‘E:\ORACLE\PRODUCT\10.2.0\ORADATA\VMS\ smpp_log5.DBF’ SIZE 10G AUTOEXTEND ON NEXT 512M EXTENT MANAGEMENT LOCAL;
ALTER TABLE TBL_SMPP_LOGS1_BACKUP1 ADD PARTITION P5 VALUES LESS THAN (TO_DATE('13/03/2009','DD/MM/YYYY')) TABLESPACE smpp_log5;
***********************************
CREATE INDEX DATE3_idx ON TBL_SMPP_LOGS1_BACKUP1(date_time) LOCAL
(PARTITION P1 TABLESPACE SMPP_LOGS1,
PARTITION P2 TABLESPACE SMPP_LOG2,
PARTITION P3 TABLESPACE SMPP_LOG3,
PARTITION P4 TABLESPACE SMPP_LOG4);

No comments:

Post a Comment