Partitioning and re-indexing - Methodology comments welcome! 3

[TurtleOp]

Hello All!

I am maintaining for one of our customer one old Oracle 8.1.7.4 database with 2 standby database using arc log file .
OS: AIX 5L on IBM P520 and RS-6000

In the production database I have the 2 most heavily used tables of the prod environment with a size of 10GB each one and 15M rows.
Btw, the total size of the db is 110GB.
In the meantime, I have a 18 and 20 indexes on them, but the majority of them are inconsistent because too old.
When I try to recreate only one index it costs me an average of 2 hours! (I extract the DDL code, drop the object and recreate the index)
Then I am seriously thinking to export the entire table and then re-import it.

Anyway, the goal is to:
- recreate all the indexes on those 2
- Partitioning the 2 tables – probably with Composite type.

I would like to ask you advice on the methodology. I have only one week-end to do it.

Do you agree with me to say that it is better to export/import the tables before partitioning them?

Cheers/TurtleOp

 

[Thargy]

The following might be better, but I'm not sure if DBMS_REDEFINITION is available in 8.

http://www.oracle-base.com/articles/misc/PartitioningAnExistingTable.php

Regards

T

 

[TurtleOp]

Sounds good! I didn't know this package. I will check it now, Thanks a lot!
TurtleOp,

 

[Beilstwh]

Build your partitioned table structure and then do a select into into the new table. When it is done, everything is indexed and partitioned. When you get done, rename the new table to the correct name.

Bill
Oracle DBA/Developer
New York State, USA

 

[LKBrwnDBA]

DBMS_REDEFINITION is not available in 8.1.7

Following Beilstwh's advice, write the DDL to create the partitions and execute CREATE TABLE ... AS SELECT in parallel sessions.

Also, avoid creating "global" indexes, use "local" indexes (Easier to maintain).



----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[TurtleOp]

Thank you very much guy!
I will do it on this way.
Kindest Regards,
TurtleOp

 

[TurtleOp]

(Guys and not guy)