Re: Space Usage

In article <ccjlopa.1.0007E7F3_at_gatekeeper.ddp.state.me.us>, ccjlopa_at_gatekeeper.ddp.state.me.us (James Lopatosky) says:
>
>We are trying to determine how much space is allocated in our tables, how
>much of that space is being used, and how much is free in Oracle 7.
>Does anyone have any good ideas?
>
>Thanks in advance,
>
>
>Jim Lopatosky

The SQL below will show you how much space has been used in each tablespace and what the largest contiguous chunk of freespace is. Sizes in ORACLE blocks.

COLUMN TABLESPACE_NAME FORMAT A16
COLUMN PCT_SPACE_USED FORMAT 990.99
COLUMN PCT_OF_FREE FORMAT 990.99

SELECT  DDF.TABLESPACE_NAME  "TABLESPACE_NAME",
        SUM(DDF.BLOCKS)      "TOTAL SPACE",
        SUM(DFS.BLOCKS)      "FREE SPACE",
        (SUM(DDF.BLOCKS)
        -SUM(DFS.BLOCKS))*100
        /SUM(DDF.BLOCKS)     "PCT_SPACE_USED",
        MAX(DFS.BLOCKS)      "LARGEST",
        MAX(DFS.BLOCKS)
        /SUM(DFS.BLOCKS)*100 "PCT_OF_FREE"
FROM    DBA_DATA_FILES DDF,
        DBA_FREE_SPACE DFS

Good luck !

   Mark Received on Tue May 17 1994 - 13:11:50 CEST