Re: Oracle 8i database reorg
- From: Paul Drake <discgolfdba@xxxxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Thu, 13 May 2004 10:59:37 -0700 (PDT)
--- Aaron Sentell <asentell1@xxxxxxx> 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:
1. 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).
2. 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.
Pd
__________________________________
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@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
Other related posts:
