Re: 12C - CDB$VIEW
- From: John Hurley <hurleyjohnb@xxxxxxxxx>
- To: "stojan.veselinovski@xxxxxxxxx" <stojan.veselinovski@xxxxxxxxx>, Ric Van Dyke <ric.van.dyke@xxxxxxxxxx>
- Date: Wed, 5 Feb 2014 17:10:58 -0800 (PST)
There are certainly some buggy looking results being returned in 12.1 when you
run queries against many of the oracle views in a PDB/CDB environment.
I have a presentation at Hotsos 2014 where I detour briefly to look at some 12c
things and point out some problems in querying v$sgastat and v$db_object_cache
in those environments. Bottom line to me at this point "early ish" code and
hopefully all these things get fixed eventually.
Thanks for pointing this out!
________________________________
From: Stojan Veselinovski <stojan.veselinovski@xxxxxxxxx>
To: Ric Van Dyke <ric.van.dyke@xxxxxxxxxx>
Cc: ORACLE-L <oracle-l@xxxxxxxxxxxxx>
Sent: Wednesday, February 5, 2014 7:55 PM
Subject: Re: 12C - CDB$VIEW
Hi Ric,
I've blogged about here - http://www.stojanveselinovski.com/blog/?p=101
Query is a little complex. Here it is:
with b_objs as (
select
cdbo.con_id,
cdbo.owner owner,
cdbo.object_name object_name,
cdbo.subobject_name subobject_name,
cdbo.object_type object_type,
count(distinct file# || block#) num_blocks
from
cdb_objects cdbo,
v$bh bh
where cdbo.con_id = bh.con_id
and cdbo.data_object_id = bh.objd
and cdbo.owner not in ('SYS','SYSTEM')
and bh.status != 'free'
group by
cdbo.con_id,
cdbo.owner,
cdbo.object_name,
cdbo.subobject_name,
cdbo.object_type )
select count(*)
from (
select
bo.con_id con_id,
bo.owner,
object_name,
object_type,
sum(num_blocks) total_blocks,
(sum(num_blocks)*ct.block_size)/1024/1024 total_size_mb,
--(select block_size from cdb_tablespaces ct where ct.con_id = cs.con_id and
ct.tablespace_name = cs.tablespace_name) block_size, -- If I don't have this in
the query then it returns no rows. subselect working, but not join
--(select 1 from cdb$view(sys.dual) where rownum = 1) dummy, -- THIS IS IT,
UNCOMMENT IT OUT and IT WORKS
buffer_pool
from
b_objs bo
,cdb_segments cs
,cdb_tablespaces ct
where cs.con_id = bo.con_id
and cs.segment_name = bo.object_name
and cs.owner = bo.owner
and cs.segment_type = bo.object_type
and nvl(cs.partition_name,'X') = nvl(bo.subobject_name,'X')
and cs.con_id = ct.con_id
and cs.tablespace_name = ct.tablespace_name
group by
cs.con_id,
cs.tablespace_name,
bo.con_id,
bo.owner,
ct.block_size,
object_name,
object_type,
buffer_pool
order by
sum(num_blocks) desc
);
Regards,
Stojan
www.stojanveselinovski.com/blog
On Thu, Feb 6, 2014 at 2:50 AM, Ric Van Dyke <ric.van.dyke@xxxxxxxxxx> wrote:
Looks to have all the trappings of an internal function. Likely something the
folks at Oracle don’t intend for us to use directly, so good luck getting more
info on it.
>
>What is the query? I just wrote a simple query on the two and it worked just
>fine.
>
>
>+--+--+--+--+--+--+--+--+--+--+--+--+--+--+
>Ric Van Dyke
>Education Director
>Hotsos Ltd.
>
>Hotsos Symposium March 2-6 2014
>Make your plans to be there now!
>
>
>From:oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On
>Behalf Of Stojan Veselinovski
>Sent: Tuesday, February 04, 2014 11:51 PM
>To: ORACLE-L
>Subject: 12C - CDB$VIEW
>
>Hi All,
>
>Anyone know anything about this function in 12c?
>
>I have an open SR in relation to it returning no data in an sql joining
>cdb_tablespaces and cdb_segments .
>
>I've put some of my findings about it here
>http://www.stojanveselinovski.com/blog/?p=87
>
>Regards,
>
>Stojan
>
>http://www.stojanveselinovski.com/blog
Other related posts:
