Re: Monitoring automatic analysis of stale tables

Rick Denoire wrote:
>
> OK, I found that using the same
> DBMS_STATS.GATHER_SCHEMA_STATS
> procedure for empty and stale tables will not only calculate
> statistics, but also returns the name of the objects analyzed.
> I just can't handle the syntax, I suppose this is a trivial question
> for you:
>
> Take a look at this:
>
> DBMS_STATS.GATHER_SCHEMA_STATS (
> ownname VARCHAR2,
> estimate_percent NUMBER DEFAULT NULL,
> block_sample BOOLEAN DEFAULT FALSE,
> method_opt VARCHAR2 DEFAULT 'FOR ALL COLUMNS SIZE 1',
> degree NUMBER DEFAULT NULL,
> granularity VARCHAR2 DEFAULT 'DEFAULT',
> cascade BOOLEAN DEFAULT FALSE,
> stattab VARCHAR2 DEFAULT NULL,
> statid VARCHAR2 DEFAULT NULL,
> options VARCHAR2 DEFAULT 'GATHER',
> objlist OUT ObjectTab,
> statown VARCHAR2 DEFAULT NULL);
>
> So I use:
> dbms_stats.gather_schema_stats(ownname->"SCOTT",options->"GATHER
> STALE")
> I must admit that I am a little bit helpless at this point. I have no
> clue about how to output the names of the analyzed tables supposedly
> contained in objlist (this is more a question about how to deal with
> DBMS packages returning values, sorry for my ignorance).
>
> I would still appreciate your help - I am still learning.
>
> Bye
> Rick Denoire

ObjTab is a plsql collection (they are documented in the plsql reference). Basically it returns array of object names so you would process them along the lines of:

begin
  for i in 1 .. objlist.count loop

• something with objlist(i) end loop; end;

etc

hth
connor

-- 
=========================
Connor McDonald
http://www.oracledba.co.uk

"Some days you're the pigeon, some days you're the statue"