Re: Partitioning on an existing table in Oracle 8.1.7
From: ddf <oratune_at_msn.com>
Date: Tue, 17 Jul 2012 15:01:29 -0700 (PDT)
Message-ID: <b014741c-ac08-46c0-829d-76b4ad15887b_at_x6g2000pbh.googlegroups.com>
On Jul 17, 6:17 am, raja <dextersu..._at_gmail.com> wrote:
> Hi All,
>
> How to create a partition on an existing table (has 250 million records), in oracle 8.1.7? Also, the table has indexes and triggers associated with it.
>
> I have one of the below method, don't know whether this will work !!!
> 1. ALTER TABLE TABLE_OLD_SOURCE RENAME TO TABLE_OLD_SOURCE_BACKUP; ( )
> 2. Create TABLE TABLE_NEW_TARGET with PARTITIONS based on letters
> 3. INSERT INTO TABLE_NEW_TARGET SELECT * FROM TABLE_OLD_SOURCE_BACKUP; COMMIT;
> 4. Create the indexes and triggers of TABLE_OLD_SOURCE, in TABLE_NEW_TARGET too
> 5. collect the stats of TABLE_NEW_TARGET
>
> Doubts :
> a. Don't know whether RENAME will work in this version?
> b. In the above method, will step1 work, if triggers are there ?
> c. Will the above method work?
> d. Any other better or practically working methods? Please suggest.
>
> Thanks in Advance.
>
> With Regards,
> Raja.
Date: Tue, 17 Jul 2012 15:01:29 -0700 (PDT)
Message-ID: <b014741c-ac08-46c0-829d-76b4ad15887b_at_x6g2000pbh.googlegroups.com>
On Jul 17, 6:17 am, raja <dextersu..._at_gmail.com> wrote:
> Hi All,
>
> How to create a partition on an existing table (has 250 million records), in oracle 8.1.7? Also, the table has indexes and triggers associated with it.
>
> I have one of the below method, don't know whether this will work !!!
> 1. ALTER TABLE TABLE_OLD_SOURCE RENAME TO TABLE_OLD_SOURCE_BACKUP; ( )
> 2. Create TABLE TABLE_NEW_TARGET with PARTITIONS based on letters
> 3. INSERT INTO TABLE_NEW_TARGET SELECT * FROM TABLE_OLD_SOURCE_BACKUP; COMMIT;
> 4. Create the indexes and triggers of TABLE_OLD_SOURCE, in TABLE_NEW_TARGET too
> 5. collect the stats of TABLE_NEW_TARGET
>
> Doubts :
> a. Don't know whether RENAME will work in this version?
> b. In the above method, will step1 work, if triggers are there ?
> c. Will the above method work?
> d. Any other better or practically working methods? Please suggest.
>
> Thanks in Advance.
>
> With Regards,
> Raja.
Is it so difficult to go to http://tahiti.oracle.com, find the 8.1.7 documentation and look this up?
http://docs.oracle.com/cd/A87860_01/doc/server.817/a76956/tables.htm#430
Yes, rename works in 8.1.7. RENAME associates all triggers and indexes with the new name but doesn't rename the indexes or triggers. You will need to have new names for these items when you recreate them against your partitioned table.
David Fitzjarrell Received on Tue Jul 17 2012 - 17:01:29 CDT