Re: Oracle 8.1.7 Database Reorganisation

Compress=y says to recreate the table/index with the same size, but instead of many extents as before, you will now have 1 extent.

This is the old way of doing things I think. Also, no matter why you are reorging, the following will at least get you into Locally Managed tablespaces and the old reasons for doing a reorg should then be minimized/eliminated.

TEST (because I haven't actually done it this way before) the following in a test database for yourself, but this should be much better... - Create a new tablespace as Locally Managed and give user XXX quota on it and while you are at it, change user XXX's default tablespace to this new tablespace.
- Alter Table ... Move ( I think this is the syntax to move to a different tablespace for tables)
- Alter Index ... Rebuild ( This works like move for table and I don't think there is a move for indexes)
- Not sure, but you might want to make sure these 2 commands do not do what compress=y was doing. Test...

But, one problem you might hit is that the move table does not work on LONG (and I assume BLOB or CLOBs). Then, you will need to do an export and import as before but create the user with a new default tablespace of the new LMT tablespace.

-- 
"Bernard Vicaire" <bernard.vicaire_at_wanadoo.fr> wrote in message
news:bl4tj6$mc8$1_at_news-reader5.wanadoo.fr...


> Hi

>

> I "m looking at the best methode to reorganize an Oracle database, I'm

> usually use thes steps :

>

> - Export with optin compress=y

> - Drop user XXX cascade

> - Create User XXX

> - Import (from user=XXX touser=XXX)

>

> After deleting lines in tables and making a reorganisation, i'm surprised

 to


> see in DBA studio that the used size of the tablespace is the same before

> and after these steps even if the tablespace is dropped and recreated

 before


> the Import.

>

> - Why ?

> - Is these steps the good methode to reorganize a database ?

> - How to reduce the used size of the tablespace ?

> - Is it better to delete and recreate the TEMP tablespace too ?

>

> Regards

>

> Bernard

>

>

>