Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

Exchange method partition table

Posted on 2011-03-21
11
469 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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

791 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