Databaase Reorganisation using full export/import

[ronnie]

Hi,

WE just migrated our database from 8.0.5 to 8.1.7.0 using the migration utilities.

We also migrated our context catridge in the database to intermedia at the same time.

Now before we make the database live I want to reoranize the tables using export import because most of them have more than 10 extents. Infact some even have 100's.

The system tables also have many extents.

Doing a export/import fixes the problem.
I know the steps require to do a full import but am not sure how exactly should i go about minimising the extents or fitting the objects to one extent.

Will just a simple export import do or I need to do something else also to fix this problem.

What should I do to take some proactive measures so that this does not happen again.

Thanks

[marist89]

Time out.

First of all, you don't need to re-organize your tables and indexes because you have "lots" of extents.

Oracle 8i has many new features regarding moving objects. Check out ALTER TABLE MOVE and ALTER INDEX REBUILD from the Documentation.

move: http://technet.oracle.com/docs/produ...2a.htm#2055687
rebuild: http://technet.oracle.com/docs/produ...e6.htm#2050230

[Edited by marist89 on 04-26-2002 at 03:36 PM]

[BV1963]

I beleive in your previous postings u said you are using RAID5. That means wheather you have 1 extent or many , they will be spread out on a different drives . You will waste you time doing this export and import or in fact using any other thechniques. If you have 1 or 100 extents, WHO CARES! Even 1 extent is not contigious on a disks, besides how ofter (if ever) do you run compress utility on a disk ?

Regards

[samdba]

Hi Ronnie,

However, if you want to overcome free space fragmentation then you should do Export/Drop schemas/Coalesce tablesapace and then Import.

You should not care for no. of extents specially on 8i. Use locally managed tablespaces rather.

Thanks,

[oravijay]

Hi,

But bare in mind that, you cannot create a Locally Managed Tablespace for System as well as you cannot create any RBS on it.

Vijay.

[jmodic]

Originally posted by oravijay
But bare in mind that, you cannot create a Locally Managed Tablespace for System as well as you cannot create any RBS on it.

You are correct about SYSTEM TS not fitting into LMT, but there is no restrictions about having rollback segments reside in a localy managed tablespace.

[ronnie]

Originally posted by BV1963
I beleive in your previous postings u said you are using RAID5. That means wheather you have 1 extent or many , they will be spread out on a different drives . You will waste you time doing this export and import or in fact using any other thechniques. If you have 1 or 100 extents, WHO CARES! Even 1 extent is not contigious on a disks, besides how ofter (if ever) do you run compress utility on a disk ?

Regards

Can you please explain it a little more clearly.

Also according to you guys I should do a full export, drop schemas , coalesce tablespaces and do the import. Is that all or something else.

What should i do with my system tablespace being so fragmented.

I can create a new Db with the same sid on some other machine, recreate the tablespaces and do the full import., that will fix the problem. But how do i make sure that system table fragmantation does not happen again. How do i determine my storage parameters to fix this.

Thanks


[Edited by ronnie on 04-28-2002 at 01:00 PM]

[nabaig]

Ronnie:

Like people already told you, it does not matter one whit if you have a single extent or thousands. But since you have migrated to 8.1.7 consider doing this.

1. Take a full export.
2. Create LMT's (Locally managed tablespaces) with uniform extent size depending on your data.
3. Now import your data.

Regards,
Nizar