> select kcbwbpd.bp_name,
> o.name,
> count(*) BLOCKS
> from sys.x$kcbwds kcbwds,
> sys.x$kcbwbpd kcbwbpd,
> sys.x$bh bh, sys.obj$ o
> Where kcbwds.set_id >= kcbwbpd.bp_lo_sid
> and kcbwds.set_id <= kcbwbpd.bp_hi_sid
> and kcbwbpd.bp_size != 0
> and bh.indx between start_buf# and end_buf#
> and o.dataobj# = bh.obj
> and bh.state !=0
> and o.owner# !=0
> and bp_name like upper('%K%')
> group by kcbwbpd.bp_name, o.name
>
> but it does not work on 9i
> I got the following error,
> ERROR at line 10:
> ORA-00904: "END_BUF#": invalid identifier
>
I'm looking around in my Oracle i9 data dictionary, and I don't see
any mention of this end_buf# column. From your script, I suspect it
used to be in x$bh in Oracle 8i. That's the kind of table (x$bh) which
has many changes from version to version. You will need to see what
replaced this end_buf# in 9i.
Daniel
Received on Thu Aug 29 2002 - 17:32:00 CDT
