Re: HoTo find out how much free space in tablespace??

Lars Steinmetz wrote:
> Hello NG,
>
> I have an Oracle 9i Server with serveral users and tablespaces. One of these
> tablespaces was full so I deleted some data out of it. How can I find out
> how much space in in the tablespace now? The enterprise Manager only shows
> high water marks!
>

Copy this to a file, give it a name and run it from SQL*Plus. It will show 0 for tempfiles but will give you a nice listing for the rest. If you need a version that will show temp files too, just ask.

--
AjA
-- --------------
SET PAGESIZE   60
SET LINESIZE   96
SET NEWPAGE    0
SET VERIFY     OFF
SET ECHO       OFF
SET UNDERLINE  =
SET HEADING    ON
SET TERMOUT    ON
SET FEEDBACK   Off

COLUMN tablespace_name   FORMAT a20          HEADING 'In Tablespace'
COLUMN num_files         FORMAT 9,999        HEADING 'Using|This Num|Files'
COLUMN sum_free_mbytes   FORMAT 99,999,999   HEADING 'There Are|This 
Many|Free M-Byte'
COLUMN count_blocks      FORMAT 99,999,999   HEADING 'In This 
Many|Contigouus|Sections'
COLUMN max_mbytes        FORMAT 99,999,999   HEADING 'The Largest|of 
Which is|This Many|M-Byte'
COLUMN sum_alloc_mbytes  FORMAT 99,999,999   HEADING 'Total 
TS|Allocated|M-Bytes'
COLUMN pct_free          FORMAT 999.99       HEADING 'Total|TS Pct|Free'
COLUMN ts_#              FORMAT 999          HEADING 'TS #'

BREAK ON REPORT
COMPUTE SUM OF sum_free_mbytes  ON report
COMPUTE SUM OF sum_alloc_mbytes ON report

TTITLE Center 'Tablespace Usage and Free Space'  skip 2

SELECT  tablespace_name,
         ts_#,
         num_files,
         sum_free_mbytes,
         count_blocks,
         max_mbytes,
         sum_alloc_mbytes,
         DECODE(sum_alloc_mbytes,0,0,100 * sum_free_mbytes / 
sum_alloc_mbytes )  AS pct_free
   FROM  (SELECT  v.name                    AS tablespace_name,
                  ts#                       AS ts_#,
                  NVL(SUM(bytes)/1048576,0) AS sum_alloc_mbytes,
                  NVL(COUNT(file_name),0)   AS num_files
            FROM  dba_data_files f,
                  v$tablespace   v
           WHERE  v.name = f.tablespace_name (+)
           GROUP
              BY  v.name,
                  ts#),
         (SELECT  v.name                    AS fs_ts_name,
                  ts#,
                  NVL(MAX(bytes)/1048576,0) AS max_mbytes,
                  NVL(COUNT(BLOCKS)     ,0) AS count_blocks,
                  NVL(SUM(bytes)/1048576,0) AS sum_free_mbytes
            FROM  dba_free_space f,
                  v$tablespace   v
           WHERE  v.name = f.tablespace_name(+)
           GROUP
              BY  v.name,
                  ts#)
  WHERE  tablespace_name = fs_ts_name
;

TTITLE OFF
BTITLE OFF
REPFOOTER OFF
TTITLE OFF
BTITLE OFF
REPFOOTER OFF
SET FEEDBACK ON
SET VERIFY     ON
SET UNDERLINE  '-'
SET HEADING    ON
SET TERMOUT    ON