Message-Id: <1189366072.3278.1209698703@webmail.messagingengine.com>
From: "GovindanK" <gkatteri@fastmail.fm>
To: chaganti.suresh@gmail.com, oracle-l@freelists.org
Content-Type: multipart/alternative; boundary="_----------=_118936607232780"; charset="ISO-8859-1"
MIME-Version: 1.0
References: <1828353c0708270644y62320a67xa65eb66371b431a2@mail.gmail.com>
Subject: Re: partitioning a non-partitioned table
In-Reply-To: <1828353c0708270644y62320a67xa65eb66371b431a2@mail.gmail.com>
Date: Sun, 09 Sep 2007 12:27:52 -0700
Sender: oracle-l-bounce@freelists.org
Errors-to: oracle-l-bounce@freelists.org
Precedence: normal
Reply-to: gkatteri@fastmail.fm
--_----------=_118936607232780
Content-Disposition: inline
Content-Transfer-Encoding: 7bit
Content-Type: text/plain; charset="ISO-8859-1"
MIME-Version: 1.0
X-Mailer: MessagingEngine.com Webmail Interface
Date: Sun, 9 Sep 2007 19:27:52 UT

Since you mention about LONG columns and you seem to be in 9i/8i,
i think the fastest method would be exp with direct=y and import
into the partitioned table with commit=y and buffer=5mb (check it
for your env). It may not be possible to avoid downtime. You have
not mentioned the table size. If if it possible for you to know
which row got inserted/modified/deleted during the day, i would
recommend create a partitioned table, insert rows into that till
yesterday, and take minimal maintenance , insert todays data (or
any updates/deletions) to the new partitioned table and rename
the old; The indexes could be pre-created on the new table. Child
tables if any would need to be made to re-point to the new
(parent) partitioned table.  That way you will have minimal
downtime. In case of rows with LONG, you might need to delete and
bring the full row.

HTH

GovindanK
On Mon, 27 Aug 2007 08:44:39 -0500, "Suresh Chaganti" <chaganti.suresh@gm
ail.com> said:

Hello


Can any one point me to a doc on partitioning a non-partitioned
table. I couldnt find one on metalink


I know we can use DBMS_REDEFINITION package startin 9i. But this
has several restriction including inability to use materialized
views, LONG columns etc..Also I have some 8i databases as well.


Even if I need to take the table offline, it is ok, but I am
looking for definitive guidance on the methodology to ensure all
the indexes, constraints will be intact after re-org.


Thank you


Suresh

--_----------=_118936607232780
Content-Disposition: inline
Content-Transfer-Encoding: quoted-printable
Content-Type: text/html; charset="ISO-8859-1"
MIME-Version: 1.0
X-Mailer: MessagingEngine.com Webmail Interface
Date: Sun, 9 Sep 2007 19:27:52 UT

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<html>
  <head>
    <META HTTP-EQUIV=3D"Content-Type" CONTENT=3D"text/html; charset=3DISO-8=
859-1" />
    <title>Re: partitioning a non-partitioned table</title>
  </head>
  <body dir=3D"ltr">
    <p>Since you mention about LONG columns=A0and you seem to be in 9i/8i, =
i think the fastest method would be exp with direct=3Dy and import into the=
 partitioned table with commit=3Dy and buffer=3D5mb (check it for your env)=
. It may not be possible to avoid downtime. You have not mentioned the tabl=
e size. If if it possible for you to know which row got inserted/modified/d=
eleted during the day, i would recommend create a partitioned table, insert=
 rows into that till yesterday, and take minimal maintenance , insert today=
s data (or any updates/deletions) to the new partitioned table and rename t=
he old; The indexes could be pre-created on the new table. Child tables if =
any would need to be made to re-point to the new (parent) partitioned table=
. =A0That way you will have minimal downtime. In case of rows with LONG, yo=
u might need to delete and bring the full row.</p><p>HTH</p><p>GovindanK</p=
><pre>On Mon, 27 Aug 2007 08:44:39 -0500, &quot;Suresh Chaganti&quot; &lt;c=
haganti.suresh@gmail.com&gt; said:=0D
</pre><blockquote class=3D"QuoteMessage" type=3D"cite"><div>Hello</div><div=
>=A0</div><div>Can any one point me to a doc on partitioning a non-partitio=
ned table. I couldnt find one on metalink</div><div>=A0</div><div>I know we=
 can use DBMS_REDEFINITION package startin 9i. But this has several restric=
tion including inability to use materialized views, LONG columns etc..Also =
I have some 8i databases as well.</div><div>=A0</div><div>Even if I need to=
 take the table offline, it is ok, but I am looking for definitive guidance=
 on the methodology to ensure all the indexes, constraints will be intact a=
fter re-org.</div><div>=A0</div><div>Thank you</div><div>=A0</div><div>Sure=
sh</div></blockquote>
  </body>
</html>

--_----------=_118936607232780--

--
http://www.freelists.org/webpage/oracle-l