Re: How do you query the size of an Oracle database?

From: Kevin McDaniel <kmcdaniel96_at_home.com>
Date: 25 Oct 2001 19:37:42 -0700
Message-ID: <9c966595.0110251837.2aa743db_at_posting.google.com>

Try this... I think this is what you want. rem This tablespace report shows total/used/free bytes and pct used. SET NEWP 0;
COLUMN dummy NOPRINT

COLUMN  pct_used FORMAT 999.9         HEADING "%|Used"
COLUMN  name     FORMAT a20           HEADING "Tablespace Name"
COLUMN  bytes    FORMAT999,999,999,999 HEADING "Bytes"
COLUMN  used     FORMAT999,999,999,999 HEADING "Used"
COLUMN  free     FORMAT999,999,999,999 HEADING "Free"

BREAK ON REPORT
COMPUTE SUM OF bytes ON REPORT
COMPUTE SUM OF free ON REPORT
COMPUTE SUM OF used ON REPORT
SPOOL free.lis
BTITLE OFF
COLUMN nline NEWLINE
SET HEADING OFF
SET EMBEDDED OFF
SET VERIFY OFF

SELECT 'Date -  '||TO_CHAR(SYSDATE,'Day Ddth Month YYYY     HH24:MI:SS'),
        'Database Name -  '||value nline,
        'Username      -  '||USER

FROM v$parameter
WHERE name = 'db_name'
/
SET EMBEDDED ON
SELECT * FROM v$version
/
PROMPT
PROMPT
SET HEADING ON

SELECT b.tablespace_name                                              name,
       SUM(b.bytes) / COUNT(DISTINCT a.file_id ||'.'|| a.block_id)    bytes,
       SUM(b.bytes) / COUNT(DISTINCT a.file_id ||'.'|| a.block_id) -
          sum(a.bytes) / COUNT(DISTINCT b.file_id)                    used,
       SUM(a.bytes) / COUNT(DISTINCT b.file_id)                       free,
       100 * (
              (SUM(b.bytes) / COUNT(DISTINCT a.file_id ||'.'|| a.block_id)) -
              (SUM(a.bytes) / COUNT(DISTINCT b.file_id))
             ) /
       (SUM(b.bytes) / COUNT(DISTINCT a.file_id ||'.'|| a.block_id ))  

pct_used
FROM sys.dba_free_space a, sys.dba_data_files b WHERE b.tablespace_name = a.tablespace_name(+) GROUP BY b.tablespace_name;
SPOOL OFF;
EXIT; thecake_at_writeme.com (John Marchioli) wrote in message news:<146f9d33.0110240338.6e91db3d_at_posting.google.com>...
> Hi folks.
>
> I have an ODBC application that displays the total diskspace of an SQL
> server database and what space is used by data, and what space is
> still free (unused).
>
> I was wandering if there was a similar query I could execute against
> Oracle? It would be nice to have the results be something like (round
> numbers):
>
> Filename TotalMB UsedMB FreeMB
> --------- ------- ------ ------
> t.mdf 8459 3720 4738
> t.ndf 5728 1757 3970
>
> Any help would be greatly appreicated.
Received on Fri Oct 26 2001 - 04:37:42 CEST