Printable View
Hi Gurus
I'm running Oracle 9i on Linux 7.1. I got a 4 tables with 75,000 records. Long back these tables were created with pratitions 2800,2900 and maxvalue. Then after we got a project with data ranging 3200. This data went into maxvalue partition. Now i want to create some more partitions 3000,3100,3200 and move this data from maxvalue into appropriate new partitions. Is there any way to do this , with out dropping the tables and recreating them.
Many thanks in advance...
Thanks
Hi
well you gotta search for the alter table split partition..clause with that you dont have to do drop the existing partition..
I am posting Sanjays code once again why did this not work for you
code:--------------------------------------------------------------------------------SQL>create table test (c1 number) partition by range (c1)
2 ( partition p10 values less than (10),
3 partition p20 values less than (20),
4 partition p30 values less than (30),
5* partition pmax values less than (maxvalue))
SQL> /
Table created.
SQL> select high_value from dba_tab_partitions where table_name = 'TEST';
HIGH_VALUE
--------------------------------------------------------------------------------
10
20
30
MAXVALUE
SQL> alter table test add partition p40 values less than (40);
alter table test add partition p40 values less than (40)
*
ERROR at line 1:
ORA-14074: partition bound must collate higher than that of the last partition
SQL> alter table test split partition pmax at (40) into (partition p40, partition pmax);
Table altered.
SQL> select high_value from dba_tab_partitions where table_name = 'TEST';
HIGH_VALUE
--------------------------------------------------------------------------------
10
20
30
MAXVALUE
40--------------------------------------------------------------------------------
HTH
__________________
Sanjay G.
OCP 8i DBA
regards
Hrishy
Hi
here is my comprehensive test result..where I have not dropped any partition neither there is data loss
SQL> drop table test
2 /
Table dropped.
SQL> create table test (c1 number) partition by range (c1)
2 ( partition p10 values less than (10),
3 partition p20 values less than (20),
4 partition p30 values less than (30),
5 partition pmax values less than (maxvalue))
6 /
Table created.
SQL> insert into test
2 values(31)
3 /
1 row created.
SQL> insert into test
2 values(41)
3 /
1 row created.
SQL> alter table test split partition pmax at (40) into (partition p40, partition pmax);
Table altered.
SQL> select * from test
2 /
C1
----------
31
41
SQL> select * from test partition(pmax)
2 /
C1
----------
41
SQL>
regards
Hrishy
P.S:do i understand your question right ?