Home » RDBMS Server » Server Administration » Regarding partitions
Regarding partitions [message #51789] Thu, 13 June 2002 11:36 Go to next message
Rahul
Messages: 94
Registered: December 1998
Member
Hi all,

I got a question..can some body let me know how can we partition a table which has all the data? I mean there is a huge table and I want to partition it now...how can I do that?

Regards,
Rahul
Re: Regarding partitions [message #51792 is a reply to message #51789] Thu, 13 June 2002 11:55 Go to previous message
Mahesh Rajendran
Messages: 10708
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
Oracle has already defined these cases
 
<u>method 1) Export/import method</u>

1)  Export your table:
2)  Drop the table ( or better rename the name...to 
    be in the safer side)
3)  Recreate the new table with partitions:
4)  Import the table with ignore=y:

The ignore=y causes the import to skip the 
table creation and continues to load all rows.

<u>method 2). Insert with a subquery method</u>

1)  Create a partitioned table:
2)  Insert into the partitioned table with a 
    subquery from the non-partitioned table:
3)  If you want the partitioned table to have the 
    same name as the original table, then drop 
    the original table and rename the new table:

<u>method 3). Partition Exchange method</u>

Make use of ALTER TABLE EXCHANGE PARTITION 

1) Create table dummy_t as select with the required partitions

2) Alter table EXCHANGE partition <partition_name>
       with <non_partition_table_name>;

<u>example:</u>

SQL> CREATE TABLE p_emp
   2     (sal NUMBER(7,2))
   3      PARTITION BY RANGE(sal)
   4      (partition emp_p1 VALUES LESS THAN (2000),
   5       partition emp_p2 VALUES LESS THAN (4000));
 Table created.

SQL> CREATE TABLE dummy_y as SELECT sal
 FROM emp WHERE  sal<2000;
 Table created.
 
SQL> CREATE TABLE dummy_z as SELECT sal FROM emp WHERE sal
 BETWEEN 2000 AND 3999;
 Table created.
 
SQL> alter table p_emp exchange partition emp_p1
 with table dummy_y;
 Table altered.

SQL> alter table p_emp exchange partition emp_p2
 with table dummy_z;
 Table altered.
Previous Topic: Need an Information.
Next Topic: Column of varchar2(4000)
Goto Forum:
  


Current Time: Wed Dec 25 20:03:57 CST 2024