-
moving data into correct partition
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
PNRDBA
-
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 ?
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|