Oracle-L: Re: DB Size

From: Tim Gorman <Tim_at_SageLogix.com>
Date: Fri, 03 May 2002 08:23:24 -0800
Message-ID: <F001.004576FA.20020503082324@fatcity.com>

I like to use this SQL*Plus script:

---------------------------------- Begin
script ------------------------------
/**********************************************************************
 * File: spc.sql
 * Type: SQL*Plus script
 * Author: Tim Gorman (Evergreen Database Technologies, Inc.)
 * Date: 10-Oct-97
 *
 * Description:
 * SQL*Plus script to display database space usage.
 *
 * Modifications:

TGorman 11mar02 added support for AUTOEXTENSIBLE data files *********************************************************************/ col tablespace format a25 col owner format a20 col type format a19 col sort1 noprint col mb format 999,990.00

clear breaks
clear compute
break on report on tablespace on owner on type

set echo off feedback off timing off pagesize 66 verify off trimspool on

col instance new_value V_INSTANCE noprint select instance from v$thread;

spool spc_&&V_INSTANCE

select tablespace_name tablespace,
owner,
'a' sort1,

segment_type type,
sum(bytes)/1048576 mb
from dba_segments
group by tablespace_name, owner, segment_type union all
select tablespace,
username owner,
'b' sort1,

segtype type,
sum(blocks)/128 mb
from v$sort_usage
group by tablespace, username, segtype
union all
select tablespace_name tablespace,
'' owner,
'c' sort1,
'-------total-------' type,

sum(bytes)/1048576 mb
from dba_segments
group by tablespace_name
union all
select tablespace,
'' owner,
'd' sort1,
'-------total-------' type,

sum(blocks)/128 mb
from v$sort_usage
group by tablespace
union all
select tablespace_name tablespace,
'' owner,
'e' sort1,
'-----allocated-----' type,

sum(bytes)/1048576 mb
from dba_data_files
group by tablespace_name
union all
select tablespace_name tablespace,
'' owner,
'f' sort1,
'-----allocated-----' type,

sum(bytes)/1048576 mb
from dba_temp_files
group by tablespace_name
union all
select tablespace_name tablespace,
'' owner,
'g' sort1,
'----allocatable----' type,

sum(decode(autoextensible,'YES',maxbytes,bytes))/1048576 mb from dba_data_files
group by tablespace_name
union all
select tablespace_name tablespace,
'' owner,
'h' sort1,
'----allocatable----' type,

sum(decode(autoextensible,'YES',maxbytes,bytes))/1048576 mb from dba_temp_files
group by tablespace_name
union all
select tablespace_name tablespace,
'' owner,
'i' sort1,
'' type,

to_number('') mb
from dba_tablespaces
union all
select tablespace,
owner,
sort1,
type,
sum(mb)
from (select '' tablespace,

  'Total' owner,
  'a' sort1,
  'Used' type,

  sum(bytes)/1048576 mb
  from dba_segments
  union all
  select '' tablespace,

  'Total' owner,
  'a' sort1,
  'Used' type,

sum(blocks)/128 mb
from v$sort_usage)
group by tablespace, owner, sort1, type
union all
select tablespace,
owner,
sort1,
type,
sum(mb)
from (select '' tablespace,

  'Total' owner,
  'b' sort1,
  'Allocated' type,

  sum(bytes)/1048576 mb
  from dba_data_files
  union all
   select '' tablespace,

  'Total' owner,
  'b' sort1,
  'Allocated' type,

sum(bytes)/1048576 mb
from dba_temp_files)
group by tablespace, owner, sort1, type
union all
select tablespace,
owner,
sort1,
type,
sum(mb)
from (select '' tablespace,

  'Total' owner,
  'c' sort1,
  'Allocatable' type,

  sum(decode(autoextensible,'YES',maxbytes,bytes))/1048576 mb   from dba_data_files
  union all
  select '' tablespace,

  'Total' owner,
  'c' sort1,
  'Allocatable' type,

sum(decode(autoextensible,'YES',maxbytes,bytes))/1048576 mb from dba_temp_files)
group by tablespace, owner, sort1, type
order by 1, 2, 3, 4;

spool off

---------------------------------- End script ------------------------------

If you want a version with all the formatting intact, you can download it from www.EvDBT.com/library.htm...

Hope this helps...

-Tim

Original Message ----- To: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com> Sent: Friday, May 03, 2002 8:53 AM

Hi all,
How could one collect data from an Oracle Server to respond to the question: "How big is (what is the size of your) Database ?"

Thaking you,

---
CSW
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Simon Waibale
  INET: waibals_at_mtn.co.ug

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Tim Gorman
  INET: Tim_at_SageLogix.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Received on Fri May 03 2002 - 11:23:24 CDT