?
Solved

Exchange method partition table

Posted on 2011-03-21
11
Medium Priority
?
494 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
[X]
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
  • 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
Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

 

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 2000 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 learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

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

Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.
Suggested Courses

752 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