Oracle-L: RE: DB Size script

From: Abdul Aleem <dmit_at_beaconhouse.edu.pk>
Date: Mon, 06 May 2002 20:28:22 -0800
Message-ID: <F001.004598FA.20020506202822@fatcity.com>

You can get the size of database using DBA studio. Which gives separately the actual space consumed by the data and the size of table spaces.

Aleem

-----Original Message-----

Sent:	Tuesday, May 07, 2002 3:39 AM
To:	Multiple recipients of list ORACLE-L
Subject:	Re: DB Size script

One more script from this list with slight changes. For 8i or up.

SELECT 'The database size is '|| round( (df.sum + rd.sum + tm.sum) / ( 1024 * 1024 * 1024 ) )

|| ' GB excluding INI, password and control files' FROM

(SELECT SUM(bytes) sum FROM sys.dba_data_files) df,
(SELECT SUM(bytes * members) sum FROM v$log) rd,
(SELECT SUM(bytes) sum FROM sys.dba_temp_files) tm

Regards
Rafiq

Reply-To: ORACLE-L_at_fatcity.com
To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com> Date: Fri, 03 May 2002 08:23:24 -0800

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).




MOHAMMAD RAFIQ


_________________________________________________________________
Chat with friends online, try MSN Messenger: http://messenger.msn.com

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Mohammad Rafiq
  INET: rafiq9857_at_hotmail.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).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Abdul Aleem
  INET: dmit_at_beaconhouse.edu.pk

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 Mon May 06 2002 - 23:28:22 CDT