Re: v$kcbcbhx

Yep :)

It must've been either a pre 7.0 Release (apparently they don't have that code base available in the "central" database anymore) - or it was some sort of UK-internal spin off.

Anyway, we ran a delete on dependency$ that linked the invalid v_$ view to a no-longer existing x$ object, and were able to drop the view.

Question remains though, how the hell this thing has gotten in there in the first place. And remained there "undetected" until now :)

Stefan

On 11/17/06, Jared Still <jkstill_at_gmail.com> wrote:
>
>
> On 11/17/06, Stefan Knecht <knecht.stefan_at_gmail.com > wrote:
> >
> > Reason I ask is that we've got a 9.2.0.6 database (AIX) where we still
> > got the v_$ view, and the according public synonym for v$kcbcbhx.
> >
> >
>
> > Story's just gotten funnier... Opened an SR about this - and Oracle says
> > this "object" was never part of any Release, from 7.x up to 10.2 - even
> > the symbol kcbcbhx didn't return any match inside the Oracle code. Shrug.
> >
> > Wonder where this came from....
>
>
> Really?
>
> I wonder how they explain this script.
>
>
> --
> Jared Still
> Certifiable Oracle DBA and Part Time Perl Evangelist
>
>
> em
> Rem Copyright (C) 1989 by ORACLE Corporation (UK) Ltd.
> Rem
> Rem FILE NAME:
> Rem stat7nap.sql
> Rem
> Rem CREATED:
> Rem Chris Ellis 15-Nov-89
> Rem
> Rem NOTES.
> Rem SQL*PLUS command file to enter start values
>
> Rem of "snapshot" statistical information
> Rem
> Rem MODIFIED:
> Rem Dave Ensor 15-Mar-90
> Rem - (summary) LRU and Extended LRU Stats recorded
> Rem - Order By placed on list of Snapshots
> Rem
>
> Rem Chris Ellis 23-Mar-90
> Rem - Remove stats$files statistics
> Rem
> Rem Chris Ellis 16-Jun-90
> Rem - Set termout off for body of command file
> Rem - Display only the latest snap_id and time
>
> Rem
> Rem Chris Ellis 21-Mar-91
> Rem - Filename changed for Unix compatibility
> Rem
> Rem Dave Ensor 23-Sep-92
> Rem - Updated for Oracle 7
> Rem - Changed column names in V$LATCH
> Rem - Obselete column names removed from V$WAITSTAT & data now
>
> Rem captured unconditionally (only 10 rows per snap)
> Rem - Additional columns in V$ROLLSTAT
> Rem - Added capture of V$LIBRARYCACHE
> Rem - Added capture of instance number & startup on snap
>
> Rem
> Rem Dave Ensor 05-Oct-92
> Rem - SGA Statistics captured
> Rem
> Rem Graham Wood 07-Oct-92
> Rem - modified SGA stats capture
> Rem - added lock activity capture
> Rem
> Rem Graham Wood 12-Oct-92
>
> Rem - added in collection of STATS$SGASTATXS
>
>
> set echo off feedback off verify off termout off
>
> Rem
> Rem Create new session in snapshot control table
> Rem and establish snap_id for begin snap
>
> Rem
>
> undefine INSTANCE
> undefine STARTED
>
> col INSTANCE new_value INSTANCE
>
> select nvl(max(value),0) INSTANCE
> from v$parameter
> where name = 'instance_number';
>
> col STARTED new_value STARTED
>
>
> select to_date(JUL.VALUE, 'J')
> || to_char(SEC.VALUE/3600, '09')
> || to_char(mod(SEC.VALUE/60, 60), '09')
> || to_char(mod(SEC.VALUE, 60), '09') STARTED
> from V$INSTANCEX JUL
>
> , V$INSTANCEX SEC
> where JUL.KEY like '%JULIAN%'
> and SEC.KEY like '%SECOND%';
>
> Rem set flag indicating if we have already recorded the stats for
> Rem the SGA since instance startup
> Rem
> undef NEW_SGA
>
> col NEW_SGA new_value NEW_SGA
> select count(1) NEW_SGA
> from sys.dual
> where exists
> (select 1 from stats$sgastats
> where instance_start = to_date('&STARTED', 'dd-mon-yy hh24 mi ss'));
>
>
> insert into stats$snapshots
> ( snap_id
> , snap_time
> , snap_instance
> , instance_start )
> values
> ( snapshot_id.nextval
> , sysdate
> , &INSTANCE
> , to_date('&STARTED', 'dd-Mon-yy hh24 mi ss')
>
> );
>
> commit work;
>
> Rem
> Rem Generate snapshot entries
> Rem
>
> insert into stats$librarycaches
> select snapshot_id.currval
> , NAMESPACE
> , GETS
> , GETHITS
> , PINS
> , PINHITS
>
> , RELOADS
> , INVALIDATIONS
> from V$LIBRARYCACHE;
>
> insert into stats$latches
> select snapshot_id.currval
> ,names.name
> ,stats.level#
> ,stats.gets
>
> ,stats.misses
> ,stats.sleeps
> ,stats.immediate_gets
> ,stats.immediate_misses
> from v$latch stats
> ,v$latchname names
> where stats.latch# = names.latch#;
>
> rem We'd like 20 groups reported, so calculate how many buffers
>
> rem should be in each group. The last group may be shorter!
>
> rem Can't use .CURRVAL with a GROUP BY
> col snapid noprint new_value snapid
>
> col granule noprint new_value granule
> col maxbuf noprint new_value maxbuf
>
>
> select snapshot_id.currval snapid
> ,ceil(value / 20) granule
> ,value maxbuf
> from v$parameter
> where name = 'db_block_buffers';
>
> insert into stats$lrustats
> select &snapid
> ,'C'
>
> ,&granule * trunc(indx / &granule) + 1
> ,least(&granule * (trunc(indx / &granule) + 1), &maxbuf)
> ,sum(count)
> from v$*
> kcbcbhx*
> where indx > 0
> group by trunc(indx / &granule);
>
> select ceil(value / 20) granule
> ,value maxbuf
> from v$parameter
> where name = 'db_block_lru_extended_statistics';
>
> insert into stats$lrustats
>
> select &snapid
> ,'R'
> ,&granule * trunc(indx / &granule) + 1
> ,least(&granule * (trunc(indx / &granule) + 1), &maxbuf)
> ,sum(count)
> from v$kcbrbhx
> where indx > 0
>
> group by trunc(indx / &granule);
>
> insert into stats$rollstats
> select snapshot_id.currval
> ,usn
> ,extents
> ,rssize
> ,writes
> ,xacts
> ,gets
> ,waits
>
> ,optsize
> ,hwmsize
> ,shrinks
> ,wraps
> ,extends
> ,aveshrink
> ,aveactive
> from v$rollstat;
>
> insert into stats$rowcaches
> select &snapid
> ,cache#
>
> ,parameter
> ,sum(count)
> ,sum(usage)
> ,sum(gets)
> ,sum(getmisses)
> ,sum(scans)
> ,sum(scanmisses)
> ,sum(scancompletes)
> ,sum(modifications)
> ,sum(flushes)
>
> from v$rowcache
> group by cache#, parameter;
>
> insert into stats$sgastats
> ( instance
> , instance_start
> , stat_type
> , name
> , c_value
> )
> select &INSTANCE
>
> ,to_date('&STARTED', 'dd-Mon-yy hh24 mi ss')
> , 1
> , name
> , value
> from v$parameter
> where &NEW_SGA = 0;
>
> insert into stats$sgastats
> ( instance
> , instance_start
>
> , stat_type
> , name
> , n_value
> )
> select &INSTANCE
> ,to_date('&STARTED', 'dd-Mon-yy hh24 mi ss')
> , 2
> , name
> , value
> from v$sga
> where &NEW_SGA = 0;
>
>
> insert into stats$sgastatxs
> ( snap_id
> , name
> , entry_no
> , value
> )
> select snapshot_id.currval
> , name
> , rownum
> , bytes
> from v$sgastatx;
>
>
> insert into stats$sysstats
> select snapshot_id.currval
> ,name
> ,value
> from v$sysstat;
>
> insert into stats$waitstats
> select snapshot_id.currval
> ,class
> ,count
> ,time
>
> from v$waitstatx;
>
> insert into stats$lockactivity
> select snapshot_id.currval
> ,indx
> ,"count"
> from v$le_statx;
>
> commit work;
> set termout on
> col snap_time format a20 heading 'Snapshot start time'
>
> col snap_instance heading 'Instance'
> col startup format a20 heading 'Instance start time'
>
> select snap_id
> ,to_char(snap_time, 'dd-Mon-yyyy hh24:mi:ss') snap_time
> ,snap_instance
>
> ,to_char(instance_start, 'dd-Mon-yyyy hh24:mi:ss') startup
> from stats$snapshots
> where snap_id= &snapid;
>
>
>
> Rem End of stat7nap.sq
>
>
>

--
http://www.freelists.org/webpage/oracle-l