Re: 12C - CDB$VIEW

From: Stojan Veselinovski <stojan.veselinovski_at_gmail.com>
Date: Thu, 6 Feb 2014 11:55:09 +1100
Message-ID: <CALn1tDvJAHoOdW=YZAhR2ACrXoLuCyAAgVEAX8r7vkikdBrJ_w_at_mail.gmail.com>

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_at_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

Received on Thu Feb 06 2014 - 01:55:09 CET