Oracle11g questions about the partition table-CodePudding

The database version: oracle11g 11.2.0.4.0
Current library have a table to partition, range partitioning, according to the automatic partition each month, but now there is a problem, is the time of partition fields have null values, when insert the data submitted to the ORA - 14300,
Solution: is when inserting data, NULL values can be converted to a time,
Solution 2: insert a NULL value, automatically NULL into another partition, insert a NULL value, namely time, writing time partition,
Methods the good solution, but method 2 do not know how,
Here is to use the RANGE - the LIST to create partitions, but they still offer ORA - 14300 error when inserting data,
Is now want to create a composite partition, to write the time of normal RANGE partitions, if there is a NULL value into the LIST partition,
Are all the same partition fields, such as: CREATE_TIME
Don't know how to do this, please master answer, thank you!
RANGE - the LIST is below, but the insert data will be submitted to the ORA - 14300 error:

 
The CREATE TABLE T_TEST 
(
TABLE_ID VARCHAR2 (64) NOT NULL, 
CREATE_TIME DATE DEFAULT SYSDATE 
) 
PARTITION BY RANGE (CREATE_TIME) 
The INTERVAL (NUMTOYMINTERVAL (1, 'MONTH')) STORE IN (TBS_TEST_DATA) 
SUBPARTITION BY LIST (CREATE_TIME) 
SUBPARTITION TEMPLATE (
SUBPARTITION SP_0 VALUES (NULL) in TABLESPACE TBS_TEST_DATA) 
(PARTITION P_0 VALUES LESS THAN (TO_DATE (' 1970-01-01 ', '- DD YYYY - MM)) in TABLESPACE TBS_TEST_DATA); 


INSERT INTO T_TEST (TABLE_ID, CREATE_TIME) VALUES (' test01, NULL); 
COMMIT;

CodePudding user response:

Null is excluded from the scope of any one can't listed

CodePudding user response:

Don't partition, table directly

CodePudding user response:

Use method 1, 2 will not work

CodePudding user response:

The

refer to the original poster nightgoblin response:

database version: oracle11g 11.2.0.4.0
Current library have a table to partition, range partitioning, according to the automatic partition each month, but now there is a problem, is the time of partition fields have null values, when insert the data submitted to the ORA - 14300,
Solution: is when inserting data, NULL values can be converted to a time,
Solution 2: insert a NULL value, automatically NULL into another partition, insert a NULL value, namely time, writing time partition,
Methods the good solution, but method 2 do not know how,
Here is to use the RANGE - the LIST to create partitions, but they still offer ORA - 14300 error when inserting data,
Is now want to create a composite partition, to write the time of normal RANGE partitions, if there is a NULL value into the LIST partition,
Are all the same partition fields, such as: CREATE_TIME
Don't know how to do this, please master answer, thank you!
RANGE - the LIST is below, but the insert data will be submitted to the ORA - 14300 error:
 
The CREATE TABLE T_TEST 
(
TABLE_ID VARCHAR2 (64) NOT NULL, 
CREATE_TIME DATE DEFAULT SYSDATE 
) 
PARTITION BY RANGE (CREATE_TIME) 
The INTERVAL (NUMTOYMINTERVAL (1, 'MONTH')) STORE IN (TBS_TEST_DATA) 
SUBPARTITION BY LIST (CREATE_TIME) 
SUBPARTITION TEMPLATE (
SUBPARTITION SP_0 VALUES (NULL) in TABLESPACE TBS_TEST_DATA) 
(PARTITION P_0 VALUES LESS THAN (TO_DATE (' 1970-01-01 ', '- DD YYYY - MM)) in TABLESPACE TBS_TEST_DATA); 


INSERT INTO T_TEST (TABLE_ID, CREATE_TIME) VALUES (' test01, NULL); 
COMMIT; 

1, you use range + list composite partition doesn't solve this problem, the original poster on composite index can deepen the understanding;
2, you can convert null into the time, but the need for this time to set up a separate partition, such as: partition pnull values less than (to_date (' 0001-12-1 ', '- dd yyyy - mm);
3, in addition, you can also create a partitioned table is built on the maxvalue partition (shown below), because the null is larger than any value, such null will insert to the partition in the future, but to build the partition may be late to maintain increased complexity,
The create table t5 (c1 date) partition by range (c1)
(
Partition p1 values less than (to_date (' 2020-02-1 ', '- dd yyyy - mm)),
Partition p2 values less than (to_date (' 2020-03-1 ', '- dd yyyy - mm)),
Partition p3 values less than (to_date (' 2020-04-1 ', '- dd yyyy - mm)),
Partition p4 values less than (to_date (' 2020-05-1 ', '- dd yyyy - mm)),
Partition the p5 values less than (to_date (' 2020-06-1 ', '- dd yyyy - mm)),
Partition p6 values less than (to_date (' 2020-07-1 ', '- dd yyyy - mm)),
Partition p7 values less than (to_date (' 2020-08-1 ', '- dd yyyy - mm)),
Partition p8 values less than (to_date (' 2020-09-1 ', '- dd yyyy - mm)),
Partition p300 values less than (maxvalue)
);