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" <[EMAIL PROTECTED]>
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: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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).
