/*
 * Title  : sho_keep_obj.sql
 * Author : EXADIME LLC
 * Created: 29-Jul-2017
 * Updated: 29-Jul-2017
 * Release: 1.0.0
 * Purpose: This script shows objects in k/p.
 * History: N/A
 */

start oracle.ini

set echo off
set feedback off
set serverout on
set termout on

set linesize 92
set pagesize 100
set pause off

set heading on
set headsep on
set underline on
set colsep " "

set define on
set verify off
set escape off
set embedded on

column c0 heading "Owner"                                    format a12
column c1 heading "Object|Name"                              format a30
column c2 heading "Object|Type"                              format a8
column c3 heading "Number of|Blocks in|Buffer|Cache"         format 99,999,999
column c4 heading "Percentage|of object|blocks in|Buffer"    format 999
column c5 heading "Buffer|Pool"                              format a7
column c6 heading "Block|Size"                               format 99,999

ttitle 'Contents of Keep Buffers'

spool &&rpt_dir/sho_bc_obj.rpt

select
	t.owner							c0,
	object_name						c1,
case 
	when object_type = 'TABLE PARTITION' then 'TAB PART'
	when object_type = 'INDEX PARTITION' then 'IDX PART'
	else object_type 
end								c2,
	sum(num_blocks)						c3,
	(sum(num_blocks)/greatest(sum(blocks), .001))*100	c4,
	buffer_pool						c5,
	sum(bytes)/sum(blocks)					c6
from
	(
		select
		   o.owner					owner,
		   o.object_name				object_name,
		   o.subobject_name				subobject_name,
		   o.object_type				object_type,
		   count(distinct file# || block#)		num_blocks
		from
		   dba_objects					o,
		   v$bh						bh
		where
		   o.data_object_id  = bh.objd
		and
		   o.owner not in ('SYS','SYSTEM')
		and
		   bh.status != 'free'
		group by
		   o.owner,
		   o.object_name,
		   o.subobject_name,
		   o.object_type
		order by
		   count(distinct file# || block#) desc
	) t,
	dba_segments s
where
	s.segment_name = t.object_name
and
	s.owner = t.owner
and
	s.segment_type = t.object_type
and
	nvl(s.partition_name,'-') = nvl(t.subobject_name,'-')
and	buffer_pool='KEEP'
group by
	t.owner,
	object_name,
	object_type,
	buffer_pool
having
	sum(num_blocks) > 10
order by
	sum(num_blocks) desc
;

spool off

ttitle off
clear columns

set embedded off
set escape off
set verify on
set define on

set colsep " "
set underline on
set headsep on
set heading on

set pause off
set pagesize 24
set linesize 80

set termout on
set serverout off
set feedback on
set echo off
