OraFAQ Forum: SQL & PL/SQL » Add patition to existing table

Home » SQL & PL/SQL » SQL & PL/SQL » Add patition to existing table (oracle 10.1.0.2.0 on IBM AIX 5.2 )

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Add patition to existing table [message #442321]

Sat, 06 February 2010 23:24

oradba123
Messages: 86
Registered: June 2009
Location: india

Member

Hi all,

we have already a table badge2 in our database we need to add partition but when we add this like we get some errors. below is the structure of the table .please any one suggest me how do i add a partition to existing table. quick response will be higly appreciated.

Name Null? Type
----------------------------------------- -------- ------------
EMP_TYPENO NUMBER(10)
EMP_NO NUMBER(10)
EMP_DATE DATE
EMP_TIME DATE
EMP_EVINT NUMBER(1)
EMP_FLAGE NUMBER(1)
MAC VARCHAR2(50)
EMP_CARDNO NUMBER(3)

when i run these queries it give errors like this

sql> alter table badge2 partition by range (emp_date)
add partition p1 values less than (TO_DATE('01/01/2005', 'DD/MM/YYYY')));

ERROR at line 1:
ORA-01735: invalid ALTER TABLE option

or

sql> alter table badge2 partition by range (emp_date)
partition p1 values less than (TO_DATE('01/01/2005', 'DD/MM/YYYY')));

ERROR at line 1:
ORA-01735: invalid ALTER TABLE option

or

SQL> alter table badge2
add partition p1 values less than (TO_DATE('01/01/2004', 'DD/MM/YYYY'));
alter table badge2
*
ERROR at line 1:
ORA-14501: object is not partitioned

Report message to a moderator

Re: Add patition to existing table [message #442322 is a reply to message #442321]

Sat, 06 February 2010 23:35

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

post DDL to (re)create existing partitioned table

Report message to a moderator

Re: Add patition to existing table [message #442323 is a reply to message #442321]

Sat, 06 February 2010 23:42

Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

What is the problem in understanding the error message?
1 and 2 are invalid statements; 3 you cannot add a partitioned to a non partioned table.

See ALTER TABLE partitioning syntax in SQL Reference.

Regards
Michel

Report message to a moderator

Re: Add patition to existing table [message #442330 is a reply to message #442322]

Sun, 07 February 2010 00:04

oradba123
Messages: 86
Registered: June 2009
Location: india

Member

Hi blackswan,

thanks for quick response but what do mean post DDL re(create) partition table.

Report message to a moderator

Re: Add patition to existing table [message #442336 is a reply to message #442330]

Sun, 07 February 2010 00:51

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

oradba123 wrote on Sat, 06 February 2010 22:04

Hi blackswan,

thanks for quick response but what do mean post DDL re(create) partition table.

In order for anyone to provide you with a turn key solution, first we need to have the same base table that you are trying to change.

So you need to provide the CREATE TABLE statement for your table.

The table needs to exist before anyone can ALTER TABLE.....

Report message to a moderator

Re: Add patition to existing table [message #442339 is a reply to message #442323]

Sun, 07 February 2010 01:18

oradba123
Messages: 86
Registered: June 2009
Location: india

Member

Hi michel,

i can understand errors but i was try to conver non partition table to partion table but I got it is not possibel.but my question now is can we create a table by make it range partion using between operator like
ql>CREATE TABLE BADGE2test
(
EMP_TYPENO NUMBER(10),
EMP_NO NUMBER(10),
EMP_DATE DATE,
EMP_TIME DATE,
EMP_EVINT NUMBER(1),
EMP_FLAGE NUMBER(1),
MAC VARCHAR2(50 BYTE),
EMP_CARDNO NUMBER(3)
)
PARTITION BY RANGE (EMP_DATE)
(PARTITION p1 between (TO_DATE('01/01/2010', 'DD/MM/YYYY')) and (TO_DATE('31/12/2010', 'DD/MM/YYYY'))
PARTITION p2 between (TO_DATE('01/01/2011', 'DD/MM/YYYY')) and (TO_DATE('31/12/2011', 'DD/MM/YYYY')));

we need to insert data as per date

is it valid syntax or not.please suggest me. thanks in advance

BEST REGARDS

Report message to a moderator

Re: Add patition to existing table [message #442341 is a reply to message #442336]

Sun, 07 February 2010 01:20

oradba123
Messages: 86
Registered: June 2009
Location: india

Member

Hi blachswan,

my question now is can we create a table by make it range partion using between operator? like

sql>CREATE TABLE BADGE2test
(
EMP_TYPENO NUMBER(10),
EMP_NO NUMBER(10),
EMP_DATE DATE,
EMP_TIME DATE,
EMP_EVINT NUMBER(1),
EMP_FLAGE NUMBER(1),
MAC VARCHAR2(50 BYTE),
EMP_CARDNO NUMBER(3)
)
PARTITION BY RANGE (EMP_DATE)
(PARTITION p1 between (TO_DATE('01/01/2010', 'DD/MM/YYYY')) and (TO_DATE('31/12/2010', 'DD/MM/YYYY'))
PARTITION p2 between (TO_DATE('01/01/2011', 'DD/MM/YYYY')) and (TO_DATE('31/12/2011', 'DD/MM/YYYY')));

we need to insert data as per date

is it valid syntax or not.please suggest me. thanks in advance

BEST REGARDS

Report message to a moderator

Re: Add patition to existing table [message #442343 is a reply to message #442339]

Sun, 07 February 2010 01:25

Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Quote:

i can understand errors but i was try to conver non partition table to partion table

You can use DBMS_REDEFINITION package.

Quote:

but my question now is can we create a table by make it range partion using between operator like

What happens when you tried?
What does the documentation say about the syntax?
Is "between" in the syntax diagram?

Do not try to guess whatthe syntax is, just read the documentation and its examples.

Regards
Michel

Report message to a moderator

Previous Topic:	Table design
Next Topic:	which function or code can replace joins

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Apr 25 23:09:26 CDT 2024