?
Solved

Using Switch Partition method in Oracle

Posted on 2005-03-04
2
Medium Priority
?
1,233 Views
Last Modified: 2008-02-01
Hi,

What i need to do is the following :

Using the switch partition I need to create a table. Then I need to switch back the partitions.
Also how can I remove unneeded partitions?

Thanks
0
Comment
Question by:Cosine_Consultants
[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
2 Comments
 
LVL 23

Expert Comment

by:paquicuba
ID: 13461142
ALTER TABLE <PARTITIONNED_TABLE>
DROP PARTITION <PARTITION_NAME>;

http://www.oracle.com/technology/oramag/oracle/02-jan/o12part.html


0
 
LVL 9

Accepted Solution

by:
neo9414 earned 2000 total points
ID: 13462092
1. This will create a table from a partition. follow the example

CREATE TABLE professional_history (
prof_history_id  NUMBER(10),
person_id        NUMBER(10) NOT NULL,
organization_id  NUMBER(10) NOT NULL,
record_date      DATE NOT NULL,
ph_comments      VARCHAR2(2000))
PARTITION BY RANGE (record_date) (
PARTITION yr0
VALUES LESS THAN (TO_DATE('01-JAN-2000','DD-MON-YYYY')),
PARTITION yr1
VALUES LESS THAN (TO_DATE('01-JAN-2001','DD-MON-YYYY')) ,
PARTITION yr2
VALUES LESS THAN (TO_DATE('01-JAN-2002','DD-MON-YYYY')),
PARTITION yr9
VALUES LESS THAN (MAXVALUE) );



insert into professional_history values (1,1,1,'01-jan-1999','ajay');
insert into professional_history values (1,1,1,'02-jan-1999','ajay');
insert into professional_history values (1,1,1,'01-feb-1999','ajay');
insert into professional_history values (1,1,1,'02-jan-2000','ajay');
insert into professional_history values (1,1,1,sysdate,'ajay');


CREATE TABLE tbl_yr0 (
prof_history_id  NUMBER(10),
person_id1        NUMBER(10) NOT NULL,
organization_id1  NUMBER(10) NOT NULL,
record_date      DATE NOT NULL,
ph_comments      VARCHAR2(2000))

alter table professional_history
exchange partition yr0 with table tbl_yr0;

follow this link. It has step by step example to solve all your needs
http://www.psoug.org/reference/partitions.html
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  â€¦
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 videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Suggested Courses

764 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