Home » SQL & PL/SQL » SQL & PL/SQL » Add patition to existing table (oracle 10.1.0.2.0 on IBM AIX 5.2 )
Add patition to existing table [message #442321] Sat, 06 February 2010 23:24 Go to next message
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





Re: Add patition to existing table [message #442322 is a reply to message #442321] Sat, 06 February 2010 23:35 Go to previous messageGo to next message
BlackSwan
Messages: 24915
Registered: January 2009
Senior Member
post DDL to (re)create existing partitioned table
Re: Add patition to existing table [message #442323 is a reply to message #442321] Sat, 06 February 2010 23:42 Go to previous messageGo to next message
Michel Cadot
Messages: 63818
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
Re: Add patition to existing table [message #442330 is a reply to message #442322] Sun, 07 February 2010 00:04 Go to previous messageGo to next message
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.
Re: Add patition to existing table [message #442336 is a reply to message #442330] Sun, 07 February 2010 00:51 Go to previous messageGo to next message
BlackSwan
Messages: 24915
Registered: January 2009
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.....
Re: Add patition to existing table [message #442339 is a reply to message #442323] Sun, 07 February 2010 01:18 Go to previous messageGo to next message
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





Re: Add patition to existing table [message #442341 is a reply to message #442336] Sun, 07 February 2010 01:20 Go to previous messageGo to next message
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
Re: Add patition to existing table [message #442343 is a reply to message #442339] Sun, 07 February 2010 01:25 Go to previous message
Michel Cadot
Messages: 63818
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
Previous Topic: Table design
Next Topic: which function or code can replace joins
Goto Forum:
  


Current Time: Sat Oct 01 04:16:11 CDT 2016

Total time taken to generate the page: 0.09470 seconds