Oracle-L: RE: Non-partitioned table to Hash Partitioned table

From: Toepke, Kevin M <ktoepke_at_trilegiant.com>
Date: Fri, 01 Mar 2002 11:33:32 -0800
Message-ID: <F001.0041D1BA.20020301113332@fatcity.com>

All:

The dbms_redefinition thing works. My test on a table that I have exclusive access to it took 10 minutes to convert a non-partitioned table of 9.5 million rows into a has partitioned table with 8 partitions!

I have included the script I used.

Thanks to all that replied!

Caver
BEGIN
DBMS_REDEFINITION.can_redef_table('KTOEPKE', 'INVOICE_DETAIL'); END;
/
BEGIN
DBMS_REDEFINITION.START_REDEF_TABLE('KTOEPKE'

                     ,'INVOICE_DETAIL'
                     ,'T_INVOICE_DETAIL');

END;
/
PROMPT

PROMPT ** Here is where to do the following:
**

PROMPT ** 1) Create Triggers on t_invoice_detail
**

PROMPT ** 2) Create Indexes on t_invoice_detail
**

PROMPT ** 3) Create Constraints on t_invoice_detail
**

PROMPT ** The Constraints should be disabled
**

PROMPT ** 4) Perform any grants on t_invoice_detail
**

PROMPT

@@new_invoice_detail_idx.sql
BEGIN
DBMS_REDEFINITION.SYNC_INTERIM_TABLE('KTOEPKE'

                     ,'INVOICE_DETAIL'
                     ,'T_INVOICE_DETAIL');

END;
/
BEGIN
DBMS_REDEFINITION.FINISH_REDEF_TABLE('KTOEPKE'

                     ,'INVOICE_DETAIL'
                     ,'T_INVOICE_DETAIL');

END;
/

-----Original Message-----
Sent: Thursday, February 28, 2002 2:14 PM To: Multiple recipients of list ORACLE-L

Oracle9i provides on online data move facility that will allow you to do this with a minimum of restrictions. I discuss this, and give an example in my Oracle Press book, Oracle9i New Features. This is facilitated throughout the new dbms_redefinition package.

Robert G. Freeman - Oracle8i OCP
Oracle DBA Technical Lead
CSX Midtier Database Administration

The Cigarette Smoking Man: Anyone who can appease a man's conscience can take his freedom away from him.

-----Original Message-----
Sent: Thursday, February 28, 2002 11:28 AM To: Multiple recipients of list ORACLE-L

Hello!

I am trying to figure out the best way to convert a non-partitioned table with approx 20 million rows into a hash-partitioned table. This should be done with minimal down-time. This will be in an Oracle 9i environment done at a time when only SELECTs are occuring on the table.

The best way I can figure is to create a second table and copy the data over; create the indexes; and rename the tables.

I would appreciate any hints on what section of TFM to read.

Kevin Toepke
ktoepke_at_trilegiant.com

The information in this electronic mail message is Trilegiant Confidential and may be legally privileged. It is intended solely for the addressee(s). Access to this Internet electronic mail message by anyone else is unauthorized. If you are not the intended recipient, any disclosure, copying, distribution or action taken or omitted to be taken in reliance on it is prohibited and may be unlawful.

The sender believes that this E-mail and any attachments were free of any virus, worm, Trojan horse, and/or malicious code when sent. This message and its attachments could have been infected during transmission. By reading the message and opening any attachments, the recipient accepts full responsibility for taking protective and remedial action about viruses and other defects. Trilegiant Corporation is not liable for any loss or damage arising in any way from this message or its attachments.

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Toepke, Kevin M
  INET: ktoepke_at_trilegiant.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Freeman, Robert
  INET: Robert_Freeman_at_csx.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Toepke, Kevin M
  INET: ktoepke_at_trilegiant.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Received on Fri Mar 01 2002 - 13:33:32 CST