Re: Reorg. database - tips

Alberto Rivera wrote:
>
> I'm planning to reorganize the database: Oracle 7.2.3 on AIX 4.1
> I will perform the folling steps:
> 1) Export (full) and backup offline database
> 2) Start up database in NO archivelog mode
> 3) Defragment some tables and tablespaces with export/import
> 4) Regenerate all indexes
> 5) Analyze all tables and indexes
>
> I want to do some improvments in order to perform this tasks faster,
> but I'm not sure if this wil work well:
>
> a) Create a special init.ora file with some changes:
> - Increase sort_area_size (more memory for create index)
> - Decrease db_block_buffers and shared_pool_size (free space
> for sort_area_size, besides not used in sequential access)

     You can create index in parallel if possible.

> b) Create a big temporary tablespace
> A big temporary area could create indexes more compacted ??

       It will avoid the error "Cannot allocate extent nnn for temporary segment ..."

> Extent size equal to sort_area_size could improve performance ??
> How estimate the space analyze will require ??
>
> c) Create index with UNRECOVERABLE option
> It will not create redo log records, what happens if the create
> index fails ??

       Since you use NOARCHIVELOG mode, you will not suffer from problems in database recovery. If an index cannot create, the index cannot create and there is no any impact. On the other hand, if you use ARCHIVELOG mode, you need to carry out a backup for the datafiles involving in the index creations.

       Hope that it can help you.

---
Name   : Lun Wing San
Title  : Oracle Application Developer of Hong Kong Productivity Council
         Oracle Database Administrator and System Administrator of QRC
Phone  : (852)27885841