Friday, October 14, 2011

Oracle: Sql script to check tablespace size

Every once in a while one of my test jobs fails, throwing this error that the table-space its created in has no further space to allocate for this table. And then comes the next sets of steps. Find out the other table space which is free. Here is a very basic SQL scripts which I use to list down all the table-spaces and their size in human readable format.

Update: Replacing with Github Gist


SELECT /* + RULE */
df.tablespace_name AS "Tablespace"
,df.bytes / (1024 * 1024 * 1024) AS "Size (GB)"
,Trunc(fs.bytes / (1024 * 1024 * 1024)) AS "Free (GB)"
FROM (
SELECT tablespace_name
,Sum(bytes) AS bytes
FROM dba_free_space
GROUP BY tablespace_name
) fs
,(
SELECT tablespace_name
,SUM(bytes) AS bytes
FROM dba_data_files
GROUP BY tablespace_name
) df
WHERE fs.tablespace_name = df.tablespace_name
ORDER BY 3 DESC
view raw Oracle Script - Check Tablespace Size.sql hosted with ❤ by GitHub
Posted by at
Labels: , , , , ,

4 comments:

  1. AnonymousOctober 09, 2012 7:10 AM

    Great Thank you so much for this script

    ReplyDelete
  2. UnknownSeptember 04, 2015 11:36 PM

    This will run quickly ;)

    SELECT USED.TABLESPACE_NAME, USED.USED_BYTES AS "USED SPACE(IN MB)", FREE.FREE_BYTES AS "FREE SPACE(IN MB)"
    FROM
    (SELECT TABLESPACE_NAME,TO_CHAR(SUM(NVL(BYTES,0))/1024/1024/1024, '99,999,990.99') AS USED_BYTES FROM USER_SEGMENTS GROUP BY TABLESPACE_NAME) USED
    INNER JOIN
    (SELECT TABLESPACE_NAME,TO_CHAR(SUM(NVL(BYTES,0))/1024/1024/1024, '99,999,990.99') AS FREE_BYTES FROM USER_FREE_SPACE GROUP BY TABLESPACE_NAME) FREE
    ON (USED.TABLESPACE_NAME = FREE.TABLESPACE_NAME);

    ReplyDelete
  3. UnknownSeptember 04, 2015 11:37 PM

    And this one is Great..!!


    col tablespace_name format A22 heading "Tablespace"
    col objects format 999999 heading "Objects"
    col files format 9999
    col allocated_mb format 9,999,990.000 heading "Allocated Size|(Mb)"
    col used_mb format 9,999,990.000 heading "Used Size|(Mb)"
    col ts_type format A6 heading "TS|type"
    col max_size_mb format 9,999,990.000 heading "Max Size|(Mb)"
    col max_free_mb format 9,999,990.000 heading "Max Free|(Mb)"
    col max_free_pct format 999 heading "Max Free|%"
     
    BREAK ON REPORT
    COMPUTE SUM LABEL "Total SUM:" OF objects files allocated_mb used_mb max_size_mb MAX_FREE_MB ON REPORT
    COMPUTE AVG LABEL "Average %:" OF FREE_PCT MAX_FREE_PCT ON REPORT
     
    SELECT ts.tablespace_name, status,
    DECODE(dt.contents,'PERMANENT',DECODE(dt.extent_management,'LOCAL',DECODE(dt.allocation_type,'UNIFORM','LM-UNI','LM-SYS'),'DM'),'TEMPORARY','TEMP',dt.contents) ts_type,
    NVL(s.count,0) objects,
    ts.files,
    ts.allocated/1024/1024 allocated_mb,
    ROUND((ts.allocated-nvl(ts.free_size,0))/1024/1024,3) used_mb,
    ROUND(maxbytes/1024/1024,3) max_size_mb,
    ROUND((maxbytes-(ts.allocated-nvl(ts.free_size,0)))/1024/1024,3) max_free_mb,
    ROUND((maxbytes-(ts.allocated-nvl(ts.free_size,0)))*100/maxbytes,2) max_free_pct
    FROM
    (
    SELECT dfs.tablespace_name,files,allocated,free_size,maxbytes
    FROM
    (SELECT fs.tablespace_name, sum(fs.bytes) free_size
    FROM dba_free_space fs
    GROUP BY fs.tablespace_name)
    dfs,
    (SELECT df.tablespace_name, count(*) files, sum(df.bytes) allocated,
    sum(DECODE(df.maxbytes,0,df.bytes,df.maxbytes)) maxbytes, max(autoextensible) autoextensible
    FROM dba_data_files df
    WHERE df.status = 'AVAILABLE'
    GROUP BY df.tablespace_name)
    ddf
    WHERE dfs.tablespace_name = ddf.tablespace_name
    UNION
    SELECT dtf.tablespace_name,files,allocated,free_size,maxbytes
    FROM
    (SELECT tf.tablespace_name, count(*) files, sum(tf.bytes) allocated,
    sum(DECODE(tf.maxbytes,0,tf.bytes,tf.maxbytes)) maxbytes, max(autoextensible) autoextensible
    FROM dba_temp_files tf
    GROUP BY tf.tablespace_name)
    dtf,
    (SELECT th.tablespace_name, SUM (th.bytes_free) free_size
    FROM v$temp_space_header th
    GROUP BY tablespace_name)
    tsh
    WHERE dtf.tablespace_name = tsh.tablespace_name
    ) ts,
    ( SELECT s.tablespace_name, count(*) count
    FROM dba_segments s
    GROUP BY s.tablespace_name) s,
    dba_tablespaces dt,
    v$parameter p
    WHERE p.name = 'db_block_size'
    AND ts.tablespace_name = dt.tablespace_name
    AND ts.tablespace_name = s.tablespace_name (+)
    ORDER BY 1

    ReplyDelete
  4. UnknownNovember 29, 2017 6:48 AM

    This comment has been removed by the author.

    ReplyDelete

Subscribe to: Post Comments (Atom)