RE: Consolidated Database Size in OEM
Date: Sun, 16 Dec 2018 08:27:50 -0500
Message-ID: <028801d49543$24691180$6d3b3480$_at_rsiz.com>
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-lReceived on Sun Dec 16 2018 - 14:27:50 CET