Re: Oracle 8i database reorg

From: Paul Drake <discgolfdba_at_yahoo.com>
Date: Thu, 13 May 2004 10:59:37 -0700 (PDT)
Message-ID: <20040513175937.49836.qmail@web20414.mail.yahoo.com>

Aaron Sentell <asentell1_at_cox.net> wrote:
> I have a 30 GB Oracle 8.1.7 database that I would
> like to reorg and at the same time modify to use
> locally managed tablespaces. This is the first time
> I will be attempting something like this. Database
> downtime is not a big issue, but I still want to do
> it as quickly and efficiently as possible. I've read
> a 1999 technical document from the IOUG web site
> that describes how to do this using export/import,
> but I was wondering if anyone has any other
> methodologies that they prefer? BTW, I need the
> tablespace names to remain the same.
>
> Thanks,
> Aaron

Aaron,

In light of the recent discussions on
comp.databases.oracle.server concerning
"clustering_factor", what all do you plan on accomplishing in the re-org?

Might you think of re-ordering the rows of data in the heap tables?

Export and import means that you'll be moving data out of the database, and back into it.

What I am suggesting has been recommended here before, but I'll say it anyways:

if you have sufficient space available, why not either:

rename, CTAS (create table as select - nologging) new tables off of old tables into the new LMT, say a tablespace at a time? This would allow for an order by to cluster data more tightly (if desired).
alter table :my_table_name move <new_LMT_tablespace>; alter index :my_index_name rebuild <new_LMT_tablespace> NOLOGGING;

by not moving data out of the database and back into it, you should be able to accomplish the re-org much more quickly than exp/imp.

You might want to take a look at Howard Roger's site. He put up a paper a day or 2 ago on "Clustering Factor".

btw - you really ought to think about migrating to 9.2.0.5 at this point.

hth.

Do you Yahoo!?
Yahoo! Movies - Buy advance tickets for 'Shrek 2' http://movies.yahoo.com/showtimes/movie?mid=1808405861

Please see the official ORACLE-L FAQ: http://www.orafaq.com

To unsubscribe send email to: oracle-l-request_at_freelists.org put 'unsubscribe' in the subject line.

--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------

Received on Thu May 13 2004 - 13:55:24 CDT