Create new Partitions on a Partitioned Table by Example. Indexes on a Partitioned Table

General
There is a Weekly Partitioned Table.
Each month, a new tablespace, dedicated only for this table, is created, and the old tablespace is dropped.
There is one tablespace for the table, and another for the indexes.
After the new tablespace is added to the table, global indexes are updated.
The last part, is to create new weekly partitions.

Steps
Example of a Monthly activity on a Partitioned Table:
- Drop old Partitions.
- Drop old Partitions Tablespace.
- Drop old Tablespace Datafile.
- Create new Datafile and New Tablespace.
- Alter Global Indexes with Storage Clause referencing new Tablespace.
- Add new Partitions with Storage Clause referencing new Tablespace.

Code Example
------ Start DB1 --------------
conn user1/pass1@DB1
ALTER TABLE MY_TABLE DROP PARTITION P_20140811_20140817 UPDATE GLOBAL INDEXES;
ALTER TABLE MY_TABLE DROP PARTITION P_20140818_20140824 UPDATE GLOBAL INDEXES;
ALTER TABLE MY_TABLE DROP PARTITION P_20140825_20140831 UPDATE GLOBAL INDEXES;
ALTER TABLE MY_TABLE DROP PARTITION P_20140901_20140907 UPDATE GLOBAL INDEXES;

ALTER DATABASE DATAFILE 'C:\ORACLE_DB\DB1\MY_INST\MY_TABLE_TB_145_01.DBF' RESIZE 10M;
ALTER DATABASE DATAFILE 'C:\ORACLE_DB\DB1\MY_INST\MY_TABLE_IX_145_01.DBF' RESIZE 10M;

DROP TABLESPACE TBS_MY_TABLE_TB_145 INCLUDING CONTENTS AND DATAFILES;
DROP TABLESPACE TBS_MY_TABLE_IX_145 INCLUDING CONTENTS AND DATAFILES;

CREATE TABLESPACE TBS_MY_TABLE_TB_147 LOGGING DATAFILE 'C:\ORACLE_DB\DB1\MY_INST\MY_TABLE_TB_147_01.DBF' SIZE 8000M AUTOEXTEND ON NEXT 500M MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO ;

CREATE TABLESPACE TBS_MY_TABLE_IX_147 LOGGING DATAFILE 'C:\ORACLE_DB\DB1\MY_INST\MY_TABLE_IX_147_01.DBF' SIZE 6000M AUTOEXTEND ON NEXT 500M MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT  AUTO ;

ALTER INDEX GLOBAL_INDEX_IX MODIFY DEFAULT ATTRIBUTES PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE (INITIAL 200m) TABLESPACE TBS_MY_TABLE_IX_147;
ALTER INDEX GLOBAL_BITMAP_BMIDX MODIFY DEFAULT ATTRIBUTES PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE (INITIAL 80m) TABLESPACE TBS_MY_TABLE_IX_147;

ALTER TABLE MY_TABLE ADD PARTITION P_20141006_20141012 VALUES LESS THAN ('20141013')TABLESPACE TBS_MY_TABLE_TB_147 PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 STORAGE (INITIAL 1000M);
ALTER TABLE MY_TABLE ADD PARTITION P_20141013_20141019 VALUES LESS THAN ('20141020')TABLESPACE TBS_MY_TABLE_TB_147 PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 STORAGE (INITIAL 1000M);
ALTER TABLE MY_TABLE ADD PARTITION P_20141020_20141026 VALUES LESS THAN ('20141027')TABLESPACE TBS_MY_TABLE_TB_147 PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 STORAGE (INITIAL 1000M);
ALTER TABLE MY_TABLE ADD PARTITION P_20141027_20141102 VALUES LESS THAN ('20141103')TABLESPACE TBS_MY_TABLE_TB_147 PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 STORAGE (INITIAL 1000M);

EXIT;
/


Comments

A. ALTER DATABASE DATAFILE 'C:\ORACLE_DB\DB1\MY_INST\MY_TABLE_TB_145_01.DBF' RESIZE 10M;
Per design, the datafile should hold only one tablespace.
However this is good practice, to resize datafile before dropping it, since if the datafile might have  another tablespace, this step would fail, thus we do not lose data.

B. ALTER INDEX XXX_IX MODIFY DEFAULT ATTRIBUTES TABLESPACE TBS_MY_TABLE_IX_147;

You cannot explicitly add a partition to a local index. 

A new partition is added to a local index only when you add a partition to the underlying table. 
When there is a local index defined on a table and you issue the ALTER TABLE ADD PARTITION, a matching partition is also added to the local index. 

You can effectively specify a new tablespace for an index partition by modifying the default attributes for the index.

In this example, before adding the new partitions, the statement "" was issued, so that the  corresponding index partitions would be created in tablespace TBS_MY_TABLE_IX_147.



Index on partitioned table
When creating a LOCAL Index on a partitioned table, by default the index is created on the table tablespace.
In order for the index to be created on the index tablespace, need to explicitly specify tablespace clause.

CREATE INDEX COUNTER_DATA_A11 on COUNTER_DATA (CUSTOMER_ID, DATE_OF_CALL)  LOCAL;
CREATE INDEX COUNTER_DATA_A12 on COUNTER_DATA (CUSTOMER_ID, COUNTER_ID) LOCAL;

SELECT segment_name, partition_name, segment_type, tablespace_name
FROM USER_SEGMENTS 
WHERE TABLESPACE_NAME LIKE 'COUNTER_%'
ORDER BY SEGMENT_TYPE, SEGMENT_NAME,PARTITION_NAME;

Notice that the segment type is Index, but tablespace name if the one from the table.

SEGMENT_NAME                   PARTITION_NAME  SEGMENT_TYPE    TABLESPACE_NAME
------------------------------ --------------- --------------- --------------------
EU_COUNTER_DATA_A11            P_01_06         INDEX PARTITION COLLECT_XEU_TABLE
EU_COUNTER_DATA_A11            P_07_12         INDEX PARTITION COLLECT_XEU_TABLE
EU_COUNTER_DATA_A11            P_13_18         INDEX PARTITION COLLECT_XEU_TABLE
EU_COUNTER_DATA_A11            P_19_24         INDEX PARTITION COLLECT_XEU_TABLE
EU_COUNTER_DATA_A11            P_25_31         INDEX PARTITION COLLECT_XEU_TABLE
EU_COUNTER_DATA_A12            P_01_06         INDEX PARTITION COLLECT_XEU_TABLE
EU_COUNTER_DATA_A12            P_07_12         INDEX PARTITION COLLECT_XEU_TABLE
EU_COUNTER_DATA_A12            P_13_18         INDEX PARTITION COLLECT_XEU_TABLE
EU_COUNTER_DATA_A12            P_19_24         INDEX PARTITION COLLECT_XEU_TABLE
EU_COUNTER_DATA_A12            P_25_31         INDEX PARTITION COLLECT_XEU_TABLE
EU_COUNTER_DATA                P_01_06         TABLE PARTITION COLLECT_XEU_TABLE
EU_COUNTER_DATA                P_07_12         TABLE PARTITION COLLECT_XEU_TABLE
EU_COUNTER_DATA                P_13_18         TABLE PARTITION COLLECT_XEU_TABLE
EU_COUNTER_DATA                P_19_24         TABLE PARTITION COLLECT_XEU_TABLE
EU_COUNTER_DATA                P_25_31         TABLE PARTITION COLLECT_XEU_TABLE

DROP INDEX COUNTER_DATA_A11;
DROP INDEX COUNTER_DATA_A12;

CREATE INDEX COUNTER_DATA_A11 on COUNTER_DATA (CUSTOMER_ID, DATE_OF_CALL)
  LOCAL TABLESPACE COUNTER_INDEX_TBS;
CREATE INDEX COUNTER_DATA_A12 on COUNTER_DATA (CUSTOMER_ID, COUNTER_ID)
  LOCAL TABLESPACE COUNTER_INDEX_TBS;

Now index is stored on a dedicated tablespace.

SELECT segment_name, partition_name, segment_type, tablespace_name
FROM USER_SEGMENTS 
WHERE TABLESPACE_NAME LIKE 'COUNTER_%'
ORDER BY SEGMENT_TYPE, SEGMENT_NAME,PARTITION_NAME;

SEGMENT_NAME                   PARTITION_NAME  SEGMENT_TYPE    TABLESPACE_NAME
------------------------------ --------------- --------------- --------------------
EU_COUNTER_DATA_A11            P_01_06         INDEX PARTITION COUNTER_XEU_INDEX
EU_COUNTER_DATA_A11            P_07_12         INDEX PARTITION COUNTER_XEU_INDEX
EU_COUNTER_DATA_A11            P_13_18         INDEX PARTITION COUNTER_XEU_INDEX
EU_COUNTER_DATA_A11            P_19_24         INDEX PARTITION COUNTER_XEU_INDEX
EU_COUNTER_DATA_A11            P_25_31         INDEX PARTITION COUNTER_XEU_INDEX
EU_COUNTER_DATA_A12            P_01_06         INDEX PARTITION COUNTER_XEU_INDEX
EU_COUNTER_DATA_A12            P_07_12         INDEX PARTITION COUNTER_XEU_INDEX
EU_COUNTER_DATA_A12            P_13_18         INDEX PARTITION COUNTER_XEU_INDEX
EU_COUNTER_DATA_A12            P_19_24         INDEX PARTITION COUNTER_XEU_INDEX
EU_COUNTER_DATA_A12            P_25_31         INDEX PARTITION COUNTER_XEU_INDEX
EU_COUNTER_DATA                P_01_06         TABLE PARTITION COLLECT_XEU_TABLE
EU_COUNTER_DATA                P_07_12         TABLE PARTITION COLLECT_XEU_TABLE
EU_COUNTER_DATA                P_13_18         TABLE PARTITION COLLECT_XEU_TABLE
EU_COUNTER_DATA                P_19_24         TABLE PARTITION COLLECT_XEU_TABLE
EU_COUNTER_DATA                P_25_31         TABLE PARTITION COLLECT_XEU_TABLE

Query Index status on a Partitioned Table
Normally, to get the index status need to run this SQL:
SELECT status, DBA_INDEXES.* 
FROM DBA_INDEXES 
WHERE status <> 'VALID'

For partitioned indexes, the value in status would be 'N/A'
To get the index status, need to query the index status per each partition:

SELECT status, DBA_IND_PARTITIONS.* 
FROM DBA_IND_PARTITIONS 
WHERE status <> 'USABLE'