Re: Partitioning Large *Existing* Table

Mr. Consultant -

Precreate the partitioned table with the range or hash partitioning you want as you described. Then insert into the partitioned table from the non-partitioned table using

INSERT INTO <part_table_name> (SELECT * FROM <non-part_table_name>);

save off the old table (just in case bad things happen) and then

RENAME <part_table_name> to <non-part_table_name>;

This assumes you have storage space to have both tables active and the column list is the same order. Otherwise you have specify columns in the correct order. The partitioning will separate the data for you.

Hope that helps

Tony Dare
Sr. Service Delivery Engineer
Advanced Product Services
Oracle Corporation

Note: Opinions expressed here are my own and do not necessarily reflect the views of Oracle Corporation.

SAP BASIS Consultant wrote:
> Hello,
>
> I have researched 8i+ partitioning, and I understand how to create a
> partitioned table. Thus, for the sake of this posting, suppose that I
> would like to create a table TDATE partitioned by range, my
> understanding (Unfortunately, I don't have access to an Oracle system
> now) is that I would issue a command such as the following:
>
> CREATE TABLE TDATE ..... PARTITION BY RANGE(DATE) (PARTITION
> F1 VALUES LESS THAN ('01-APR-2001', 'DD-MON-YYYY', PARTITION
> F10...));
>
> However, suppose that there is a large (Say 10GB or 20GB) table
> named TDATE which is not partitioned, and which I would like to
> partition.
>
> Would I have to go through a procedure such as backing up the TDATE
> and/or copying it to a temp. table, exporting its data, deleting
> TDATE, recreating TDATE as a partitioned table, and reimporting the
> data into TDATE, or is there is an easier way to partition an existing
> table?
>
>
>
> Thanks in Advance,
> SAP BASIS Consulant
Received on Wed Apr 21 2004 - 16:24:42 CDT