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