How can I rebuild my database using import

[gnagesh]

Hi


Since my database is hitting excess fragmentation, would like to rebuild my database from the scratch. Presently my database is in NOARCHIVE LOG MODE. I have full export backup with me. Now my question is, can I rebuild my database using .dmp?

Can any one suggest me the steps to perform the above task OR Is there any good procedure to de-fragment my tablespaces?

Fragmentation details:

TABLESPACE_NAME FSFI
------------------------------ ----------
USERS_SSP1_TRANSACTION 1792
RBS 17
USERS_FSP_TRANSACTION 11
INDEX_SSP1_MASTER 9
SYSTEM 9
INDEX_SSP1_TRANSACTION 8
TEMP 6
INDEX_FSP_MASTER 3
USERS_FSP_MASTER 3
USERS_SSP1_MASTER 3
INDEX_FSP_TRANSACTION 1
INDX 1
OEM_REPOSITORY 1
USERS 1
TOOLS 1
PERFSTAT 1

Thanks In Advance.

[Sureshy]

have you thought about converting your tablespaces to LMT, this will eliminate the need to worry about fragmentation.

In answer to your question :

export with compress=y
drop object owners cascade.
recreate owners
import.

[jgmagnus]

LMT (locally managed tablespaces) is for 8i right?

gnagesh, what version are you running?

[gnagesh]

Hi,

I am on Oracle 8i (8.1.6.0.0). I think LMT's in 8.1.6.0.0 have some bugs. For LMT's 8.1.7 is the right version? Am I right?

Is Only export and import is the option to de-fragment your tablespaces? Or any other procedure?

Even i choose LMT's, is it required to drop the fragmented tablespaces and re-create as LMT, and then Import?

Thanks In Advance.

[Sureshy]

To move to LMT.. use Jeff's plan (preferred) :

Create a new LMT tablespace
Move objects to it
Drop dictionary mananged.

or dbms_space package :
you could also use dbms_space package which will convert for you, however these are not 'true' LMT's. Search forums for 'dbms_space' to get more info on this.

you could also use export/import to convert to LMT.

Ensure you update users default tablespace and any other scripts etc which reference old tablespace.


You do not HAVE to export/import to defragment. You could create a tablespace as big as the one you will defragment, move objects to it and then move them back again.

Not aware of any LMT bugs in 8.1.6 - anyone else ?

[TimHall]

Alternatively you can do this using the Enterprise Manager!

Right-Click on the tablespace and pick "Make Locally Managed..."

This actually does the same process of that people have talked about earlier, but it will generate the complete script and an impact report for you. It might make you feel a little more confident if it's the first time you've done it.

Good luck!