• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 552
  • Last Modified:

Exchange method partition table

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;


What would be the statement to go from a non-partition table (stock_table_3)to a hash partition table (stocks)?
0
msimons4
Asked:
msimons4
  • 6
  • 5
1 Solution
 
morguloCommented:
It would be the same statement, but you must look at "<with | without> VALIDATION" parameter and indexes.

Read this aricle: http://download.oracle.com/docs/cd/B19306_01/server.102/b14231/partiti.htm#i1107555
0
 
msimons4Author Commented:
So it would also be, even though you are going from a non-partition table to a partition table,
 ALTER TABLE stocks
    EXCHANGE PARTITION p3 WITH stock_table_3;


0
 
morguloCommented:
Yes, you can exchange partition with non-partitioned table only. But remember if you use without validation parameter data in exchanged partition could be not match partition key. And all global indexes are marked as unusable.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
msimons4Author Commented:
When you say you can exchange partition with non-partition table only, can I also exchange a non-partition with a partition?
0
 
morguloCommented:
"You can convert a partition (or subpartition) into a non-partitioned table, and a non-partitioned table into a partition (or subpartition) of a partitioned table by exchanging their data segments."

It means that your statement is correct and should works.
0
 
msimons4Author Commented:
OK, so can my partition table have lets say 8 partitions and if so, when I do the exchange from the non-partition table do I have to do the exchange into only one partition and then split into the other 7 partitions?
0
 
morguloCommented:
No, non-partitioned table is switched with only one of 8 partitions. If you want exchange all (8) partitions from partitioned table you should have 8 non-partitioned tables. One non-partitioned table exchange data with one partition from partitioned table.
0
 
msimons4Author Commented:
So, after I do the exchange into one of my partitions, then what do I do? Lets say I have 8 partitions total.?
0
 
morguloCommented:
You have data from partition in non-partitioned table, and ie you can backup data and drop table... First of all why you want exchange partition? What do you need to do?
0
 
msimons4Author Commented:
To load the data into a partition table. I am getting this error after exchange/split. What is wrong with my syntax?

alter table schema.table_partition
   exchange partition p1 with table schema.table
   WITHOUT VALIDATION;

Alter table schema.table_partition split partition at (p1) values
  (Partition p1,
   Partition p2,
   Partition p3,
   Partition p4,
   Partition p5,
   Partition p6,
   Partition p7,
   Partition p8);

ERROR at line 1:
ORA-14028: missing AT or VALUES keyword
0
 
morguloCommented:
SPLIT PARTITION clause cannot be used for hash partitions.
I think you should use:
CREATE TABLE table_partition
PARTITION BY HASH (hash_col)
PARTITIONS 8
PARALLEL (degree 4)
NOLOGGING
as
select * from table;

Open in new window


See also other methods: http://www.rampant-books.com/art_nanda_partitioning1.htm
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

  • 6
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now