Thanks Cherif and Pavan for ansewring.
Now , I have another doubt.
I would like to create a partition by year and and for each year by month.
In the last example I used INTERVAL.
I have the code below.
Is there a way to create the partition structure without hard code the
year, as :
PARTITION before_2007 VALUES LESS THAN (2007) TABLESPACE tbs
drop table DATA_LOAD;
CREATE TABLE DATA_LOAD
( data_id NUMBER(6),
source_id NUMBER,
insert_date DATE,
delete_date date,
data_size NUMBER,
state VARCHAR2(20),
sys_current_date DATE DEFAULT SYSDATE,
year_delete_date NUMBER GENERATED ALWAYS AS
(TO_NUMBER(TO_CHAR(delete_date,'YYYY'))) VIRTUAL,
mm_delete_date NUMBER GENERATED ALWAYS AS
(TO_NUMBER(TO_CHAR(delete_date,'MM'))) VIRTUAL)
PARTITION BY RANGE (year_delete_date)
SUBPARTITION BY RANGE (mm_delete_date)
( PARTITION before_2007 VALUES LESS THAN (2007) TABLESPACE tbs
(SUBPARTITION before_2007_01 VALUES LESS THAN (2),
SUBPARTITION before_2007_02 VALUES LESS THAN (3),
SUBPARTITION before_2007_03 VALUES LESS THAN (4),
SUBPARTITION before_2007_04 VALUES LESS THAN (5),
SUBPARTITION before_2007_05 VALUES LESS THAN (6),
SUBPARTITION before_2007_06 VALUES LESS THAN (7),
SUBPARTITION before_2007_07 VALUES LESS THAN (8),
SUBPARTITION before_2007_08 VALUES LESS THAN (9),
SUBPARTITION before_2007_09 VALUES LESS THAN (10),
SUBPARTITION before_2007_10 VALUES LESS THAN (11),
SUBPARTITION before_2007_11 VALUES LESS THAN (12),
SUBPARTITION before_2007_12 VALUES LESS THAN (MAXVALUE)),
PARTITION before_2008 VALUES LESS THAN (2009) TABLESPACE tbs
(SUBPARTITION before_2008_01 VALUES LESS THAN (2),
SUBPARTITION before_2008_02 VALUES LESS THAN (3),
SUBPARTITION before_2008_03 VALUES LESS THAN (4),
SUBPARTITION before_2008_04 VALUES LESS THAN (5),
SUBPARTITION before_2008_05 VALUES LESS THAN (6),
SUBPARTITION before_2008_06 VALUES LESS THAN (7),
SUBPARTITION before_2008_07 VALUES LESS THAN (8),
SUBPARTITION before_2008_08 VALUES LESS THAN (9),
SUBPARTITION before_2008_09 VALUES LESS THAN (10),
SUBPARTITION before_2008_10 VALUES LESS THAN (11),
SUBPARTITION before_2008_11 VALUES LESS THAN (12),
SUBPARTITION before_2008_12 VALUES LESS THAN (MAXVALUE)),
PARTITION before_2009 VALUES LESS THAN (2010) TABLESPACE tbs
(SUBPARTITION before_2009_01 VALUES LESS THAN (2),
SUBPARTITION before_2009_02 VALUES LESS THAN (3),
SUBPARTITION before_2009_03 VALUES LESS THAN (4),
SUBPARTITION before_2009_04 VALUES LESS THAN (5),
SUBPARTITION before_2009_05 VALUES LESS THAN (6),
SUBPARTITION before_2009_06 VALUES LESS THAN (7),
SUBPARTITION before_2009_07 VALUES LESS THAN (8),
SUBPARTITION before_2009_08 VALUES LESS THAN (9),
SUBPARTITION before_2009_09 VALUES LESS THAN (10),
SUBPARTITION before_2009_10 VALUES LESS THAN (11),
SUBPARTITION before_2009_11 VALUES LESS THAN (12),
SUBPARTITION before_2009_12 VALUES LESS THAN (MAXVALUE)),
PARTITION before_2010 VALUES LESS THAN (2011) TABLESPACE tbs
(SUBPARTITION before_2010_01 VALUES LESS THAN (2),
SUBPARTITION before_2010_02 VALUES LESS THAN (3),
SUBPARTITION before_2010_03 VALUES LESS THAN (4),
SUBPARTITION before_2010_04 VALUES LESS THAN (5),
SUBPARTITION before_2010_05 VALUES LESS THAN (6),
SUBPARTITION before_2010_06 VALUES LESS THAN (7),
SUBPARTITION before_2010_07 VALUES LESS THAN (8),
SUBPARTITION before_2010_08 VALUES LESS THAN (9),
SUBPARTITION before_2010_09 VALUES LESS THAN (10),
SUBPARTITION before_2010_10 VALUES LESS THAN (11),
SUBPARTITION before_2010_11 VALUES LESS THAN (12),
SUBPARTITION before_2010_12 VALUES LESS THAN (MAXVALUE)),
PARTITION before_2011 VALUES LESS THAN (2012) TABLESPACE tbs
(SUBPARTITION before_2011_01 VALUES LESS THAN (2),
SUBPARTITION before_2011_02 VALUES LESS THAN (3),
SUBPARTITION before_2011_03 VALUES LESS THAN (4),
SUBPARTITION before_2011_04 VALUES LESS THAN (5),
SUBPARTITION before_2011_05 VALUES LESS THAN (6),
SUBPARTITION before_2011_06 VALUES LESS THAN (7),
SUBPARTITION before_2011_07 VALUES LESS THAN (8),
SUBPARTITION before_2011_08 VALUES LESS THAN (9),
SUBPARTITION before_2011_09 VALUES LESS THAN (10),
SUBPARTITION before_2011_10 VALUES LESS THAN (11),
SUBPARTITION before_2011_11 VALUES LESS THAN (12),
SUBPARTITION before_2011_12 VALUES LESS THAN (MAXVALUE)),
PARTITION before_2012 VALUES LESS THAN (2013) TABLESPACE tbs
(SUBPARTITION before_2012_01 VALUES LESS THAN (2),
SUBPARTITION before_2012_02 VALUES LESS THAN (3),
SUBPARTITION before_2012_03 VALUES LESS THAN (4),
SUBPARTITION before_2012_04 VALUES LESS THAN (5),
SUBPARTITION before_2012_05 VALUES LESS THAN (6),
SUBPARTITION before_2012_06 VALUES LESS THAN (7),
SUBPARTITION before_2012_07 VALUES LESS THAN (8),
SUBPARTITION before_2012_08 VALUES LESS THAN (9),
SUBPARTITION before_2012_09 VALUES LESS THAN (10),
SUBPARTITION before_2012_10 VALUES LESS THAN (11),
SUBPARTITION before_2012_11 VALUES LESS THAN (12),
SUBPARTITION before_2012_12 VALUES LESS THAN (MAXVALUE)),
PARTITION before_2013 VALUES LESS THAN (2014) TABLESPACE tbs
(SUBPARTITION before_2013_01 VALUES LESS THAN (2),
SUBPARTITION before_2013_02 VALUES LESS THAN (3),
SUBPARTITION before_2013_03 VALUES LESS THAN (4),
SUBPARTITION before_2013_04 VALUES LESS THAN (5),
SUBPARTITION before_2013_05 VALUES LESS THAN (6),
SUBPARTITION before_2013_06 VALUES LESS THAN (7),
SUBPARTITION before_2013_07 VALUES LESS THAN (8),
SUBPARTITION before_2013_08 VALUES LESS THAN (9),
SUBPARTITION before_2013_09 VALUES LESS THAN (10),
SUBPARTITION before_2013_10 VALUES LESS THAN (11),
SUBPARTITION before_2013_11 VALUES LESS THAN (12),
SUBPARTITION before_2013_12 VALUES LESS THAN (MAXVALUE)),
PARTITION before_2014 VALUES LESS THAN (2015) TABLESPACE tbs
(SUBPARTITION before_2014_01 VALUES LESS THAN (2),
SUBPARTITION before_2014_02 VALUES LESS THAN (3),
SUBPARTITION before_2014_03 VALUES LESS THAN (4),
SUBPARTITION before_2014_04 VALUES LESS THAN (5),
SUBPARTITION before_2014_05 VALUES LESS THAN (6),
SUBPARTITION before_2014_06 VALUES LESS THAN (7),
SUBPARTITION before_2014_07 VALUES LESS THAN (8),
SUBPARTITION before_2014_08 VALUES LESS THAN (9),
SUBPARTITION before_2014_09 VALUES LESS THAN (10),
SUBPARTITION before_2014_10 VALUES LESS THAN (11),
SUBPARTITION before_2014_11 VALUES LESS THAN (12),
SUBPARTITION before_2014_12 VALUES LESS THAN (MAXVALUE)),
PARTITION before_2999 VALUES LESS THAN (MAXVALUE) TABLESPACE tbs
(SUBPARTITION before_2999_01 VALUES LESS THAN (2),
SUBPARTITION before_2999_02 VALUES LESS THAN (3),
SUBPARTITION before_2999_03 VALUES LESS THAN (4),
SUBPARTITION before_2999_04 VALUES LESS THAN (5),
SUBPARTITION before_2999_05 VALUES LESS THAN (6),
SUBPARTITION before_2999_06 VALUES LESS THAN (7),
SUBPARTITION before_2999_07 VALUES LESS THAN (8),
SUBPARTITION before_2999_08 VALUES LESS THAN (9),
SUBPARTITION before_2999_09 VALUES LESS THAN (10),
SUBPARTITION before_2999_10 VALUES LESS THAN (11),
SUBPARTITION before_2999_11 VALUES LESS THAN (12),
SUBPARTITION before_2999_12 VALUES LESS THAN (MAXVALUE))
)
ENABLE ROW MOVEMENT
;
-- Insert massivo
declare
seq1 NUMBER;
seq2 NUMBER;
BEGIN
DELETE FROM DATA_LOAD;
seq1 := 0;
seq2 := 0;
while (seq1 < 11)
loop
seq2 := 30;
while (seq2 < 3001)
loop
INSERT INTO DATA_LOAD (DATA_ID, SOURCE_ID, INSERT_DATE, DATA_SIZE,
STATE, SYS_CURRENT_DATE)
VALUES ( 1, 1, SYSDATE-seq2, 1,
'M:'||seq2, DEFAULT);
INSERT INTO DATA_LOAD (DATA_ID, SOURCE_ID, INSERT_DATE, DATA_SIZE,
STATE, SYS_CURRENT_DATE)
VALUES ( 1, 2, SYSDATE-seq2, 1,
'M:'||seq2, DEFAULT);
INSERT INTO DATA_LOAD (DATA_ID, SOURCE_ID, INSERT_DATE, DATA_SIZE,
STATE, SYS_CURRENT_DATE)
VALUES ( 1, 3, SYSDATE-seq2, 1,
'M:'||seq2, DEFAULT);
INSERT INTO DATA_LOAD (DATA_ID, SOURCE_ID, INSERT_DATE, DATA_SIZE,
STATE, SYS_CURRENT_DATE)
VALUES ( 1, 4, SYSDATE-seq2, 1,
'M:'||seq2, DEFAULT);
INSERT INTO DATA_LOAD (DATA_ID, SOURCE_ID, INSERT_DATE, DATA_SIZE,
STATE, SYS_CURRENT_DATE)
VALUES ( 1, 5, SYSDATE-seq2, 1,
'M:'||seq2, DEFAULT);
INSERT INTO DATA_LOAD (DATA_ID, SOURCE_ID, INSERT_DATE, DATA_SIZE,
STATE, SYS_CURRENT_DATE)
VALUES ( 1, 6, SYSDATE-seq2, 1,
'M:'||seq2, DEFAULT);
seq2 := seq2 + 1;
end loop;
seq1 := seq1 + 1;
end loop;
end;
/
UPDATE DATA_LOAD SET delete_date = insert_date+1
WHERE to_char(insert_date, 'YYYY') = 2007;
UPDATE DATA_LOAD SET delete_date = insert_date+1
WHERE to_char(insert_date, 'yyyy') = 2008;
UPDATE DATA_LOAD SET delete_date = insert_date+1
WHERE to_char(insert_date, 'yyyy') = 2009;
UPDATE DATA_LOAD SET delete_date = insert_date+1
WHERE to_char(insert_date, 'yyyy') = 2010;
UPDATE DATA_LOAD SET delete_date = insert_date+1
WHERE to_char(insert_date, 'yyyy') = 2011;
UPDATE DATA_LOAD SET delete_date = insert_date+1
WHERE to_char(insert_date, 'yyyy') = 2012;
UPDATE DATA_LOAD SET delete_date = insert_date+1
WHERE to_char(insert_date, 'yyyy') = 2013;
UPDATE DATA_LOAD SET delete_date = insert_date+1
WHERE to_char(insert_date, 'yyyy') = 2014;
UPDATE DATA_LOAD SET delete_date = insert_date+1
WHERE to_char(insert_date, 'yyyy') = 2015;
commit;
BEGIN
dbms_stats.gather_table_stats(user,'DATA_LOAD',ESTIMATE_PERCENT => 100,
GRANULARITY=>'ALL' , CASCADE => true);
END;
/
select TABLE_NAME,COMPOSITE,PARTITION_NAME,NUM_ROWS,subpartition_count from
user_tab_partitions where TABLE_NAME='DATA_LOAD';
select TABLE_NAME,PARTITION_NAME,SUBPARTITION_NAME,NUM_ROWS from
USER_TAB_SUBPARTITIONS where TABLE_NAME='DATA_LOAD';
select table_name, partitioning_type, partition_count, interval from
user_part_tables;
SELECT * FROM DATA_LOAD;
select count(*) from (
SELECT * FROM DATA_LOAD WHERE DELETE_DATE IS NULL
);
select count(*) from (
SELECT * FROM DATA_LOAD WHERE DELETE_DATE IS not NULL
);
Regards
Eriovaldo
2015-05-19 5:55 GMT-03:00 Cherif Ben Henda <cherif.benhenda@xxxxxxxxx>:
Hi Eriovaldo,
You can try to use virtual column as below.
CREATE TABLE DATA_LOAD_INT
( data_id NUMBER(6),
source_id NUMBER,
insert_date DATE,
delete_date DATE,
data_size NUMBER,
state VARCHAR2(20),
sys_current_date DATE DEFAULT SYSDATE,
year_delete_date NUMBER GENERATED ALWAYS AS
(TO_NUMBER(TO_CHAR(delete_date,'YYYY'))) VIRTUAL,
mm_delete_date NUMBER GENERATED ALWAYS AS
(TO_NUMBER(TO_CHAR(delete_date,'MM'))) VIRTUAL,
delete_date_part date GENERATED always as (
NVL(delete_date,TO_DATE('01-01-1900', 'DD-MM-YYYY'))) VIRTUAL
)
PARTITION BY RANGE (delete_date_part)
INTERVAL(NUMTOYMINTERVAL(1, 'MONTH'))
( PARTITION p0 VALUES LESS THAN (TO_DATE('01-01-1900', 'DD-MM-YYYY'))
)
enable row movement
;
Thanks,
Cherif.
2015-05-19 5:21 GMT+01:00 pavan kumar <pavan_843@xxxxxxxxxxx>:
Hi Eriovaldo,
If you refer to oracle documentation, it clearly says.conveys the
restrictions that "NULL" value are not allowed
For INTERVAL Clause
"You cannot specify NULL values for the partitioning key column."
Try to use some default value for that column, it may resolve issue.
- Thanks
Pavan Kumar N
------------------------------
*From:* Eriovaldo Andrietta <ecandrietta@xxxxxxxxx>
*To:* ORACLE-L <oracle-l@xxxxxxxxxxxxx>
*Sent:* Tuesday, 19 May 2015 6:13 AM
*Subject:* Using partition with interval and insert NULL
Hello,
I have the code below and it is not working right, when I insert data.
I got the error message: ORA-14300: partitioning key maps to a partition
outside maximum permitted number of partitions
the insert has a null in the column delete_date.
Is there a way to define the partition using interval with NULL ?
DROP TABLE DATA_LOAD_INT;
CREATE TABLE DATA_LOAD_INT
( data_id NUMBER(6),
source_id NUMBER,
insert_date DATE,
delete_date DATE,
data_size NUMBER,
state VARCHAR2(20),
sys_current_date DATE DEFAULT SYSDATE,
year_delete_date NUMBER GENERATED ALWAYS AS
(TO_NUMBER(TO_CHAR(delete_date,'YYYY'))) VIRTUAL,
mm_delete_date NUMBER GENERATED ALWAYS AS
(TO_NUMBER(TO_CHAR(delete_date,'MM'))) VIRTUAL)
PARTITION BY RANGE (delete_date)
INTERVAL(NUMTOYMINTERVAL(1, 'MONTH'))
( PARTITION p0 VALUES LESS THAN (TO_DATE('01-01-1900', 'DD-MM-YYYY'))
)
enable row movement
;
-- Insert massivo
declare
seq1 NUMBER;
seq2 NUMBER;
BEGIN
DELETE FROM DATA_LOAD_INT;
seq1 := 0;
seq2 := 0;
while (seq1 < 11)
loop
seq2 := 30;
while (seq2 < 3001)
loop
INSERT INTO DATA_LOAD_INT (DATA_ID, SOURCE_ID, INSERT_DATE,
DELETE_DATE, DATA_SIZE, STATE, SYS_CURRENT_DATE)
VALUES ( 1, 1, SYSDATE-seq2,
null, 1, 'M:'||seq2, DEFAULT);
seq2 := seq2 + 1;
end loop;
seq1 := seq1 + 1;
end loop;
end;
/
Regards
Eriovaldo
