how to get space allocated and used for tables space

[davey23uk]

Interesting answer.

I think the person is asking how to find out how large a tablespace is and how much of it used. If so here is a sql script that should help.

SET ECHO         OFF
SET SERVEROUTPUT ON
SET LINESIZE 140

DECLARE
   CURSOR cur_ts IS
      SELECT tablespace_name,
             ROUND(SUM(bytes)/1024/1024) totalMB,
             TO_CHAR(ROUND(SUM(bytes)/1024/1024), '999,999') totalMBV
        FROM dba_data_files
       GROUP BY tablespace_name
       ORDER BY tablespace_name;

   TYPE lineDetailRec IS RECORD 
      ( HighWaterMarkMB  VARCHAR2(32),
        freeMB           VARCHAR2(32),
        largestFreeSpace VARCHAR2(32),
        pctUsed          VARCHAR2(32) );

   lineDetail lineDetailRec;

   lv_fs       NUMBER := 0;
   percent_fs  NUMBER := 0;
   large_fs    NUMBER := 0;
   blocksize   NUMBER := 0;
   lv_name     v$database.name%TYPE;

BEGIN
   DBMS_OUTPUT.enable(1000000);

   SELECT value
     INTO blocksize
     FROM v$parameter
    WHERE name = 'db_block_size';

   SELECT name 
     INTO lv_name 
     FROM v$database;

   DBMS_OUTPUT.PUT_LINE('*****************'||lv_name||
      ' Database -- Tablespace Summary - Freespace Report *********************');

   DBMS_OUTPUT.PUT_LINE('Tablespace name       '   ||
                        ' Total Space(MB)'         ||
                        ' Highwater Mark '         ||
                        ' Free Space(MB) '         ||
                        '  LG Free(MB)  '          ||
                        '  %Free Space ');

   DBMS_OUTPUT.PUT_LINE('********************* '   ||
                        ' ************** '         ||
                        ' ************** '         ||
                        ' ************** '         ||
                        ' ************* '          ||
                        ' *************');

   FOR lv_cur_ts IN cur_ts LOOP
      SELECT TO_CHAR(hwm.HighWaterMarkMB, '999,999'),
             TO_CHAR(fs.freeMB,           '999,999'),
             TO_CHAR(fs.largestFreeSpace, '999,999'),
             TO_CHAR(ROUND( (freeMB/lv_cur_ts.totalMB)*100, 1), '9990.0')
        INTO lineDetail
        FROM ( SELECT ROUND(SUM(BYTES)/1024/1024) freeMB,
                      ROUND(MAX(bytes)/1024/1024) largestFreeSpace
                 FROM dba_free_space
                WHERE tablespace_name = lv_cur_ts.tablespace_name ) fs,
             ( SELECT NVL(MAX(block_id), 0)*blocksize/1024/1024 HighWaterMarkMB
                 FROM dba_extents
                WHERE tablespace_name = lv_cur_ts.tablespace_name ) hwm;

      DBMS_OUTPUT.PUT_LINE(RPAD(lv_cur_ts.tablespace_name,   21) ||
                           LPAD(lv_cur_ts.totalMBV,          16) ||
                           LPAD(lineDetail.HighWaterMarkMB,  16) ||
                           LPAD(lineDetail.freeMB,           16) ||
                           LPAD(lineDetail.largestFreeSpace, 15) ||
                           LPAD(lineDetail.pctUsed,          10));
   END LOOP;
END;
/

SET ECHO ON

hmm yeah, should read more closely