Home » SQL & PL/SQL » SQL & PL/SQL » Create Partition in Existing Table (Oracle 10g)
Create Partition in Existing Table [message #326439] Wed, 11 June 2008 09:16 Go to next message
amorphous4u
Messages: 34
Registered: December 2007
Location: Boston, US
Member

Hi,

I am having an existing table with below description.
CREATE TABLE TEST
(
ID NUMBER NOT NULL,
DATETIME DATE,
TYPE VARCHAR2(20),
PRICE NUMBER(5,2),
USER VARCHAR2(40),
GROUP VARCHAR2(40)
);

I want to add partitions and sub partition into it with below details.

PARTITION BY RANGE (DATETIME)
SUBPARTITION BY LIST(TYPE)
SUBPARTITION TEMPLATE(
SUBPARTITION T1 VALUES ('TYPE1'),
SUBPARTITION T2 VALUES ('TYPE2'),
SUBPARTITION T3 VALUES ('TYPE3'),
SUBPARTITION T4 VALUES ('TYPE4'))
(
PARTITION MAR VALUES LESS THAN (TO_DATE('2008-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
PARTITION APR VALUES LESS THAN (TO_DATE('2008-05-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
PARTITION MAY VALUES LESS THAN (TO_DATE('2008-06-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
PARTITION JUN VALUES LESS THAN (TO_DATE('2008-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
)

I know that we can recreate the table with these partitions and sub partitions and migrate data into new table and rename it.
But my concern is every month we have to create new partition for that month and remove the oldest partition. Since the quantity of data is huge and it takes hours and hours to move data to new table. So i need an example of something like

ALTER TABLE ADD PARTITION(SUBPARTITION) ......

Can anyone help me with this?

Thanks and Regards,
Amit

Re: Create Partition in Existing Table [message #326440 is a reply to message #326439] Wed, 11 June 2008 09:21 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
>it takes hours and hours to move data to new table.
Move data from where to where?
Why a new table every month?
Please clarify what exists today.
Re: Create Partition in Existing Table [message #326451 is a reply to message #326439] Wed, 11 June 2008 09:41 Go to previous messageGo to next message
Michel Cadot
Messages: 64133
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
So i need an example of something like

ALTER TABLE ADD PARTITION(SUBPARTITION) ......

What about reading the SQL Reference at ALTER TABLE page?

Regards
Michel
Re: Create Partition in Existing Table [message #326455 is a reply to message #326451] Wed, 11 June 2008 09:58 Go to previous messageGo to next message
amorphous4u
Messages: 34
Registered: December 2007
Location: Boston, US
Member

I read the article and tried below query:

ALTER TABLE test
ADD PARTITION JUN BY RANGE (DATETIME) VALUES LESS THAN (TO_DATE('2008-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))

But it is throwing error as

ORA-14020: this physical attribute may not be specified for a table partition

Regards
Re: Create Partition in Existing Table [message #326456 is a reply to message #326439] Wed, 11 June 2008 10:01 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
The posted SQL & error code do not match.
14020, 00000, "this physical attribute may not be specified for a table partition"
// *Cause:   unexpected option was encountered while parsing physical 
//           attributes of a table partition; valid options for Range or 
//           Composite Range partitions are INITRANS, MAXTRANS, TABLESPACE,  
//           STORAGE, PCTFREE, and PCTUSED; only TABLESPACE may be specified 
//           for Hash partitions
// *Action:  remove invalid option(s) from the list of physical attributes 
//           of a table partition
// *Comment: this error could have resulted from omission of a
//           terminating (right) parenthesis following the list of 
//           partition descriptions


What was the actual SQL that you tried?
Re: Create Partition in Existing Table [message #326461 is a reply to message #326456] Wed, 11 June 2008 10:22 Go to previous messageGo to next message
amorphous4u
Messages: 34
Registered: December 2007
Location: Boston, US
Member

I have checked the error but I need some example which I can use to create partition.

Thanks
Re: Create Partition in Existing Table [message #326468 is a reply to message #326439] Wed, 11 June 2008 10:28 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
use CUT & PASTE to show us EXACTLY the SQL you tried.


http://www.orafaq.com/forum/t/88153/0/
Please read & follow posting guidelines as stated in URL above
Re: Create Partition in Existing Table [message #326470 is a reply to message #326461] Wed, 11 June 2008 10:30 Go to previous messageGo to next message
Michel Cadot
Messages: 64133
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Examples are given in the above page, maybe the don't fit EXACTLY what you have but in this case copy and paste EXACTLY whar you did, that is copy and paste your session starting from create table up to the erroneous statement.

But before please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter).
Use the "Preview Message" button to verify.

Regards
Michel
Re: Create Partition in Existing Table [message #326473 is a reply to message #326470] Wed, 11 June 2008 10:35 Go to previous messageGo to next message
amorphous4u
Messages: 34
Registered: December 2007
Location: Boston, US
Member

The desc of existing table:

SQL> desc TEST1
Name Null? Type
----------------------------------------- -------- -------------------
ID NOT NULL NUMBER
DATETIME DATE
TYPE VARCHAR2(20)
PRICE NUMBER(5,2)
USERNAME VARCHAR2(40)
GROUPNAME VARCHAR2(40)

I fired below query to alter table:

SQL> ALTER TABLE TEST1
2 ADD PARTITION JUN BY RANGE (DATETIME) VALUES LESS THAN (TO_DATE('2008-07-01 00:00:00', 'SYYYY-M
M-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
3 /
ADD PARTITION JUN BY RANGE (DATETIME) VALUES LESS THAN (TO_DATE('2008-07-01 00:00:00', 'SYYYY-MM-DD
*
ERROR at line 2:
ORA-14020: this physical attribute may not be specified for a table partition

Regards
Re: Create Partition in Existing Table [message #326476 is a reply to message #326473] Wed, 11 June 2008 10:45 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
SQL> drop table test1;

Table dropped.

SQL> create table test1
(
 id number not null,
 datetime date,
 type varchar2(20)
) nologging;  2    3    4    5    6

Table created.

SQL> alter table test1 add partition test1_p1 by range(datetime) VALUES LESS THAN
(TO_DATE('2008-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'));  2
alter table test1 add partition test1_p1 by range(datetime) VALUES LESS THAN
                                         *
ERROR at line 1:
ORA-14020: this physical attribute may not be specified for a table partition



You are trying convert a non-partitioned table to a partitioned table. It is not possible with the way your are doing it. You first need to import the data into the partitioned table and after that as others have already suggested you need to check the syntax for add partition and drop partition. It will be something like this.

alter table <table_name> add partition <partition_name> <whatever partition clause>
alter table <table_name> drop partition <partition_name>


If your table contains a maxval partition then you can use split partition command. For more information about adding, dropping and splitting partition check this link.

http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/statements_3001.htm#i2131218

Hope that helps

Regards

Raj

check this link.

http://www.dbapool.com/articles/031003.html

Regards

Raj

[Updated on: Wed, 11 June 2008 10:50]

Report message to a moderator

Re: Create Partition in Existing Table [message #326478 is a reply to message #326476] Wed, 11 June 2008 10:47 Go to previous message
amorphous4u
Messages: 34
Registered: December 2007
Location: Boston, US
Member

Thanks Raj,

It Helped.
Previous Topic: Viewing the errors on a procedure
Next Topic: CHR(13)||CHR(10) at the end of the sql
Goto Forum:
  


Current Time: Thu Dec 08 04:12:27 CST 2016

Total time taken to generate the page: 0.05496 seconds