RE: UTLBSTAT/UTLESTAT=ORA-00942

From: Steve Adams <steve.adams_at_ixora.com.au>
Date: Fri, 10 Nov 2000 09:38:53 +1000
Message-Id: <10675.121661@fatcity.com>

Hi Larry,

The SQL statements to create the stats$ temporary tables from which the report is generated have failed. If you move the 'spool report.txt' command to the top of the script, it will no doubt catch the error. Maybe you are short of space in the SYSTEM tablespace?

@ Regards,
@ Steve Adams
@ http://www.ixora.com.au/
@ http://www.christianity.net.au/

-----Original Message-----
From: Larry Taylor [mailto:ltaylor_at_iq.com] Sent: Friday, 10 November 2000 7:57
To: Multiple recipients of list ORACLE-L Subject: UTLBSTAT/UTLESTAT=ORA-00942

Hi All,

Report.txt was generated on 8.1.6. and here is what i have done:

1. Alter system set timed_statistics=TRUE
2. utlbstat.sql
3. utestat.sql

Does anyone have any ideas as to why my report.txt has so many

   ( ORA-00942: table or view does not exist )

SVRMGR> select namespace library,
     2>        gets,
     3>        round(decode(gethits,0,1,gethits)/decode(gets,0,1,gets),3)
     4>           gethitratio,
     5>        pins,
     6>        round(decode(pinhits,0,1,pinhits)/decode(pins,0,1,pins),3)
     7>           pinhitratio,
     8>        reloads, invalidations
     9>   from stats$lib;
  from stats$lib
       *

ORA-00942: table or view does not exist

SVRMGR> select n1.name "Statistic",
     2>        n1.change "Total",
     3>        round(n1.change/trans.change,2) "Per Transaction",
     4>        round(n1.change/((start_users + end_users)/2),2)  "Per
Logon",
     5>        round(n1.change/(to_number(to_char(end_time,   'J'))*60*60*24
-
     6>                         to_number(to_char(start_time, 'J'))*60*60*24
+
     7>                         to_number(to_char(end_time,   'SSSSS')) -
     8>                         to_number(to_char(start_time, 'SSSSS')))
     9>              , 2) "Per Second"
    10>    from
    11>                 stats$stats n1,
    12>                 stats$stats trans,
    13>                 stats$dates
    14>    where
    15>          trans.name='user commits'
    16>     and  n1.change != 0
    17>    order by n1.name;
                stats$stats trans,

  *
ORA-00942: table or view does not exist

SVRMGR>
SVRMGR> select queue.change/writes.change "Average Write Queue Length"
     2>   from stats$stats queue, stats$stats writes
     3>  where queue.name  = 'summed dirty queue length'
     4>   and  writes.name = 'write requests';
  from stats$stats queue, stats$stats writes
                          *

ORA-00942: table or view does not exist

SVRMGR> select  n1.event "Event Name",
     2>         n1.event_count "Count",
     3>         n1.time_waited "Total Time",
     4>         round(n1.time_waited/n1.event_count, 2) "Avg Time"
     5>    from stats$event n1
     6>    where n1.event_count > 0
     7>    order by n1.time_waited desc;
   from stats$event n1
        *

ORA-00942: table or view does not exist

SVRMGR> select  n1.event "Event Name",
     2>         n1.event_count "Count",
     3>         n1.time_waited "Total Time",
     4>         round(n1.time_waited/n1.event_count, 2) "Avg Time"
     5>    from stats$bck_event n1
     6>    where n1.event_count > 0
     7>    order by n1.time_waited desc;
   from stats$bck_event n1
        *

ORA-00942: table or view does not exist

SVRMGR>
SVRMGR> select name latch_name, gets, misses,
     2>     round((gets-misses)/decode(gets,0,1,gets),3)
     3>       hit_ratio,
     4>     sleeps,
     5>     round(sleeps/decode(misses,0,1,misses),3) "SLEEPS/MISS"
     6>    from stats$latches
     7>     where gets != 0
     8>     order by name;
   from stats$latches
        *

ORA-00942: table or view does not exist

SVRMGR>
SVRMGR> select name latch_name,
     2>     immed_gets nowait_gets,
     3>     immed_miss nowait_misses,
     4>     round((immed_gets/(immed_gets+immed_miss)), 3)
     5>       nowait_hit_ratio
     6>    from stats$latches
     7>     where immed_gets + immed_miss != 0
     8>     order by name;
   from stats$latches
        *

ORA-00942: table or view does not exist

Guess you have the picture, sorry for so much information.

Have any ideas?

thank you very much.

--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Larry Taylor
  INET: ltaylor_at_iq.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may

Received on Thu Nov 09 2000 - 17:38:53 CST