Re: compress a tablespace

It is _a_ solution - even the standard one. However, there are other things of a similar nature.

You can try:

        alter tablespace ts_name coalesce;

straight away and it may reclaim some of the fragmentation, depending on its nature. (If you are a careful - sensible? - person, you will do it after a backup.)

If you have enough space elsewhere, you can create a temporary tablespace big enough, copy all the tables and data; make sure you are able to re-create indexes, constraints, grants and re-validate invalid procedures, triggers etc and then drop the old tables with cascade constraints. When done, run the coalesce and then re-create all your objects, constraints etc.

You can buy tools to do all this for you - at mega-bucks.

Nb: if you use the export/import approach, the easiest way is to delete the schema owner (after export) and then re-create after the coalesce and re-import. You can create tables and indexes separately before the import of data, if you need to re-size objects - but don't forget to import with ignore=y in this case. You also need to think about your export: by default the data will be exported in compress mode as well and the import will try to create tables with all the current data in a new single initial extent. This can result in the thing falling over if you don't have enough space for bigger extents if the tablespace is in multiple data files.

In brief, you have to think the whole process through carefully and test it - but then, I guess, that goes without saying!

Len

>
>hi,
>
>i've got a tablespace with a big fragmentation.
>does the "export - drop objects - import" is the unique solution ?
>
>thanx
>@nnib
>
>
Received on Sun Sep 02 2001 - 11:15:05 CDT