subpartitioning is something you need to do at table create time. We can achieve your goals, just not the way you tried. Consider, we'll start with a table with a single (low) partition and a single subpartition -- we need to START with a composite partitioned table:



ops$tkyte@ORA920> create table new_table
2 (OWNER VARCHAR2(30),
3 OBJECT_NAME VARCHAR2(30),
4 SUBOBJECT_NAME VARCHAR2(30),
5 OBJECT_ID NUMBER,
6 DATA_OBJECT_ID NUMBER,
7 OBJECT_TYPE VARCHAR2(18),
8 CREATED DATE,
9 LAST_DDL_TIME DATE,
10 TIMESTAMP VARCHAR2(19),
11 STATUS VARCHAR2(7),
12 TEMPORARY VARCHAR2(1),
13 GENERATED VARCHAR2(1),
14 SECONDARY VARCHAR2(1)
15 )
16 partition by range ( CREATED )
17 subpartition by hash(object_id) subpartitions 1
18 ( partition part1 values less than ( to_date( '01-jan-2003', 'dd-mon-yyyy' ) )
19 )
20 /

Table created.

Now, we can easily make it so additional partitions all have 8 subpartitions:

ops$tkyte@ORA920> alter table new_table set subpartition template 8;

Table altered.

ops$tkyte@ORA920>
ops$tkyte@ORA920> alter table new_table add partition part2 values less than ( to_date('01-jan-2004', 'dd-mon-yyyy' ) );

Table altered.

ops$tkyte@ORA920> alter table new_table add partition part3 values less than ( to_date('01-jan-2005', 'dd-mon-yyyy' ) );

Table altered.

and if you decide part3 should have even MORE then part2, just add more manually

ops$tkyte@ORA920>
ops$tkyte@ORA920>
ops$tkyte@ORA920> alter table new_table modify partition part3 add subpartition;

Table altered.

ops$tkyte@ORA920> alter table new_table modify partition part3 add subpartition;

Table altered.

ops$tkyte@ORA920> alter table new_table modify partition part3 add subpartition;

Table altered.

ops$tkyte@ORA920> alter table new_table modify partition part3 add subpartition;

Table altered.

ops$tkyte@ORA920> alter table new_table modify partition part3 add subpartition;

Table altered.

ops$tkyte@ORA920> alter table new_table modify partition part3 add subpartition;

Table altered.

ops$tkyte@ORA920> alter table new_table modify partition part3 add subpartition;

Table altered.

ops$tkyte@ORA920>
ops$tkyte@ORA920> select partition_name, subpartition_count
2 from user_tab_partitions
3 where table_name = 'NEW_TABLE';

PARTITION_NAME SUBPARTITION_COUNT
------------------------------ ------------------
PART1 1
PART2 8
PART3 15

then, bulk load the table this way:


ops$tkyte@ORA920> alter table new_table NOLOGGING;
Table altered.

ops$tkyte@ORA920> set autotrace on statistics;
ops$tkyte@ORA920> insert /*+ append */ into new_table select * from all_objects;

31622 rows created.


Statistics
----------------------------------------------------------
1248 recursive calls
870 db block gets
106096 consistent gets
57 physical reads
64692 redo size
775 bytes sent via SQL*Net to client
830 bytes received via SQL*Net from client
4 SQL*Net roundtrips to/from client
13 sorts (memory)
0 sorts (disk)
31622 rows processed

ops$tkyte@ORA920> set autotrace off
ops$tkyte@ORA920> alter table new_table logging;
Table altered.

backup and you are done.