Hi Techies,
I have below queries and need your expertise help.
(Q 1) The SQL command for getting the partitions currently looks something like the following:
"SELECT PARTITION_NAME, HIGH_VALUE FROM ALL_TAB_PARTITIONS WHERE TABLE_OWNER = :schemaName AND TABLE_NAME = :tableName AND PARTITION_NAME != 'xxxxxxx'";
Now we have sub-partitions and looking for a query to get partition and sub-partitions ?
Tried the below but it fails with error - invalid identifier
SELECT PARTITION_NAME, SUBPARTITION_NAME, HIGH_VALUE FROM ALL_TAB_SUBPARTITIONS WHERE TABLE_OWNER = xxxxxxxxx AND TABLE_NAME = xxxxxxxxxx ;
(Q 2) Currently, we use a query similar to the following to add partitions:
"ALTER TABLE ${" + DDL_VARIABLE_KEY_SCHEMA_NAME + "}.${" + DDL_VARIABLE_KEY_TABLE_NAME + "} ADD PARTITION ${" + DDL_VARIABLE_KEY_PARTITION_NAME + "} VALUES LESS THAN(${" + DDL_VARIABLE_KEY_CUTOFF_DATE + "})"
How should I go about creating the query to create the sub-partitions ? please suggest with oracle query and will write into code.
From below which is best way to use option - a, b or c ? and please suggestion if these are not apt and the best alternative.
a) ALTER TABLE schema_name.table_name modify partition partition_name add subpartition subpartition_name values ('0,1,2,3');
b)
alter table schema_name.table_name
modify PARTITION BY RANGE (CREATEDATE)
SUBPARTITION BY LIST (PURGEID)
subpartition template(
SUBPARTITION SP1 values (0),
SUBPARTITION SP2 values (1),
SUBPARTITION SP3 values (2),
SUBPARTITION SP4 values (3))
(
PARTITION p1 VALUES LESS THAN (TO_DATE('01-JAN-2018', 'DD-MON-YYYY')),
PARTITION p2 VALUES LESS THAN (TO_DATE('01-MAY-2018', 'DD-MON-YYYY')),
PARTITION p3 VALUES LESS THAN (TO_DATE('01-SEP-2018', 'DD-MON-YYYY')),
PARTITION p5 VALUES LESS THAN (maxvalue)) online;
(or) something like below ... if so what would be the complete query.
c) ALTER TABLE table_name MODIFY PARTITION partition_name ADD SUBPARTITION subpartition_definition;
(Q 3) BTW is there a way to get the cutoff date from the sub-partition without needing to retrieve the parent partition as well ?
Thanks.