RE: Consolidated Database Size in OEM

From: Mark W. Farnham <mwf_at_rsiz.com>
Date: Sun, 16 Dec 2018 08:27:50 -0500
Message-ID: <028801d49543$24691180$6d3b3480$_at_rsiz.com>



“What do you need a repository for?”  

A few decades ago I was pushing the notion that we should have “A datawarehouse FOR the DBA” to keep track of this and a lot of other things. I still think that is true and that beating up the production database to get that information more than a few times a day is a silly idea (including for ASH analysis, etc.). Why would you want to spend production class storage and cpu license time with the dba competing for cycles with users from analysts up through the CFO? But I’m officially an antique.  

Nevertheless:  

Some folks used to find “extmon” and “exthist” useful, not only because they kept a time series of extents back in the day when maximum extents was small and related to block size, but also because the time series aggregate analysis could be useful in planning (and justifying) capital acquisition of storage.  

I still think that is useful, but since I never wrapped it sales dried up, and I had better uses of my time than maintaining it for free, so it is out of date.  

In 1995 it looked something like this, and reporting the sizes in K might make you chuckle. Even then I used to say “Disk is cheap” but the cost has dropped orders of magnitude since then. And of course you had to use the associated script that iterated through all the databases you cared about on all the machines you cared about, because OEM didn’t exist yet.  

mwf  

nothing after this except out of date script text….  

more *.sql | cat  

::::::::::::::

all_in_k.sql

::::::::::::::

column owner format a8

column segment_name format a30

column segment_type hea segtype

column extents format 990 hea ext

column bytes/1024 format 9,999,990

set pagesize 60 newpage 0

break on owner skip 1 on segment_name on segment_type

select owner,segment_name,segment_type,as_of_date,extents,bytes/1024 "KBYTES"

from extent_history

order by owner,segment_name,as_of_date

/

::::::::::::::

c_extent_history.sql

::::::::::::::

/*

   Copyright (C) 1994 Rightsizing, Inc.  

   c_extent_history.sql  

   Run this script one time as the "UTIL" user defined

   by c_util.sql to initialize the baseline history.

*/

create table extent_history

   (

   owner varchar2(30),

   segment_name varchar2(81),

   segment_type varchar2( 7),

   as_of_date date,

   object_id number,

   extents number,

   bytes number

   )

   tablespace &&data_tablespace

   storage (initial 1200K next 1200K pctincrease 0 maxextents 200);

create unique index extent_history_u1

   on extent_history (owner,segment_name,segment_type,as_of_date,object_id)

   tablespace &&index_tablespace

   storage (initial 1200K next 1200K pctincrease 0 maxextents 200);

create table extent_current

   (

   owner varchar2(30),

   segment_name varchar2(81),

   segment_type varchar2( 7),

   as_of_date date,

   object_id number,

   extents number,

   bytes number

   )

   tablespace &&data_tablespace

   storage (initial 1200K next 1200K pctincrease 0 maxextents 200);

create unique index extent_current_u1

   on extent_current (owner,segment_name,segment_type,as_of_date,object_id)

   tablespace &&index_tablespace

   storage (initial 1200K next 1200K pctincrease 0 maxextents 200);

create table extent_changed

   (

   owner varchar2(30),

   segment_name varchar2(81),

   segment_type varchar2( 7),

   as_of_date date,

   object_id number,

   extents number,

   bytes number

   )

   tablespace &&data_tablespace

   storage (initial 1200K next 1200K pctincrease 0 maxextents 200);

create unique index extent_changed_u1

   on extent_changed (owner,segment_name,segment_type,as_of_date,object_id)

   tablespace &&index_tablespace

   storage (initial 1200K next 1200K pctincrease 0 maxextents 200);

exit;

::::::::::::::

c_util.sql

::::::::::::::

/*

   Copyright (C) 1994 Rightsizing, Inc.  

   c_util.sql -- Run this one time from a DBA account

   to define the "UTIL" user.

*/

grant connect to &&util identified by &&utilpw;

alter user &&util

   default tablespace &&default_space

   temporary tablespace &&temporary_space

   quota unlimited on tools;

grant monitorer to &&util;

/*

grant select on obj$ to &&util;

grant select on user$ to &&util;

gr

exit;

::::::::::::::

df.sql

::::::::::::::

set linesize 100;

set pagesize 60;

column owner format a10

column segment_name format a30

column segment_type format a8 hea seg_type

column tablespace_name format a10 hea tablespace

column file_name format a20

column file_id format 9999990

select * from sys.dba_data_files;

::::::::::::::

diff2.sql

::::::::::::::

/* Copyright (C) 1994 Rightsizing, Inc.  

   diff_extent_history  

   Run this script as the "UTIL" user defined

   by c_util to d1024

from extent_changed c, extent_history h

where c.owner = h.owner

  and c.segment_name = h.segment_name

  and c.segment_type = h.segment_type

order by h.owner,h.segment_name,h.as_of_date;

exit;

::::::::::::::

diff_extent_history.sql

::::::::::::::

/* Copyright (C) 1994 Rightsizing, Inc.  

   diff_extent_history  

   Run this script as the "UTIL" user defined

   by c_util to display the history of extents

   that have recently changed.  

*/

set linesize 132;

set pagesize 60;

br

::::::::::::::

exts.sql

::::::::::::::

set linesize 100;

set pagesize 60;

column owner format a10

column segment_name format a30

column segment_type format a8 hea seg_type

column tablespace_name format a10 hea tablespace

compute sum of sum(blocks) on segment_type

compute sum of sum(blocks) on report

compute sum of sum(blocks) on tablespace_name

compute sum of sum(blocks) on owner

break on segment_type on tablespace_name on owner on report

select owner,tablespace_name,segment_type,segment_namun before this

   script.

*/

insert into extent_history

   select

      u.name,

      o.name,

      so.object_type,

      SYSDATE,       o.obj#,

      count(*),

      sum(e.length*ts.blocksize)

   from

      sys.user$ u,

      sys.obj$ o,

      sys.sys_objects so,

      sys.ts$ ts,

      sys.seg$ s,

      sys.uet$ e

   where o.obj# = so.object_id

     and o.owner# = u.user#

     and o.type = so.object_type_id

     and s.type = so.segmentner on report

select owner,tablespace_name,segment_type,segment_name,sum(blocks),count(*)

   from sys.dba_extents

   group by owner,tablespace_name,segment_type,segment_name;

::::::::::::::

some_in_k.sql

::::::::::::::

column owner format a8

column segment_name format a30

column segment_type hea segtype

column extents format 990 hea ext

column bytes/1024 format 9,999,990

set pagesize 60 newpage 0

break on owner skip 1 on segment_name on segment_type

select owner,segment_name,segmrrent_u1;

truncate table extent_current reuse storage;

insert into extent_current

   select

      u.name,

      o.name,

      so.object_type,

      SYSDATE,       o.obj#,

      count(*),

      sum(e.length*ts.blocksize)

   from

      sys.user$ u,

      sys.obj$ o,

      sys.sys_objects so,

      sys.ts$ ts,

      sys.seg$ s,

      sys.uet$ e

   where o.obj# = so.object_id

     and o.owner# = u.user#

     and o.type = so.object_type_id

     a so.segment_type_id

     and s.ts# = ts.ts#

     and s.file# = so.header_file

     and s.block# = so.header_block

     and e.segfile# = so.header_file

     and e.segblock# = so.header_block

   group by u.name,o.name,so.object_type,SYSDATE,o.obj#;

commit;

create unique index extent_current_u1

   on extent_current (owner,segment_name,segment_type,as_of_date,object_id)

   tablespace TOOLS

   storage (initial 1200K next 1200K pctincrease 0 maxextents 200);

drop index extent_changed_u1;

truncate table extent_changed reuse storage;

/*

   Get current rows that differ in bytes or extents from the latest

   row in recorded history

*/

insert into extent_changed

   select * from extent_current c

   where (owner, segment_name, segment_type) in

      (select h.owner, h.segment_name, h.segment_type

         from extent_history h

         where c.owner = h.owner

           and c.segment_name = h.segment_name

           and c.segment_type = h.segment_type

           and

             ( c.extents != h.extents or

               c.bytes != h.bytes

             )

           and (h.owner, h.segment_name, h.segment_type, h.as_of_date) in

            (select owner, segment_name, segment_type, max(as_of_date)

                from extent_history

                where owner = h.owner

                  and segment_name = h.segment_name

                  and segment_type = h.segment_type

                group by owner, segment_name, segment_type

            )

       )ease 0 maxextents 200);

insert into extent_history

   select * from extent_changed;

commit;

exit;

::::::::::::::

update_extent_history.sql

::::::::::::::

/*

   Copyright (C) 1994 Rightsizing, Inc.  

   update_extent_history.sql  

   Run this script according to your schedule as the

   "UTIL" user defined by c_util.sql.  

   The scripts c_extent_history and load_extent_history

   must be run one time before this script is valid.

*/

set echo on;

drop index extent_current_u1;

truncate tab.file# = so.header_file

     and s.block# = so.header_block

     and e.segfile# = so.header_file

     and e.segblock# = so.header_block

   group by u.name,o.name,so.object_type,SYSDATE,o.obj#;

commit;

create unique index extent_current_u1

   on extent_current (owner,segment_name,segment_type,as_of_date,object_id)

   tablespace TOOLS

   storage (initial 1200K next 1200K pctincrease 0 maxextents 200);

drop index extent_changed_u1;

truncate table extent_changed reuse storage;

/*

   Get current r

               c.extents != h.extents or

               c.bytes != h.bytes

             )

           and (h.owner, h.segment_name, h.segment_type, h.as_of_date) in

            (select owner, segment_name, segment_type, max(as_of_date)

                from extent_history

                where owner = h.owner

                  and segment_name = h.segment_name

                  and segment_type = h.segment_type

                group by owner, segment_name, segment_type

            )

  • h.segment_type (+)

           and h.owner is null

      );

commit;    

create unique index extent_changed_u1

   on extent_changed (owner,segment_name,segment_type,as_of_date,object_id)

   tablespace TOOLS

   storage (initial 1200K next 1200K pctincrease 0 maxextents 200);

insert into extent_history

   select * from extent_changed;

commit;

exit;    

From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Mladen Gogala Sent: Saturday, December 15, 2018 11:37 PM To: oracle-l_at_freelists.org
Subject: Re: Consolidated Database Size in OEM  

What do you need repository for? Usually, it's done from the database itself:

SQL> set sqlformat ansiconsole
SQL> select con_id,sum(bytes)/1048575 MB   2 from cdb_data_files
  3 group by rollup(con_id);

CON_ID  MB                                         
1       1970.001878740195026583696922013208401879  
4       905.000863076079441146317621533986600863   
        2875.002741816274467730014543547195002742  

There may be some problems with executing this on a 11G database. However, in approximately 2 weeks, Oracle 11G will die. Extended support will no longer be available for free. If there is something strange in your database, who you gonna call? Definitely not Oracle.

Regards

On 12/15/18 7:23 PM, Nagaraj chk wrote:

Hi Gurus ,  

I am looking for a query to get the database size from OEM Repository database. Please help if anyone of you have query in handy.  

-Naga

-- 
Mladen Gogala
Database Consultant
Tel: (347) 321-1217


--
http://www.freelists.org/webpage/oracle-l
Received on Sun Dec 16 2018 - 14:27:50 CET

Original text of this message