how to get space allocated and used for tables space
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: how to get space allocated and used for tables space

  1. #1
    Join Date
    Aug 2008
    Posts
    118

    how to get space allocated and used for tables space

    Plesae guide me in this questio please.

    I need to get the space allocated and used by table space?

    How can i do that!!

  2. #2
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,333
    create a table then and put some data in it

  3. #3
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    4,021
    Quote Originally Posted by davey23uk
    create a table then and put some data in it
    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.

    Code:
    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
    this space intentionally left blank

  4. #4
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,333
    Quote Originally Posted by gandolf989
    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.

    Code:
    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

  5. #5
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    4,021
    Quote Originally Posted by davey23uk
    hmm yeah, should read more closely
    i usually read closely, but then again I'm nearsighted.
    this space intentionally left blank

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width