Home » RDBMS Server » Server Administration » Add Partition Error
| Add Partition Error [message #115476] |
Mon, 11 April 2005 19:40  |
vqd2697
Messages: 36
Registered: October 2004
|
Member |
|
|
Hi Guru,
I need to add more partition for year 2005 into one of the table below but got this error and don't understand why? Please help me out.
Errors ORA-14074:
ALTER TABLE spy_lab_mart
ADD PARTITION POC_DATA30501 VALUES LESS THAN (TO_DATE(' 2005-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) TABLESPACE BISC_DATA;
ADD PARTITION POC_DATA30507 VALUES LESS THAN (TO_DATE(' 2005-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) TABLESPACE BISC_DATA;
Existing schema of the table:
PARTITION BY RANGE(FROM_DT)
(PARTITION POC_DATA30201 VALUES LESS THAN (TO_DATE(' 2002-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
STORAGE(INITIAL 10M
NEXT 1M
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
FREELISTS 1
FREELIST GROUPS 1)
TABLESPACE BISC_DATA
NOLOGGING,
PARTITION POC_DATA30207 VALUES LESS THAN (TO_DATE(' 2002-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
STORAGE(INITIAL 10M
NEXT 1M
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
FREELISTS 1
FREELIST GROUPS 1)
TABLESPACE BISC_DATA
NOLOGGING,
PARTITION POC_DATA30301 VALUES LESS THAN (TO_DATE(' 2003-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
STORAGE(INITIAL 10M
NEXT 1M
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
FREELISTS 1
FREELIST GROUPS 1)
TABLESPACE BISC_DATA
NOLOGGING,
PARTITION POC_DATA30307 VALUES LESS THAN (TO_DATE(' 2003-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
STORAGE(INITIAL 10M
NEXT 1M
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
FREELISTS 1
FREELIST GROUPS 1)
TABLESPACE BISC_DATA
NOLOGGING,
PARTITION POC_DATA30401 VALUES LESS THAN (TO_DATE(' 2004-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
STORAGE(INITIAL 10M
NEXT 1M
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
FREELISTS 1
FREELIST GROUPS 1)
TABLESPACE BISC_DATA
NOLOGGING,
PARTITION POC_DATA30407 VALUES LESS THAN (TO_DATE(' 2004-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
STORAGE(INITIAL 10M
NEXT 1M
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
FREELISTS 1
FREELIST GROUPS 1)
TABLESPACE BISC_DATA
NOLOGGING
|
|
|
|
| Re: Add Partition Error [message #115529 is a reply to message #115476] |
Tue, 12 April 2005 08:58  |
smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida
|
Senior Member |
|
|
Do a copy and paste of your screen and show us the exact error you are getting.
Also, I'm assuming the second statement that leaves off the alter table portion is just a typo in this post and not what you are doing?
Do an exact copy and paste or spool to a file, no editing.
Oh and also, I wouldn't bother with the time portion of a date if you are just specifying all 0's:
MYDBA@ORCL > create table t (a date);
Table created.
MYDBA@ORCL > insert into t values (to_date('2005-01-01','YYYY-MM-DD'));
1 row created.
MYDBA@ORCL > select * from t;
A
--------------------
01-JAN-2005 00:00:00
1 row selected.
MYDBA@ORCL > insert into t values (to_date('2005-01-01 00:00:00','YYYY-MM-DD HH24:MI:SS'));
1 row created.
MYDBA@ORCL > select * from t;
A
--------------------
01-JAN-2005 00:00:00
01-JAN-2005 00:00:00
2 rows selected.
|
|
|
|
| Re: Add Partition Error [message #115578 is a reply to message #115476] |
Tue, 12 April 2005 13:43 |
vqd2697
Messages: 36
Registered: October 2004
|
Member |
|
|
I hoped this will help you understand more the problem.
frisk2 (vqd) 113 : more add_partition.sql
ALTER TABLE spy_lab_mart
ADD PARTITION POC_DATA30501 VALUES LESS THAN (TO_DATE('2005-01-01','YYYY-MM-DD')) TABLESPACE BISC_DATA;
ADD PARTITION POC_DATA30507 VALUES LESS THAN (TO_DATE('2005-07-01','YYYY-MM-DD')) TABLESPACE BISC_DATA;
frisk2 (vqd) 114 : orab < add_partition.sql > partition.out
frisk2 (vqd) 115 : more partition.out
SQL*Plus: Release 9.2.0.1.0 - Production on Tue Apr 12 10:38:53 2005
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Connected to:
Oracle9i Enterprise Edition Release 9.2.0.5.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.5.0 - Production
Session altered.
SQL> 2 ADD PARTITION POC_DATA30501 VALUES LESS THAN (TO_DATE('2005-01-01','YYYY-MM-DD')) TABLESPACE BISC_DATA
*
ERROR at line 2:
ORA-14074: partition bound must collate higher than that of the last partition
SQL> SP2-0734: unknown command beginning "ADD PARTIT..." - rest of line ignored.
SQL> Disconnected from Oracle9i Enterprise Edition Release 9.2.0.5.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.5.0 - Production
|
|
|
|
| Re: Add Partition Error [message #115582 is a reply to message #115476] |
Tue, 12 April 2005 14:23 |
smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida
|
Senior Member |
|
|
I think this is (at least one of) the problem:
ALTER TABLE spy_lab_mart
ADD PARTITION POC_DATA30501 VALUES LESS THAN (TO_DATE('2005-01-01','YYYY-MM-DD')) TABLESPACE BISC_DATA;
ADD PARTITION POC_DATA30507 VALUES LESS THAN (TO_DATE('2005-07-01','YYYY-MM-DD')) TABLESPACE BISC_DATA;
See how you have a semicolan after the first add partition statement and before the second? That isn't the right syntax. Check the syntax diagrams. You can probably either add a comma in there and put them both in one statement, or leave the semicolan but then start a second statement to add the second partition. But check the diagrams.
|
|
|
|
| Re: Add Partition Error [message #115587 is a reply to message #115582] |
Tue, 12 April 2005 15:00 |
vqd2697
Messages: 36
Registered: October 2004
|
Member |
|
|
Even after I took out the second partition, please see below:
frisk2 (vqd) 126 : more add_partition.sql
ALTER TABLE spy_lab_mart
ADD PARTITION POC_DATA30501 VALUES LESS THAN (TO_DATE('2005-01-01','YYYY-MM-DD')) TABLESPACE BISC_DATA;
frisk2 (vqd) 127 : orab < add_partition.sql > partition.out
frisk2 (vqd) 128 : more partition.out
SQL*Plus: Release 9.2.0.1.0 - Production on Tue Apr 12 11:56:54 2005
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Connected to:
Oracle9i Enterprise Edition Release 9.2.0.5.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.5.0 - Production
Session altered.
SQL> 2 ADD PARTITION POC_DATA30501 VALUES LESS THAN (TO_DATE('2005-01-01','YYYY-MM-DD')) TABLESPACE BISC_DATA
*
ERROR at line 2:
ORA-14074: partition bound must collate higher than that of the last partition
SQL> Disconnected from Oracle9i Enterprise Edition Release 9.2.0.5.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.5.0 - Production
frisk2 (vqd) 129 :
|
|
|
|
| Re: Add Partition Error [message #115591 is a reply to message #115476] |
Tue, 12 April 2005 16:14 |
smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida
|
Senior Member |
|
|
Do you have a maxvalue in there?
From the 10g docs:
Restrictions on Adding Range Partitions
If the upper partition bound of each partitioning key in the existing high partition is MAXVALUE, then you cannot add a partition to the table. Instead, use the split_table_partition clause to add a partition at the beginning or the middle of the table.
Here is a test and a split:
MYDBA@ORCL > col pname format a5
MYDBA@ORCL >
MYDBA@ORCL > create table test(a date, data char(10))
2 partition by range(a)
3 (
4 partition p1 values less than (to_date('2005-01-01', 'YYYY-MM-DD')),
5 partition p2 values less than (to_date('2005-02-01', 'YYYY-MM-DD')),
6 partition p3 values less than (to_date('2005-03-01', 'YYYY-MM-DD'))
7 );
Table created.
MYDBA@ORCL >
MYDBA@ORCL > select partition_name pname, high_value from user_tab_partitions
2 where table_name = 'TEST' order by partition_name;
PNAME HIGH_VALUE
----- --------------------------------------------------------------------------------
P1 TO_DATE(' 2005-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
P2 TO_DATE(' 2005-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
P3 TO_DATE(' 2005-03-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
3 rows selected.
MYDBA@ORCL >
MYDBA@ORCL > alter table test add partition p4 values less than (to_date('2005-04-01','YYYY-MM-
DD'));
Table altered.
MYDBA@ORCL >
MYDBA@ORCL > select partition_name pname, high_value from user_tab_partitions
2 where table_name = 'TEST' order by partition_name;
PNAME HIGH_VALUE
----- --------------------------------------------------------------------------------
P1 TO_DATE(' 2005-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
P2 TO_DATE(' 2005-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
P3 TO_DATE(' 2005-03-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
P4 TO_DATE(' 2005-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
4 rows selected.
MYDBA@ORCL >
MYDBA@ORCL > alter table test add partition p5 values less than (maxvalue);
Table altered.
MYDBA@ORCL >
MYDBA@ORCL > select partition_name pname, high_value from user_tab_partitions
2 where table_name = 'TEST' order by partition_name;
PNAME HIGH_VALUE
----- --------------------------------------------------------------------------------
P1 TO_DATE(' 2005-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
P2 TO_DATE(' 2005-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
P3 TO_DATE(' 2005-03-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
P4 TO_DATE(' 2005-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
P5 MAXVALUE
5 rows selected.
MYDBA@ORCL >
MYDBA@ORCL > alter table test add partition p6 values less than (to_date('2005-05-01','YYYY-MM-
DD'));
alter table test add partition p6 values less than (to_date('2005-05-01','YYYY-MM-DD'))
*
ERROR at line 1:
ORA-14074: partition bound must collate higher than that of the last partition
MYDBA@ORCL >
MYDBA@ORCL > alter table test split partition p5 at (to_date('2005-05-01','YYYY-MM-DD'))
2 into (partition p5, partition p6);
Table altered.
MYDBA@ORCL >
MYDBA@ORCL > select partition_name pname, high_value from user_tab_partitions
2 where table_name = 'TEST' order by partition_name;
PNAME HIGH_VALUE
----- --------------------------------------------------------------------------------
P1 TO_DATE(' 2005-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
P2 TO_DATE(' 2005-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
P3 TO_DATE(' 2005-03-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
P4 TO_DATE(' 2005-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
P5 TO_DATE(' 2005-05-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
P6 MAXVALUE
6 rows selected.
MYDBA@ORCL >
MYDBA@ORCL > drop table test;
Table dropped.
|
|
|
|
|
|
Goto Forum:
Current Time: Sat Jan 25 03:41:41 CST 2025
|
] 
Blog
Search
Help
Members
Register
Login
Home
