Re: Analyze /LMT Question .....

Mahesh Hardikar wrote:
> Hello all ,
>
> I am a bit new to Oracle. We are having Oracle EE 8.1.7.1 on HP-UX 11
> We do index rebuild & analyze table compute statistics weekly.
>
> Do I need to recompile all procedures after analyze in order touse
> fresh statistics ? Or they will get automatically recompiled upon next
> execution ?
>

Not required

> Is there anything else I need to do ?
>
> One more question :
>
> We have all tablespaces as DICTIONARY managed. I wish to turn them
> (except SYSTEM) LMT's with Uniform Extent Size. I read about
> dmbs_space_admin.tablespace_migrate_to_local. But will it reorganise
> existing data as per new storage settings or affect only future
> allocation ?
> On Test , I saw it converted tablespace to LMT but made it
> AUTOALLOCATE . How can I make it UNIFORM & specify size ?
>

tablespace_migrate_to_local only builds appropriate bitmap to map the existing extents, it doesn't reorganize the data. besides, you might still end up in fragmentation. so personally i would create a new LMT tablespace with uniform extents size and use alter table move to move objects (remember to rebuild associated indexes too) from dictionary to LMT. this way you get the full benifits of LMT.

> Will it be advisable to export schema , drop user , convert tablespace
> to LMT (or drop existing TS & create new LMT with specified size , run
> DDL's for Tables ) & then do Import ?
>
> Any help is kindly appreciated ...
>
> Thanks for reading such a long post ...
>
> Regards ,
> Mahesh Hardikar

-Stan Received on Thu Feb 20 2003 - 00:25:43 CST