| Adding and Dropping Partitions [message #169571] |
Thu, 27 April 2006 10:57  |
faiz_hyd
Messages: 294
Registered: February 2006
|
Senior Member |
|
|
Hi
I create a partition table now wanted to add a partition some error message like
create table partn
partition by range(cr_dt)
(PARTITION P1
VALUES LESS THAN (to_date('20050101','YYYYMMDD')),
PARTITION P2 VALUES LESS THAN (to_date('20060101','YYYYMMDD')),
partition P3 values less than(maxvalue))
as select * from tab2
Then
select * from partn
NAME COL1 COL2 COL3 CR_DT
dfad adfad adfa dfadf 1/1/2004
bacd adf asdf sfa 1/1/2005
John ABCDEF Bob Frank
Samuel GHIJF Null John
Frank John Samuel Bob
Bob Bull John Frank
adf John Bob
bbb John adfa Bob
adfa adfa adfca fadf 1/1/2006
dfadvad safa adfvas adsfadf 4/27/2006
fdfadf gtasfa fadf sdfdf 10/5/2006
Now want to add a new partition
alter table partn add partition p values less than (to_date('20070101','YYYYMMDD'))
ERROR at line 1:
ORA-14074: partition bound must collate higher than that of the last partition
Another question i was reading the Documentaion regarding dropping all partitions keeping the data basically unpartitioning a partitioned table i came across this
"You are allowed to merge the contents of two adjacent range partitions into one partition. Non adjacent range partitions cannot be merged. The resulting partition inherits the higher upper bound of the two merged partitions."
I just to make partitioned table as unpartitioned How can i do that keeping the data "
Exchanging a Range, Hash, or List Partition
To exchange a partition of a range, hash, or list-partitioned table with a nonpartitioned table, or the reverse, use the ALTER TABLE ... EXCHANGE PARTITION statement. An example of converting a partition into a nonpartitioned table follows. In this example, table stocks can be range, hash, or list partitioned.
ALTER TABLE stocks
EXCHANGE PARTITION p3 WITH stock_table_3;
Here stock_table_3 is a new table basically what i want is just remove partitions.Please clarify
Thanks
[Updated on: Thu, 27 April 2006 11:02] Report message to a moderator |
|
|
|
| Re: Adding and Dropping Partitions [message #169618 is a reply to message #169571] |
Thu, 27 April 2006 15:14  |
smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida
|
Senior Member |
|
|
To create a non-partitioned table based on a partitioned one, and keep all of the data, just create a new, empty table and copy the data. Create table new_table as select * from old_table. You can't just merge partitions, because at some point you'll just end up with a partitioned table with one partition, but it will still be a partitioned table.
Search around on the board for this, it has been discussed before.
As far as your first error, the error is telling you what it means. Can't just add another partition in the middle of where other partitioned data would go. Can add one at the end, or can merge partitions, or can split partitions, or even alter existing partitions. In your case, the "last" partition, with regards to ranges and highest bound, is the p3, the one with maxvalue.
| Quote: |
If the upper partition bound of each partitioning key in the existing high partition is MAXVALUE, then you cannot add a partition to the table. Instead, use the split_table_partition clause to add a partition at the beginning or the middle of the table.
|
|
|
|
|
| Re: Adding and Dropping Partitions [message #169621 is a reply to message #169571] |
Thu, 27 April 2006 15:32 |
smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida
|
Senior Member |
|
|
MYDBA >
MYDBA > create table test (a number, b number)
2 partition by range(a)
3 (
4 partition p1 values less than (6),
5 partition p2 values less than (11),
6 partition p3 values less than (maxvalue)
7 );
Table created.
MYDBA >
MYDBA >
MYDBA > insert into test select level, level from dual connect by level <= 20;
20 rows created.
MYDBA > commit;
Commit complete.
MYDBA >
MYDBA > select * from test partition (p1);
A B
---------- ----------
1 1
2 2
3 3
4 4
5 5
MYDBA > select * from test partition (p2);
A B
---------- ----------
6 6
7 7
8 8
9 9
10 10
MYDBA > select * from test partition (p3);
A B
---------- ----------
11 11
12 12
13 13
14 14
15 15
16 16
17 17
18 18
19 19
20 20
10 rows selected.
MYDBA >
MYDBA > set long 20
MYDBA > select partition_name, high_value from user_tab_partitions order by 1;
PARTITION_NAME HIGH_VALUE
------------------------------ --------------------
P1 6
P2 11
P3 MAXVALUE
MYDBA >
MYDBA > alter table test split partition p3 at (16) into (partition p3, partition p4);
Table altered.
MYDBA >
MYDBA > select partition_name, high_value from user_tab_partitions order by 1;
PARTITION_NAME HIGH_VALUE
------------------------------ --------------------
P1 6
P2 11
P3 16
P4 MAXVALUE
MYDBA >
MYDBA > select * from test partition (p1);
A B
---------- ----------
1 1
2 2
3 3
4 4
5 5
MYDBA > select * from test partition (p2);
A B
---------- ----------
6 6
7 7
8 8
9 9
10 10
MYDBA > select * from test partition (p3);
A B
---------- ----------
11 11
12 12
13 13
14 14
15 15
MYDBA > select * from test partition (p4);
A B
---------- ----------
16 16
17 17
18 18
19 19
20 20
MYDBA >
MYDBA > drop table test;
Table dropped.
MYDBA >
MYDBA > set echo off;
|
|
|
|
| Re: Adding and Dropping Partitions [message #169628 is a reply to message #169621] |
Thu, 27 April 2006 16:21 |
faiz_hyd
Messages: 294
Registered: February 2006
|
Senior Member |
|
|
Thanks Smartin for Reply
It was a good example for Split of partition,
Well i wanted to avoid that
Create table unpartition as select * from partition
because of many level referential constraints and index,
If there is no other way to unpartition a table i am left no other option,
Thanks Once again.
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
