Re: Re-organising a database?

Assuming you don't have LONGs, then it would probably be a good deal faster not to remove the data from the database at all, but to use the MOVE command to move the tables, at a time and at a pace of your choosing from the existing tablespaces into newly-created LMTs.

You need to rebuild indexes manually after any move command, so it's still a fairly involved process, but it's not as pathetically slow (nor potentially as troublesome) as the export-import method.

The only major cost is disk space: until the move is complete, potentially double the disk space is needed, because the old table is not dropped until the new version is fully in place. Likewise with index rebuilds.

Downtime, however, is on a per-table basis, and the reorganisation can take as long as you want, doing it bit by bit until it's finished.

Regards
HJR "Tim Kearsley" <tim.kearsley_at_milton-keynes.gov.uk> wrote in message news:725736ef.0304140119.6623ab37_at_posting.google.com...
> Hi all,
>
> Environment is:
>
> Oracle 8.1.7 on AIX 4.3.3 on RS6000 SP hardware
>
> I've got a legacy database, about 30 Gbytes in size, which I would
> very much like to re-organise. Primarily I would like to implement
> LMTs and to put the datafiles into a structure which is fairly close
> to OFA.
>
> The obvious solution I suppose is to create the new database and
> export/import from the existing one. My problem is the time this
> appears to be taking. A trial took some two days by the time I had
> imported, re-created indices and solved a few problems with invalid
> packages. The database is pretty heavily used and it is going to be
> hard for me to sell a weekend's downtime to the users.
>
> Are there any other techniques I could use to reduce this downtime? I
> admit that I haven't tried tuning the export/import process. At
> present the export is done to a Unix pipe which compresses the dump
> file on the fly. Likewise the trial import was done by uncompressing
> the dump file on the fly. Is it likely to be substantially faster to
> export to multiple files rather than compressing (I am facing an OS
> filesize limit)?
>
> Any thoughts or ideas very welcome.
>
> Thank you.
>
> Tim Kearsley
> Database Manager
> Milton Keynes Council
Received on Mon Apr 14 2003 - 06:04:26 CDT