Re: Analyze /LMT Question .....

Comments below:

"Mahesh Hardikar" <hardikarm_at_yahoo.com> wrote in message news:4a1c57c2.0302192127.35df46a2_at_posting.google.com...
> 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.
Why rebuild weekly? index rebuild should be done when really necessary.

>
> Do I need to recompile all procedures after analyze in order touse
> fresh statistics ? Or they will get automatically recompiled upon next
> execution ?

No. They don't get invalid after an analyze.

>
> Is there anything else I need to do ?

Depends ... you could read the manuals .. watch cricket ;) Apart from that .. nothing ..

>
> 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 ?

system anyway cannot be turned to LMT on 8i. First I'd advise you to patch to 8.1.7.4. The dbms_space_admin ..... package just goes and creates the bitmap. It does not do anything else ... no reorganizing! Why do you want to reorganize anyway, especially if you are converting the tablespaces to LMT?

> On Test , I saw it converted tablespace to LMT but made it
> AUTOALLOCATE . How can I make it UNIFORM & specify size ?
>
> 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 ?

Choose your medicine. export import will be slower than running the above package. Of course do your homework from metalink and see if there are any bugs you can hit while running that package. I personally prefer doing it using export import if I can afford downtime (no solid reason for doing it that way though).

>
> Any help is kindly appreciated ...
>
> Thanks for reading such a long post ...
>
> Regards ,
> Mahesh Hardikar
Received on Wed Feb 19 2003 - 23:52:17 CST