Solved

Exchange method partition table

Posted on 2011-03-21
11
459 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
ID: 35184731
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
ID: 35184811
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
ID: 35184869
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
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
ID: 35184890
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
ID: 35184951
"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
 

Author Comment

by:msimons4
ID: 35184981
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
ID: 35185075
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
ID: 35185209
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
ID: 35186872
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
ID: 35193043
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
ID: 35194129
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.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

770 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