Solved

Exchange method partition table

Posted on 2011-03-21
11
445 Views
Last Modified: 2012-05-11
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
Comment
Question by:msimons4
  • 6
  • 5
11 Comments
 
LVL 5

Expert Comment

by:morgulo
Comment Utility
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
 

Author Comment

by:msimons4
Comment Utility
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
 
LVL 5

Expert Comment

by:morgulo
Comment Utility
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
 

Author Comment

by:msimons4
Comment Utility
When you say you can exchange partition with non-partition table only, can I also exchange a non-partition with a partition?
0
 
LVL 5

Expert Comment

by:morgulo
Comment Utility
"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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

Author Comment

by:msimons4
Comment Utility
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
 
LVL 5

Expert Comment

by:morgulo
Comment Utility
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
 

Author Comment

by:msimons4
Comment Utility
So, after I do the exchange into one of my partitions, then what do I do? Lets say I have 8 partitions total.?
0
 
LVL 5

Expert Comment

by:morgulo
Comment Utility
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
 

Author Comment

by:msimons4
Comment Utility
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
 
LVL 5

Accepted Solution

by:
morgulo earned 500 total points
Comment Utility
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

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Suggested Solutions

Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…

728 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

8 Experts available now in Live!

Get 1:1 Help Now