Re: change uniform size in LMT

goforticket_at_yahoo.com (Linda Lee) wrote in message news:<f901fb80.0205152024.16cb53ea_at_posting.google.com>...
> I want to change the uniform size (extent size) from 5M to 3M in a LMT
> tablespace. Is there an easy way to do it?
>
> If I have to use export/drop/create/import,
>
> 1. should I choose "transport_tablespace=y", "direct=y" and
> "compress=N"?
>
> 2. some tables in this tablespace have indexes built in other
> tablespaces, should I rebuild the indexes?
>
> 3. do I need to recompile triggers, packages? and how?
>
> Thanks a lot.

The uniform extent size is set at tablespace creation and can only be changed by dropping and re-creating the tablespace. The easiest way to make this change might be, if you have the disk space, to create a new tablespace with the desired extent size, then use alter table move to relocate the table, and then do an alter index rebuild for all the indexes on the relocated table. Oracle will take care of the grants and triggers for you using this method.

Hopefully since the object structure has not changed Oracle does not invalidate referencing packages but if it does then after performing the moves you can run the $ORACLE_HOME/rdbms/admin/utlrp package to revalidate any invalid packages. The name of the version 8 utlrp script might have changed in version 9, but an equivilent script exists. You can always use the SQL statement "alter package owner.packagename compile" or "compilbe body" to manually recompile but then you have to know the dependency order.

HTH -- Mark D Powell -- Received on Thu May 16 2002 - 08:13:17 CDT