Re: 12C - CDB$VIEW

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_at_gmail.com>
To: Ric Van Dyke <ric.van.dyke_at_hotsos.com> 
Cc: ORACLE-L <oracle-l_at_freelists.org> 
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_at_hotsos.com> 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_at_freelists.org [mailto:oracle-l-bounce@freelists.org] 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 

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