From oracle-l-bounce@freelists.org Wed Aug 3 05:30:56 2005 Return-Path: Received: from air891.startdedicated.com (root@localhost) by orafaq.com (8.12.10/8.12.10) with ESMTP id j73AUu62015435 for ; Wed, 3 Aug 2005 05:30:56 -0500 X-ClientAddr: 206.53.239.180 Received: from turing.freelists.org (freelists-180.iquest.net [206.53.239.180]) by air891.startdedicated.com (8.12.10/8.12.10) with ESMTP id j73AUmIP015412 for ; Wed, 3 Aug 2005 05:30:48 -0500 Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 7F5BE1DC2B7; Wed, 3 Aug 2005 05:30:43 -0500 (EST) Received: from turing.freelists.org ([127.0.0.1]) by localhost (turing [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 29912-04; Wed, 3 Aug 2005 05:30:43 -0500 (EST) Received: from turing (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id F03DD1DC084; Wed, 3 Aug 2005 05:30:42 -0500 (EST) X-MimeOLE: Produced By Microsoft Exchange V6.0.6603.0 content-class: urn:content-classes:message MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----_=_NextPart_001_01C59816.21DAF886" Subject: RE: Drop Partitioning Date: Wed, 3 Aug 2005 11:28:47 +0100 Message-ID: <1C6E45ADB2EC324F9553E468ABFE0F63030F0DAC@UKWMXM04> X-MS-Has-Attach: X-MS-TNEF-Correlator: Thread-Topic: Drop Partitioning Thread-Index: AcWYEdxL4m3QGL1tSr+y2MiwW2/87AABC7Ow From: "Hallas, John, Tech Dev" To: , "Oracle-L" X-OriginalArrivalTime: 03 Aug 2005 10:28:48.0410 (UTC) FILETIME=[226B7FA0:01C59816] X-archive-position: 23228 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@freelists.org Errors-To: oracle-l-bounce@freelists.org X-original-sender: John.Hallas@gb.vodafone.co.uk Precedence: normal Reply-To: John.Hallas@gb.vodafone.co.uk X-list: oracle-l X-Virus-Scanned: by amavisd-new-20030616-p9 (Debian) at avenirtech.net X-mailscan-MailScanner-Information: Please contact the ISP for more information X-mailscan-MailScanner: Found to be clean X-MailScanner-From: oracle-l-bounce@freelists.org X-Spam-Level: X-Spam-Checker-Version: SpamAssassin 2.63 (2004-01-11) on air891.startdedicated.com X-Spam-Status: No, hits=-3.6 required=5.0 tests=AWL,BAYES_00,HTML_70_80, HTML_FONTCOLOR_UNKNOWN,HTML_MESSAGE autolearn=no version=2.63 ------_=_NextPart_001_01C59816.21DAF886 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: quoted-printable I think it is much simpler than that. Experiment first though =20 John =20 =20 ALTER TABLE EXCHANGE PARTITION WITH TABLE INDEXES VALIDATION EXCEPTIONS INTO ; ALTER TABLE sales=20 EXCHANGE PARTITION feb97 WITH TABLE sales_feb97 INCLUDING INDEXES WITHOUT VALIDATION EXCEPTIONS INTO uwclass.problems; =20 =20 _____ =20 From: oracle-l-bounce@freelists.org [mailto:oracle-l-bounce@freelists.org] On Behalf Of Bryan Wells Sent: 03 August 2005 10:57 To: Oracle-L Subject: Drop Partitioning =20 We have a test box that we want to remove partitioning from. does anyone have the steps to retain table data while dropping the date range partitioning. My first thought was the following. please correct me where I go astray:=20 1. export the table data 2. create similar table in the same tablespace with the appropriate constraints/indexes 3. insert into as select * from 4. alter table drop partition=20 5. drop table cascade 6. alter table rename to =20 Thank you in advance for your help and expertise... =20 ------_=_NextPart_001_01C59816.21DAF886 Content-Type: text/html; charset="us-ascii" Content-Transfer-Encoding: quoted-printable

I think it is much simpler than = that. Experiment first though

 

John

 

 

ALTER TABLE <table_name>
EXCHANGE PARTITION <partition_name>
WITH TABLE <new_table_name>
<including | excluding> INDEXES
<with | without> VALIDATION
EXCEPTIONS INTO <schema.table_name>;

ALTER TABLE = sales
EXCHANGE PARTITION feb97 WITH TABLE sales_feb97
INCLUDING INDEXES
WITHOUT VALIDATION
EXCEPTIONS INTO uwclass.problems;

 

 

From: oracle-l-bounce@freelists.org [mailto:oracle-l-bounce@freelists.org] = On Behalf Of Bryan Wells
Sent: 03 August 2005 = 10:57
To: Oracle-L
Subject: Drop = Partitioning

 

We have a test box that we want to remove partitioning = from.  does anyone have the steps to retain table data while dropping the date range partitioning.  My first thought was the following.  please = correct me where I go astray:

  1. export the table = data
  2. create similar table in the same = tablespace with the appropriate constraints/indexes
  3. insert into <new> as select * from <old>
  4. alter table <old> drop partition =
  5. drop table <old> = cascade
  6. alter table <new> rename to = <old>

 

Thank you in advance for your help and = expertise...

 

------_=_NextPart_001_01C59816.21DAF886-- -- http://www.freelists.org/webpage/oracle-l