• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1258
  • Last Modified:

Using Switch Partition method in Oracle

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
Cosine_Consultants
Asked:
Cosine_Consultants
1 Solution
 
paquicubaCommented:
ALTER TABLE <PARTITIONNED_TABLE>
DROP PARTITION <PARTITION_NAME>;

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


0
 
neo9414Commented:
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

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now