rem 
rem $Header: prvtpool.sql 7010300.1 94/02/24 18:23:29 snataraj Generic<base> $ 
rem 
Rem  Copyright (c) 1991 by Oracle Corporation 
Rem    NAME
Rem      prvtpool.sql - dbms_shared_pool utility package (private).
Rem    DESCRIPTION
Rem      This package allows you to display the sizes of objects in the 
Rem      shared pool, and mark them for keeping or unkeeping in order to
Rem      reduce memory fragmentation.
Rem    RETURNS
Rem 
Rem    NOTES
Rem    MODIFIED   (MM/DD/YY)
Rem     adowning   02/23/94 -  Branch_for_patch
Rem     adowning   02/23/94 -  Creation
Rem     ajasuja    01/06/94 -  merge changes from branch 1.1.312.1
Rem     rkooi      04/20/93 -  change psdkeep to psdkep 
Rem     ajasuja    11/05/93 -  handle UNIX addresses
Rem     rkooi      12/08/92 -  Creation 

Rem DBA_KEEPSIZES - size that will be keeped for a pl/sql object
Rem   For instance, do:
Rem     select * from dba_keepsizes where totsize>30 and owner='SCOTT'
Rem     order by totsize desc;
Rem   to see all PL/SQL objects owned by SCOTT which are larger than 30Kbytes
create or replace view dba_keepsizes (totsize, owner, name) as
  select trunc((sum(parsed_size)+sum(code_size))/1000),
         owner, name
  from dba_object_size
  where type in ('PACKAGE', 'PROCEDURE','FUNCTION','PACKAGE BODY')
  group by owner, name;

create or replace package body dbms_shared_pool is
  procedure psdkep(schema varchar2, name varchar2, cursor_addr raw,
                   cursor_hash binary_integer, keep boolean);
  pragma interface(C, psdkep);
  
  procedure doit(name varchar2, keep boolean, flag char) is
    owner      varchar2(30);
    part1      varchar2(30);
    part2      varchar2(30);
    dblink     varchar2(30);
    part1_type number;
    objno      number;
  begin
    if flag <> 'P' and flag <> 'p' then
      psdkep('', '', hextoraw(substr(name,1,8)),
	     to_number(substr(name,10)), keep);
    else
      dbms_utility.name_resolve(name, 1, owner, part1, part2, dblink,
                                part1_type, objno);
      psdkep(owner, part1, null, null, keep);
    end if;
  end;

  procedure keep(name varchar2, flag char) is
  begin
    doit(name, TRUE, flag);
  end;

  procedure unkeep(name varchar2, flag char) is
  begin
    doit(name, FALSE, flag);
  end;

  procedure sizes(minsize number) is
    cursor c1(msize number) is
        select to_char(sharable_mem/1000, '999999') sz,
               decode(kept_versions, 0, '      ',
                 rpad('YES(' || to_char(kept_versions) || ')', 6)) keeped,
               rawtohex(address) || ',' || to_char(hash_value) name,
               substr(sql_text,1,354) extra
        from v$sqlarea
        where sharable_mem > msize*1000
      union
        select to_char(sharable_mem/1000, '999999') sz,
               decode(kept, 'YES', 'YES   ', '      ') keeped,
               owner || '.' || name || lpad(' ', 29 - (length(owner) +
                 length(name))) || '(' || type || ')' name,
               null extra
        from v$db_object_cache v
        where sharable_mem > msize*1000
      order by 1 desc;
    l number;
    i number;
  begin
   dbms_output.put_line('SIZE(K) KEPT   NAME');
   dbms_output.put_line('------- ------ ---------------------------------------------------------------');
    for rec in c1(minsize) loop
      if substr(rec.name,1,1) in ('0','1','2','3','4','5','6','7','8','9') then
        l := length(rec.extra);
        i := 0;
        while i < l loop
          if i = 0 then
            dbms_output.put_line(rec.sz || ' ' || rec.keeped || ' ' ||
                                 substr(rec.extra,i,63));
            i := i + 63;
          else
            dbms_output.put_line('                   ' ||
                                 substr(rec.extra,i,59));
            i := i + 59;
          end if;
        end loop;
        dbms_output.put_line(rpad('                   (' || rec.name ||
                             ')', 45) || '(CURSOR)');
      else
        dbms_output.put_line(rec.sz || ' ' || rec.keeped || ' ' || rec.name);
      end if;
    end loop;
  end;
end;
/

