SQL> SQL> drop table history purge; Table dropped. SQL> SQL> CREATE TABLE history 2 ( 3 hist_date DATE DEFAULT SYSDATE NOT NULL, 4 hist_type varchar2(1) default 'K' CONSTRAINT ck_hist_type CHECK (hist_type in('K', 'D')) not null, 5 hist_message varchar2(128) 6 ) 7 PARTITION BY RANGE (hist_date) 8 SUBPARTITION BY LIST (hist_type) 9 ( 10 PARTITION P201201 VALUES LESS THAN (TO_DATE(' 2012-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS')) 11 ( SUBPARTITION SP201201_K VALUES ('K') 12 , SUBPARTITION SP201201_D VALUES ('D') 13 ), 14 PARTITION P201202 VALUES LESS THAN (TO_DATE(' 2012-03-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS')) 15 ( SUBPARTITION SP201202_K VALUES ('K') 16 , SUBPARTITION SP201202_D VALUES ('D') 17 ), 18 PARTITION P201203 VALUES LESS THAN (TO_DATE(' 2012-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS')) 19 ( SUBPARTITION SP201203_K VALUES ('K') 20 , SUBPARTITION SP201203_D VALUES ('D') 21 ), 22 PARTITION P201204 VALUES LESS THAN (TO_DATE(' 2012-05-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS')) 23 ( SUBPARTITION SP201204_K VALUES ('K') 24 , SUBPARTITION SP201204_D VALUES ('D') 25 ), 26 PARTITION P201205 VALUES LESS THAN (TO_DATE(' 2012-06-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS')) 27 ( SUBPARTITION SP201205_K VALUES ('K') 28 , SUBPARTITION SP201205_D VALUES ('D') 29 ), 30 PARTITION P201206 VALUES LESS THAN (TO_DATE(' 2012-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS')) 31 ( SUBPARTITION SP201206_K VALUES ('K') 32 , SUBPARTITION SP201206_D VALUES ('D') 33 ), 34 PARTITION P201207 VALUES LESS THAN (TO_DATE(' 2012-08-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS')) 35 ( SUBPARTITION SP201207_K VALUES ('K') 36 , SUBPARTITION SP201207_D VALUES ('D') 37 ), 38 PARTITION P201208 VALUES LESS THAN (TO_DATE(' 2012-09-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS')) 39 ( SUBPARTITION SP201208_K VALUES ('K') 40 , SUBPARTITION SP201208_D VALUES ('D') 41 ), 42 PARTITION P201209 VALUES LESS THAN (TO_DATE(' 2012-10-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS')) 43 ( SUBPARTITION SP201209_K VALUES ('K') 44 , SUBPARTITION SP201209_D VALUES ('D') 45 ), 46 PARTITION P201210 VALUES LESS THAN (TO_DATE(' 2012-11-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS')) 47 ( SUBPARTITION SP201210_K VALUES ('K') 48 , SUBPARTITION SP201210_D VALUES ('D') 49 ), 50 PARTITION P201211 VALUES LESS THAN (TO_DATE(' 2012-12-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS')) 51 ( SUBPARTITION SP201211_K VALUES ('K') 52 , SUBPARTITION SP201211_D VALUES ('D') 53 ), 54 PARTITION P201212 VALUES LESS THAN (TO_DATE(' 2013-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS')) 55 ( SUBPARTITION SP201212_K VALUES ('K') 56 , SUBPARTITION SP201212_D VALUES ('D') 57 ), 58 PARTITION P_MAX VALUES LESS THAN (MAXVALUE) 59 ( SUBPARTITION SPMAX_K VALUES ('K') 60 , SUBPARTITION SPMAX_D VALUES ('D') 61 ) 62 ) 63 ENABLE ROW MOVEMENT 64 COMPRESS FOR ALL OPERATIONS; Table created. SQL> SQL> insert /*+ APPEND */ into HISTORY 2 select TO_DATE('01.01.2012', 'DD.MM.RRRR') + dbms_random.value(1,365) as hist_date, 3 case when mod(rownum, 5) = 0 then 'D' 4 else 'K' 5 end as hist_type, 6 dbms_random.string('A', 50) as hist_message 7 from dual connect by level <=500; 500 rows created. SQL> SQL> commit; Commit complete. SQL> SQL> insert into HISTORY 2 values (to_date('01.01.2013', 'DD.MM.YYYY'), 'K', dbms_random.string('A', 50)); 1 row created. SQL> SQL> commit; Commit complete. SQL> SQL> col partition_name for a10 SQL> col tblspc for a10 SQL> select partition_position as p#, partition_name, subpartition_count as subcnt, high_value, tablespace_name as tblspc 2 from user_tab_partitions 3 where table_name = 'HISTORY' 4 order by partition_position; P# PARTITION_ SUBCNT HIGH_VALUE TBLSPC ---------- ---------- ---------- -------------------------------------------------------------------------------- ---------- 1 P201201 2 TO_DATE(' 2012-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA USERS 2 P201202 2 TO_DATE(' 2012-03-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA USERS 3 P201203 2 TO_DATE(' 2012-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA USERS 4 P201204 2 TO_DATE(' 2012-05-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA USERS 5 P201205 2 TO_DATE(' 2012-06-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA USERS 6 P201206 2 TO_DATE(' 2012-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA USERS 7 P201207 2 TO_DATE(' 2012-08-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA USERS 8 P201208 2 TO_DATE(' 2012-09-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA USERS 9 P201209 2 TO_DATE(' 2012-10-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA USERS 10 P201210 2 TO_DATE(' 2012-11-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA USERS 11 P201211 2 TO_DATE(' 2012-12-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA USERS 12 P201212 2 TO_DATE(' 2013-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA USERS 13 P_MAX 2 MAXVALUE USERS 13 rows selected. SQL> SQL> col partition_name for a10 SQL> col hv for a5 SQL> col tblspc for a10 SQL> select subpartition_position as sub#, partition_name, subpartition_name, high_value as hv, tablespace_name as tblspc 2 from user_tab_subpartitions 3 where table_name = 'HISTORY' 4 order by partition_name, subpartition_position; SUB# PARTITION_ SUBPARTITION_NAME HV TBLSPC ---------- ---------- ------------------------------ ----- ---------- 1 P201201 SP201201_K 'K' USERS 2 P201201 SP201201_D 'D' USERS 1 P201202 SP201202_K 'K' USERS 2 P201202 SP201202_D 'D' USERS 1 P201203 SP201203_K 'K' USERS 2 P201203 SP201203_D 'D' USERS 1 P201204 SP201204_K 'K' USERS 2 P201204 SP201204_D 'D' USERS 1 P201205 SP201205_K 'K' USERS 2 P201205 SP201205_D 'D' USERS 1 P201206 SP201206_K 'K' USERS 2 P201206 SP201206_D 'D' USERS 1 P201207 SP201207_K 'K' USERS 2 P201207 SP201207_D 'D' USERS 1 P201208 SP201208_K 'K' USERS 2 P201208 SP201208_D 'D' USERS 1 P201209 SP201209_K 'K' USERS 2 P201209 SP201209_D 'D' USERS 1 P201210 SP201210_K 'K' USERS 2 P201210 SP201210_D 'D' USERS 1 P201211 SP201211_K 'K' USERS 2 P201211 SP201211_D 'D' USERS 1 P201212 SP201212_K 'K' USERS 2 P201212 SP201212_D 'D' USERS 1 P_MAX SPMAX_K 'K' USERS 2 P_MAX SPMAX_D 'D' USERS 26 rows selected. SQL> SQL> alter table HISTORY 2 SET INTERVAL (NUMTOYMINTERVAL(1,'MONTH')); alter table HISTORY * ERROR at line 1: ORA-14759: SET INTERVAL is not legal on this table. SQL> SQL> select TO_CHAR(TRUNC(add_months(max(hist_date), +1), 'MONTH'), 'RRRR-MM-DD HH24:MI:SS') 2 as split_at 3 from HISTORY partition (p_max); SPLIT_AT ------------------- 2013-02-01 00:00:00 SQL> SQL> ALTER TABLE HISTORY SPLIT PARTITION P_MAX 2 AT (TIMESTAMP'2013-02-01 00:00:00') INTO (PARTITION P201301, PARTITION P_MAX); Table altered. SQL> SQL> ALTER TABLE HISTORY drop PARTITION p_max; Table altered. SQL> SQL> alter table HISTORY 2 SET INTERVAL (NUMTOYMINTERVAL(1,'MONTH')); Table altered. SQL> SQL> alter table HISTORY 2 set SUBPARTITION TEMPLATE 3 (SUBPARTITION SP_K VALUES ('K'), 4 SUBPARTITION SP_D VALUES ('D') 5 ); Table altered. SQL> SQL> alter table HISTORY set STORE IN (TBLSPC_01, TBLSPC_02, TBLSPC_03, TBLSPC_04, 2 TBLSPC_05, TBLSPC_06, TBLSPC_07, TBLSPC_08, 3 TBLSPC_09, TBLSPC_10, TBLSPC_11, TBLSPC_12); Table altered. SQL> SQL> insert into HISTORY 2 values(TO_DATE('15.01.2013', 'DD.MM.RRRR'), 'K', dbms_random.string('A', 50)); 1 row created. SQL> SQL> insert into HISTORY 2 values(TO_DATE('15.02.2013', 'DD.MM.RRRR'), 'D', dbms_random.string('A', 50)); 1 row created. SQL> SQL> commit; Commit complete. SQL> SQL> col partition_name for a10 SQL> col tblspc for a10 SQL> select partition_position as p#, partition_name, subpartition_count as subcnt, high_value, tablespace_name as tblspc 2 from user_tab_partitions 3 where table_name = 'HISTORY' 4 order by partition_position; P# PARTITION_ SUBCNT HIGH_VALUE TBLSPC ---------- ---------- ---------- -------------------------------------------------------------------------------- ---------- 1 P201201 2 TO_DATE(' 2012-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA USERS 2 P201202 2 TO_DATE(' 2012-03-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA USERS 3 P201203 2 TO_DATE(' 2012-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA USERS 4 P201204 2 TO_DATE(' 2012-05-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA USERS 5 P201205 2 TO_DATE(' 2012-06-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA USERS 6 P201206 2 TO_DATE(' 2012-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA USERS 7 P201207 2 TO_DATE(' 2012-08-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA USERS 8 P201208 2 TO_DATE(' 2012-09-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA USERS 9 P201209 2 TO_DATE(' 2012-10-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA USERS 10 P201210 2 TO_DATE(' 2012-11-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA USERS 11 P201211 2 TO_DATE(' 2012-12-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA USERS 12 P201212 2 TO_DATE(' 2013-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA USERS 13 P201301 2 TO_DATE(' 2013-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA USERS 14 SYS_P923 2 TO_DATE(' 2013-03-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA TBLSPC_02 14 rows selected. SQL> SQL> col partition_name for a10 SQL> col hv for a5 SQL> col tblspc for a10 SQL> select subpartition_position as sub#, partition_name, subpartition_name, high_value as hv, tablespace_name as tblspc 2 from user_tab_subpartitions 3 where table_name = 'HISTORY' 4 order by partition_name, subpartition_position; SUB# PARTITION_ SUBPARTITION_NAME HV TBLSPC ---------- ---------- ------------------------------ ----- ---------- 1 P201201 SP201201_K 'K' USERS 2 P201201 SP201201_D 'D' USERS 1 P201202 SP201202_K 'K' USERS 2 P201202 SP201202_D 'D' USERS 1 P201203 SP201203_K 'K' USERS 2 P201203 SP201203_D 'D' USERS 1 P201204 SP201204_K 'K' USERS 2 P201204 SP201204_D 'D' USERS 1 P201205 SP201205_K 'K' USERS 2 P201205 SP201205_D 'D' USERS 1 P201206 SP201206_K 'K' USERS 2 P201206 SP201206_D 'D' USERS 1 P201207 SP201207_K 'K' USERS 2 P201207 SP201207_D 'D' USERS 1 P201208 SP201208_K 'K' USERS 2 P201208 SP201208_D 'D' USERS 1 P201209 SP201209_K 'K' USERS 2 P201209 SP201209_D 'D' USERS 1 P201210 SP201210_K 'K' USERS 2 P201210 SP201210_D 'D' USERS 1 P201211 SP201211_K 'K' USERS 2 P201211 SP201211_D 'D' USERS 1 P201212 SP201212_K 'K' USERS 2 P201212 SP201212_D 'D' USERS 1 P201301 SYS_SUBP919 'K' USERS 2 P201301 SYS_SUBP920 'D' USERS 1 SYS_P923 SYS_SUBP921 'K' TBLSPC_02 SUB# PARTITION_ SUBPARTITION_NAME HV TBLSPC ---------- ---------- ------------------------------ ----- ---------- 2 SYS_P923 SYS_SUBP922 'D' TBLSPC_02 28 rows selected. SQL> SQL> SQL> insert into HISTORY 2 select ADD_MONTHS(TO_DATE('01.11.2012', 'DD.MM.RRRR'), LEVEL), 'K', dbms_random.string('A', 50) 3 from dual 4 connect by LEVEL <= 24; 24 rows created. SQL> SQL> commit; Commit complete. SQL> SQL> col partition_name for a10 SQL> col tblspc for a10 SQL> select partition_position as p#, partition_name, subpartition_count as subcnt, high_value, tablespace_name as tblspc 2 from user_tab_partitions 3 where table_name = 'HISTORY' 4 order by partition_position; P# PARTITION_ SUBCNT HIGH_VALUE TBLSPC ---------- ---------- ---------- -------------------------------------------------------------------------------- ---------- 1 P201201 2 TO_DATE(' 2012-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA USERS 2 P201202 2 TO_DATE(' 2012-03-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA USERS 3 P201203 2 TO_DATE(' 2012-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA USERS 4 P201204 2 TO_DATE(' 2012-05-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA USERS 5 P201205 2 TO_DATE(' 2012-06-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA USERS 6 P201206 2 TO_DATE(' 2012-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA USERS 7 P201207 2 TO_DATE(' 2012-08-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA USERS 8 P201208 2 TO_DATE(' 2012-09-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA USERS 9 P201209 2 TO_DATE(' 2012-10-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA USERS 10 P201210 2 TO_DATE(' 2012-11-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA USERS 11 P201211 2 TO_DATE(' 2012-12-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA USERS 12 P201212 2 TO_DATE(' 2013-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA USERS 13 P201301 2 TO_DATE(' 2013-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA USERS 14 SYS_P923 2 TO_DATE(' 2013-03-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA TBLSPC_02 15 SYS_P926 2 TO_DATE(' 2013-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA TBLSPC_03 16 SYS_P929 2 TO_DATE(' 2013-05-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA TBLSPC_04 17 SYS_P932 2 TO_DATE(' 2013-06-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA TBLSPC_05 18 SYS_P935 2 TO_DATE(' 2013-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA TBLSPC_06 19 SYS_P938 2 TO_DATE(' 2013-08-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA TBLSPC_07 20 SYS_P941 2 TO_DATE(' 2013-09-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA TBLSPC_08 21 SYS_P944 2 TO_DATE(' 2013-10-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA TBLSPC_09 22 SYS_P947 2 TO_DATE(' 2013-11-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA TBLSPC_10 23 SYS_P950 2 TO_DATE(' 2013-12-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA TBLSPC_11 24 SYS_P953 2 TO_DATE(' 2014-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA TBLSPC_12 25 SYS_P956 2 TO_DATE(' 2014-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA TBLSPC_01 26 SYS_P959 2 TO_DATE(' 2014-03-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA TBLSPC_02 27 SYS_P962 2 TO_DATE(' 2014-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA TBLSPC_03 P# PARTITION_ SUBCNT HIGH_VALUE TBLSPC ---------- ---------- ---------- -------------------------------------------------------------------------------- ---------- 28 SYS_P965 2 TO_DATE(' 2014-05-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA TBLSPC_04 29 SYS_P968 2 TO_DATE(' 2014-06-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA TBLSPC_05 30 SYS_P971 2 TO_DATE(' 2014-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA TBLSPC_06 31 SYS_P974 2 TO_DATE(' 2014-08-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA TBLSPC_07 32 SYS_P977 2 TO_DATE(' 2014-09-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA TBLSPC_08 33 SYS_P980 2 TO_DATE(' 2014-10-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA TBLSPC_09 34 SYS_P983 2 TO_DATE(' 2014-11-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA TBLSPC_10 35 SYS_P986 2 TO_DATE(' 2014-12-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA TBLSPC_11 35 rows selected. SQL> SQL> col partition_name for a10 SQL> col hv for a5 SQL> col tblspc for a10 SQL> select subpartition_position as sub#, partition_name, subpartition_name, high_value as hv, tablespace_name as tblspc 2 from user_tab_subpartitions 3 where table_name = 'HISTORY' 4 order by partition_name, subpartition_position; SUB# PARTITION_ SUBPARTITION_NAME HV TBLSPC ---------- ---------- ------------------------------ ----- ---------- 1 P201201 SP201201_K 'K' USERS 2 P201201 SP201201_D 'D' USERS 1 P201202 SP201202_K 'K' USERS 2 P201202 SP201202_D 'D' USERS 1 P201203 SP201203_K 'K' USERS 2 P201203 SP201203_D 'D' USERS 1 P201204 SP201204_K 'K' USERS 2 P201204 SP201204_D 'D' USERS 1 P201205 SP201205_K 'K' USERS 2 P201205 SP201205_D 'D' USERS 1 P201206 SP201206_K 'K' USERS 2 P201206 SP201206_D 'D' USERS 1 P201207 SP201207_K 'K' USERS 2 P201207 SP201207_D 'D' USERS 1 P201208 SP201208_K 'K' USERS 2 P201208 SP201208_D 'D' USERS 1 P201209 SP201209_K 'K' USERS 2 P201209 SP201209_D 'D' USERS 1 P201210 SP201210_K 'K' USERS 2 P201210 SP201210_D 'D' USERS 1 P201211 SP201211_K 'K' USERS 2 P201211 SP201211_D 'D' USERS 1 P201212 SP201212_K 'K' USERS 2 P201212 SP201212_D 'D' USERS 1 P201301 SYS_SUBP919 'K' USERS 2 P201301 SYS_SUBP920 'D' USERS 1 SYS_P923 SYS_SUBP921 'K' TBLSPC_02 SUB# PARTITION_ SUBPARTITION_NAME HV TBLSPC ---------- ---------- ------------------------------ ----- ---------- 2 SYS_P923 SYS_SUBP922 'D' TBLSPC_02 1 SYS_P926 SYS_SUBP924 'K' TBLSPC_03 2 SYS_P926 SYS_SUBP925 'D' TBLSPC_03 1 SYS_P929 SYS_SUBP927 'K' TBLSPC_04 2 SYS_P929 SYS_SUBP928 'D' TBLSPC_04 1 SYS_P932 SYS_SUBP930 'K' TBLSPC_05 2 SYS_P932 SYS_SUBP931 'D' TBLSPC_05 1 SYS_P935 SYS_SUBP933 'K' TBLSPC_06 2 SYS_P935 SYS_SUBP934 'D' TBLSPC_06 1 SYS_P938 SYS_SUBP936 'K' TBLSPC_07 2 SYS_P938 SYS_SUBP937 'D' TBLSPC_07 1 SYS_P941 SYS_SUBP939 'K' TBLSPC_08 2 SYS_P941 SYS_SUBP940 'D' TBLSPC_08 1 SYS_P944 SYS_SUBP942 'K' TBLSPC_09 2 SYS_P944 SYS_SUBP943 'D' TBLSPC_09 1 SYS_P947 SYS_SUBP945 'K' TBLSPC_10 2 SYS_P947 SYS_SUBP946 'D' TBLSPC_10 1 SYS_P950 SYS_SUBP948 'K' TBLSPC_11 2 SYS_P950 SYS_SUBP949 'D' TBLSPC_11 1 SYS_P953 SYS_SUBP951 'K' TBLSPC_12 2 SYS_P953 SYS_SUBP952 'D' TBLSPC_12 1 SYS_P956 SYS_SUBP954 'K' TBLSPC_01 2 SYS_P956 SYS_SUBP955 'D' TBLSPC_01 1 SYS_P959 SYS_SUBP957 'K' TBLSPC_02 2 SYS_P959 SYS_SUBP958 'D' TBLSPC_02 1 SYS_P962 SYS_SUBP960 'K' TBLSPC_03 2 SYS_P962 SYS_SUBP961 'D' TBLSPC_03 SUB# PARTITION_ SUBPARTITION_NAME HV TBLSPC ---------- ---------- ------------------------------ ----- ---------- 1 SYS_P965 SYS_SUBP963 'K' TBLSPC_04 2 SYS_P965 SYS_SUBP964 'D' TBLSPC_04 1 SYS_P968 SYS_SUBP966 'K' TBLSPC_05 2 SYS_P968 SYS_SUBP967 'D' TBLSPC_05 1 SYS_P971 SYS_SUBP969 'K' TBLSPC_06 2 SYS_P971 SYS_SUBP970 'D' TBLSPC_06 1 SYS_P974 SYS_SUBP972 'K' TBLSPC_07 2 SYS_P974 SYS_SUBP973 'D' TBLSPC_07 1 SYS_P977 SYS_SUBP975 'K' TBLSPC_08 2 SYS_P977 SYS_SUBP976 'D' TBLSPC_08 1 SYS_P980 SYS_SUBP978 'K' TBLSPC_09 2 SYS_P980 SYS_SUBP979 'D' TBLSPC_09 1 SYS_P983 SYS_SUBP981 'K' TBLSPC_10 2 SYS_P983 SYS_SUBP982 'D' TBLSPC_10 1 SYS_P986 SYS_SUBP984 'K' TBLSPC_11 2 SYS_P986 SYS_SUBP985 'D' TBLSPC_11 70 rows selected. SQL> SQL> alter table HISTORY drop partition for (to_date('01.01.2012', 'DD.MM.YYYY')); Table altered. SQL> SQL> alter table HISTORY drop subpartition for (to_date('01.02.2012', 'DD.MM.YYYY'), 'D'); Table altered. SQL> SQL> create index idx_hist_type on HISTORY(hist_type); Index created. SQL> SQL> SELECT index_name, status 2 from user_indexes 3 where index_name='IDX_HIST_TYPE'; INDEX_NAME STATUS ------------------------------ -------- IDX_HIST_TYPE VALID SQL> SQL> alter table HISTORY 2 drop subpartition for (to_date('01.03.2012', 'DD.MM.YYYY'), 'D') ; Table altered. SQL> SQL> SELECT index_name, status 2 from user_indexes 3 where index_name='IDX_HIST_TYPE'; INDEX_NAME STATUS ------------------------------ -------- IDX_HIST_TYPE UNUSABLE SQL> SQL> alter index idx_hist_type rebuild online; Index altered. SQL> SQL> SELECT index_name, status 2 from user_indexes 3 where index_name='IDX_HIST_TYPE'; INDEX_NAME STATUS ------------------------------ -------- IDX_HIST_TYPE VALID SQL> SQL> alter table HISTORY 2 drop subpartition for (to_date('01.04.2012', 'DD.MM.YYYY'), 'D') 3 UPDATE GLOBAL INDEXES; Table altered. SQL> SQL> SELECT index_name, status 2 from user_indexes 3 where index_name='IDX_HIST_TYPE'; INDEX_NAME STATUS ------------------------------ -------- IDX_HIST_TYPE VALID SQL> SQL> col partition_name for a10 SQL> col tblspc for a10 SQL> select partition_position as p#, partition_name, subpartition_count as subcnt, high_value, tablespace_name as tblspc 2 from user_tab_partitions 3 where table_name = 'HISTORY' 4 order by partition_position; P# PARTITION_ SUBCNT HIGH_VALUE TBLSPC ---------- ---------- ---------- -------------------------------------------------------------------------------- ---------- 1 P201202 1 TO_DATE(' 2012-03-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA USERS 2 P201203 1 TO_DATE(' 2012-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA USERS 3 P201204 1 TO_DATE(' 2012-05-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA USERS 4 P201205 2 TO_DATE(' 2012-06-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA USERS 5 P201206 2 TO_DATE(' 2012-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA USERS 6 P201207 2 TO_DATE(' 2012-08-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA USERS 7 P201208 2 TO_DATE(' 2012-09-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA USERS 8 P201209 2 TO_DATE(' 2012-10-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA USERS 9 P201210 2 TO_DATE(' 2012-11-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA USERS 10 P201211 2 TO_DATE(' 2012-12-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA USERS 11 P201212 2 TO_DATE(' 2013-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA USERS 12 P201301 2 TO_DATE(' 2013-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA USERS 13 SYS_P923 2 TO_DATE(' 2013-03-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA TBLSPC_02 14 SYS_P926 2 TO_DATE(' 2013-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA TBLSPC_03 15 SYS_P929 2 TO_DATE(' 2013-05-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA TBLSPC_04 16 SYS_P932 2 TO_DATE(' 2013-06-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA TBLSPC_05 17 SYS_P935 2 TO_DATE(' 2013-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA TBLSPC_06 18 SYS_P938 2 TO_DATE(' 2013-08-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA TBLSPC_07 19 SYS_P941 2 TO_DATE(' 2013-09-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA TBLSPC_08 20 SYS_P944 2 TO_DATE(' 2013-10-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA TBLSPC_09 21 SYS_P947 2 TO_DATE(' 2013-11-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA TBLSPC_10 22 SYS_P950 2 TO_DATE(' 2013-12-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA TBLSPC_11 23 SYS_P953 2 TO_DATE(' 2014-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA TBLSPC_12 24 SYS_P956 2 TO_DATE(' 2014-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA TBLSPC_01 25 SYS_P959 2 TO_DATE(' 2014-03-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA TBLSPC_02 26 SYS_P962 2 TO_DATE(' 2014-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA TBLSPC_03 27 SYS_P965 2 TO_DATE(' 2014-05-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA TBLSPC_04 P# PARTITION_ SUBCNT HIGH_VALUE TBLSPC ---------- ---------- ---------- -------------------------------------------------------------------------------- ---------- 28 SYS_P968 2 TO_DATE(' 2014-06-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA TBLSPC_05 29 SYS_P971 2 TO_DATE(' 2014-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA TBLSPC_06 30 SYS_P974 2 TO_DATE(' 2014-08-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA TBLSPC_07 31 SYS_P977 2 TO_DATE(' 2014-09-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA TBLSPC_08 32 SYS_P980 2 TO_DATE(' 2014-10-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA TBLSPC_09 33 SYS_P983 2 TO_DATE(' 2014-11-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA TBLSPC_10 34 SYS_P986 2 TO_DATE(' 2014-12-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA TBLSPC_11 34 rows selected. SQL> SQL> col partition_name for a10 SQL> col hv for a5 SQL> col tblspc for a10 SQL> select subpartition_position as sub#, partition_name, subpartition_name, high_value as hv, tablespace_name as tblspc 2 from user_tab_subpartitions 3 where table_name = 'HISTORY' 4 order by partition_name, subpartition_position; SUB# PARTITION_ SUBPARTITION_NAME HV TBLSPC ---------- ---------- ------------------------------ ----- ---------- 1 P201202 SP201202_K 'K' USERS 1 P201203 SP201203_K 'K' USERS 1 P201204 SP201204_K 'K' USERS 1 P201205 SP201205_K 'K' USERS 2 P201205 SP201205_D 'D' USERS 1 P201206 SP201206_K 'K' USERS 2 P201206 SP201206_D 'D' USERS 1 P201207 SP201207_K 'K' USERS 2 P201207 SP201207_D 'D' USERS 1 P201208 SP201208_K 'K' USERS 2 P201208 SP201208_D 'D' USERS 1 P201209 SP201209_K 'K' USERS 2 P201209 SP201209_D 'D' USERS 1 P201210 SP201210_K 'K' USERS 2 P201210 SP201210_D 'D' USERS 1 P201211 SP201211_K 'K' USERS 2 P201211 SP201211_D 'D' USERS 1 P201212 SP201212_K 'K' USERS 2 P201212 SP201212_D 'D' USERS 1 P201301 SYS_SUBP919 'K' USERS 2 P201301 SYS_SUBP920 'D' USERS 1 SYS_P923 SYS_SUBP921 'K' TBLSPC_02 2 SYS_P923 SYS_SUBP922 'D' TBLSPC_02 1 SYS_P926 SYS_SUBP924 'K' TBLSPC_03 2 SYS_P926 SYS_SUBP925 'D' TBLSPC_03 1 SYS_P929 SYS_SUBP927 'K' TBLSPC_04 2 SYS_P929 SYS_SUBP928 'D' TBLSPC_04 SUB# PARTITION_ SUBPARTITION_NAME HV TBLSPC ---------- ---------- ------------------------------ ----- ---------- 1 SYS_P932 SYS_SUBP930 'K' TBLSPC_05 2 SYS_P932 SYS_SUBP931 'D' TBLSPC_05 1 SYS_P935 SYS_SUBP933 'K' TBLSPC_06 2 SYS_P935 SYS_SUBP934 'D' TBLSPC_06 1 SYS_P938 SYS_SUBP936 'K' TBLSPC_07 2 SYS_P938 SYS_SUBP937 'D' TBLSPC_07 1 SYS_P941 SYS_SUBP939 'K' TBLSPC_08 2 SYS_P941 SYS_SUBP940 'D' TBLSPC_08 1 SYS_P944 SYS_SUBP942 'K' TBLSPC_09 2 SYS_P944 SYS_SUBP943 'D' TBLSPC_09 1 SYS_P947 SYS_SUBP945 'K' TBLSPC_10 2 SYS_P947 SYS_SUBP946 'D' TBLSPC_10 1 SYS_P950 SYS_SUBP948 'K' TBLSPC_11 2 SYS_P950 SYS_SUBP949 'D' TBLSPC_11 1 SYS_P953 SYS_SUBP951 'K' TBLSPC_12 2 SYS_P953 SYS_SUBP952 'D' TBLSPC_12 1 SYS_P956 SYS_SUBP954 'K' TBLSPC_01 2 SYS_P956 SYS_SUBP955 'D' TBLSPC_01 1 SYS_P959 SYS_SUBP957 'K' TBLSPC_02 2 SYS_P959 SYS_SUBP958 'D' TBLSPC_02 1 SYS_P962 SYS_SUBP960 'K' TBLSPC_03 2 SYS_P962 SYS_SUBP961 'D' TBLSPC_03 1 SYS_P965 SYS_SUBP963 'K' TBLSPC_04 2 SYS_P965 SYS_SUBP964 'D' TBLSPC_04 1 SYS_P968 SYS_SUBP966 'K' TBLSPC_05 2 SYS_P968 SYS_SUBP967 'D' TBLSPC_05 1 SYS_P971 SYS_SUBP969 'K' TBLSPC_06 SUB# PARTITION_ SUBPARTITION_NAME HV TBLSPC ---------- ---------- ------------------------------ ----- ---------- 2 SYS_P971 SYS_SUBP970 'D' TBLSPC_06 1 SYS_P974 SYS_SUBP972 'K' TBLSPC_07 2 SYS_P974 SYS_SUBP973 'D' TBLSPC_07 1 SYS_P977 SYS_SUBP975 'K' TBLSPC_08 2 SYS_P977 SYS_SUBP976 'D' TBLSPC_08 1 SYS_P980 SYS_SUBP978 'K' TBLSPC_09 2 SYS_P980 SYS_SUBP979 'D' TBLSPC_09 1 SYS_P983 SYS_SUBP981 'K' TBLSPC_10 2 SYS_P983 SYS_SUBP982 'D' TBLSPC_10 1 SYS_P986 SYS_SUBP984 'K' TBLSPC_11 2 SYS_P986 SYS_SUBP985 'D' TBLSPC_11 65 rows selected. SQL> SQL> spool off