Oracle database tablespace report – SQL script

May 1, 2013 by 14 Comments 

In this article I’ll share a SQL script that I probably use most often. The SQL will show Oracle database tablespace information. Many Oracle DBAs and developers use GUI tools nowadays and me also, but sometimes you need to see some extras. The SQL script will list Oracle database tablespaces including tablespace status and type, counts of files and segments per tablespace and the most important – display proper tablespace sizes.

UPDATE [Jun-2016]: the script is entirely rewritten to version 2.0

– New [2.0]: Tablespace (TS) type is extended to display UNIFORM / System Extend Management and ASSM
– New [2.0]: Display actual used space in UNDO and TEMP tablespaces (not HWM as before)
– New [2.0]: Runtime is considerably reduced
– New [2.0]: Compatible with OEM CC 12c/13c output
– Tested on Oracle database 10g, 11g, 12c
– Column: “Max Size” – maximum possible size of a tablespace as a result of Autoextention of database files
– Column: “TS Type” (Tablespace type):
-> LM/DM – Local/Dictionary Managed
-> SYS/UNI – SYStem/UNIform Extent Management (LM only)
-> ASSM/MSSM – Automatic/Manual Segment Space Management (ASSM -> LM only)

* Script [Version 2.0; updated in Jun-2016]

set pagesize 10000 linesize 300 tab off

col tablespace_name format A22              heading "Tablespace"
col ts_type         format A13              heading "TS Type"
col segments        format 999999           heading "Segments"
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 Space|(Mb)"
col Free_mb         format 999,990.000      heading "Free Space|(Mb)"
col used_pct        format 999              heading "Used|%"
col max_ext_mb      format 99,999,990.000   heading "Max Size|(Mb)"
col max_free_mb     format 9,999,990.000    heading "Max Free|(Mb)"
col max_used_pct    format 999              heading "Max Used|(%)"

BREAK ON REPORT
COMPUTE SUM LABEL "TOTAL SUM ==========>" AVG LABEL "AVERAGE   ==========>" OF segments files allocated_mb used_mb Free_MB max_ext_mb ON REPORT

WITH df AS (SELECT tablespace_name, SUM(bytes) bytes, COUNT(*) cnt, DECODE(SUM(DECODE(autoextensible,'NO',0,1)), 0, 'NO', 'YES') autoext, sum(DECODE(maxbytes,0,bytes,maxbytes)) maxbytes FROM dba_data_files GROUP BY tablespace_name), 
     tf AS (SELECT tablespace_name, SUM(bytes) bytes, COUNT(*) cnt, DECODE(SUM(DECODE(autoextensible,'NO',0,1)), 0, 'NO', 'YES') autoext, sum(DECODE(maxbytes,0,bytes,maxbytes)) maxbytes FROM dba_temp_files GROUP BY tablespace_name), 
     tm AS (SELECT tablespace_name, used_percent FROM dba_tablespace_usage_metrics),
     ts AS (SELECT tablespace_name, COUNT(*) segcnt FROM dba_segments GROUP BY tablespace_name)
SELECT d.tablespace_name, 
       d.status,
       DECODE(d.contents,'PERMANENT',DECODE(d.extent_management,'LOCAL','LM','DM'),'TEMPORARY','TEMP',d.contents)||'-'||DECODE(d.allocation_type,'UNIFORM','UNI','SYS')||'-'||decode(d.segment_space_management,'AUTO','ASSM','MSSM') ts_type,
       a.cnt files,  
       NVL(s.segcnt,0) segments,
       ROUND(NVL(a.bytes / 1024 / 1024, 0), 3) Allocated_MB, 
       ROUND(NVL(a.bytes - NVL(f.bytes, 0), 0)/1024/1024,3) Used_MB, 
       ROUND(NVL(f.bytes, 0) / 1024 / 1024, 3) Free_MB, 
       ROUND(NVL((a.bytes - NVL(f.bytes, 0)) / a.bytes * 100, 0), 2) Used_pct, 
       ROUND(a.maxbytes / 1024 / 1024, 3)  max_ext_mb,
       ROUND(NVL(m.used_percent,0), 2) Max_used_pct
  FROM dba_tablespaces d, df a, tm m, ts s, (SELECT tablespace_name, SUM(bytes) bytes FROM dba_free_space GROUP BY tablespace_name) f 
 WHERE d.tablespace_name = a.tablespace_name(+) 
   AND d.tablespace_name = f.tablespace_name(+) 
   AND d.tablespace_name = m.tablespace_name(+) 
   AND d.tablespace_name = s.tablespace_name(+)
   AND NOT d.contents = 'UNDO' 
   AND NOT ( d.extent_management = 'LOCAL' AND d.contents = 'TEMPORARY' ) 
UNION ALL 
-- TEMP TS
SELECT d.tablespace_name, 
       d.status, 
       DECODE(d.contents,'PERMANENT',DECODE(d.extent_management,'LOCAL','LM','DM'),'TEMPORARY','TEMP',d.contents)||'-'||DECODE(d.allocation_type,'UNIFORM','UNI','SYS')||'-'||decode(d.segment_space_management,'AUTO','ASSM','MSSM') ts_type, 
       a.cnt, 
       0,
       ROUND(NVL(a.bytes / 1024 / 1024, 0), 3) Allocated_MB, 
       ROUND(NVL(t.ub*d.block_size, 0)/1024/1024, 3) Used_MB, 
       ROUND((NVL(a.bytes ,0)/1024/1024 - NVL((t.ub*d.block_size), 0)/1024/1024), 3) Free_MB,
       ROUND(NVL((t.ub*d.block_size) / a.bytes * 100, 0), 2) Used_pct,
       ROUND(a.maxbytes / 1024 / 1024, 3)  max_size_mb, 
       ROUND(NVL(m.used_percent,0), 2) Max_used_pct
  FROM dba_tablespaces d, tf a, tm m, (SELECT ss.tablespace_name , sum(ss.used_blocks) ub FROM gv$sort_segment ss GROUP BY ss.tablespace_name) t 
 WHERE d.tablespace_name = a.tablespace_name(+) 
   AND d.tablespace_name = t.tablespace_name(+) 
   AND d.tablespace_name = m.tablespace_name(+) 
   AND d.extent_management = 'LOCAL' 
   AND d.contents = 'TEMPORARY'   
UNION ALL 
-- UNDO TS
SELECT d.tablespace_name, 
       d.status, 
       DECODE(d.contents,'PERMANENT',DECODE(d.extent_management,'LOCAL','LM','DM'),'TEMPORARY','TEMP',d.contents)||'-'||DECODE(d.allocation_type,'UNIFORM','UNI','SYS')||'-'||decode(d.segment_space_management,'AUTO','ASSM','MSSM') ts_type, 
       a.cnt, 
       NVL(s.segcnt,0) segments,
       ROUND(NVL(a.bytes / 1024 / 1024, 0), 3) Allocated_MB, 
       ROUND(NVL(u.bytes, 0) / 1024 / 1024, 3) Used_MB, 
       ROUND(NVL(a.bytes - NVL(u.bytes, 0), 0)/1024/1024, 3) Free_MB,
       ROUND(NVL(u.bytes / a.bytes * 100, 0), 2) Used_pct, 
       ROUND(a.maxbytes / 1024 / 1024, 3)  max_size_mb,
       ROUND(NVL(m.used_percent,0), 2) Max_used_pct
FROM dba_tablespaces d, df a, tm m, ts s, (SELECT tablespace_name, SUM(bytes) bytes FROM dba_undo_extents where status in ('ACTIVE','UNEXPIRED') GROUP BY tablespace_name) u 
WHERE d.tablespace_name = a.tablespace_name(+) 
AND d.tablespace_name = u.tablespace_name(+) 
AND d.tablespace_name = m.tablespace_name(+) 
AND d.tablespace_name = s.tablespace_name(+)
AND d.contents = 'UNDO' 
ORDER BY 1 
/

prompt * Tablespace (TS) types: 
prompt .  - LM/DM     - Local/Dictionary Managed 
prompt .  - SYS/UNI   - SYStem/UNIform Extent Management (LM only)
prompt .  - ASSM/MSSM - Automatic/Manual Segment Space Management (ASSM -> LM only)

See below the expected SQL script output:

                                                              Allocated Size     Used Space   Free Space Used        Max Size Max Used
Tablespace             STATUS    TS Type       FILES Segments           (Mb)           (Mb)         (Mb)    %            (Mb)      (%)
---------------------- --------- ------------- ----- -------- -------------- -------------- ------------ ---- --------------- --------
SYSAUX                 ONLINE    LM-SYS-ASSM       1     2787        750.000        696.063       53.938   93       8,000.000        9
SYSTEM                 ONLINE    LM-SYS-MSSM       1     2065        700.000        625.063       74.938   89       8,000.000        8
TEMP                   ONLINE    TEMP-UNI-MSSM     1        0         20.000          5.000       15.000   25      32,000.000        0
UNDOTBS1               ONLINE    UNDO-SYS-MSSM     1       10        455.000          7.188      447.813    2      32,000.000        0
USERS                  ONLINE    LM-SYS-ASSM       7        6         56.000          1.750       54.250    3     224,000.000        0
                                               ----- -------- -------------- -------------- ------------      ---------------
AVERAGE   ==========>                              2      974        396.200        267.013      129.188           60,800.000
TOTAL SUM ==========>                             11     4868      1,981.000      1,335.064      645.939          304,000.000

5 rows selected.

* Tablespace (TS) types:
.  - LM/DM     - Local/Dictionary Managed
.  - SYS/UNI   - SYStem/UNIform Extent Management (LM only)
.  - ASSM/MSSM - Automatic/Manual Segment Space Management (ASSM -> LM only)

I encourage readers to comment or share own tips for Oracle database tablespace information.

Enjoyed this article? Please share it with others using the social site of your choice:

Comments

14 Responses to “Oracle database tablespace report – SQL script”
  1. mdinh says:

    Thanks for a very nice script., What does negative Max Free for UNDO mean? Why is Max Size is less than Allocated Size for USERS tbs?

    TS Allocated Size Used Size Max Size Max Free Max Free
    Tablespace STATUS type Objects FILES (MB) (MB) (MB) (MB) %
    ———————- ——— —— ——- —– ————– ———- ———- ———- ——–
    UNDOTBS1 ONLINE UNDO 719 3 28,672 26,268 22,528 -3,740 -17
    USERS ONLINE LM-SYS 448 36 645,428 515,376 523,141 7,765 1

  2. Angelo Corbo says:

    Great script, Kirill !

    It’s already part of my toolkit.

    Sometimes I find the line breaking of SQL*Plus a nuisance, so I added four lines at the top, and now the output has columns well aligned and much easier to read:

    set lines 556
    set trimout on
    set space 1
    set tab off

  3. Kirill Loifman says:

    Hi Angelo
    Great addition!
    I think set tab off does the trick fixing the shifting lines issue.
    Thanks
    — Kirill

  4. Paige Hannah says:

    Hello,

    I think I just learned that this might not work on tablespaces that are full. In an SAP instance, SAP Admins were complaining that a tablespace was full…. however after running your script, the tablespace just would not show up in the output no matter how many times I ran it. Only after a new datafile was added to the missing tablespace was when it showed up in the output

  5. Kirill Loifman says:

    Never expririenced this. Give me more details. you mean the TS does not show up in the result set of my SQL??
    Permanent or temporary TS?
    — Kirill

  6. Paige Hannah says:

    Yes, correct. The Full tablespace was not listed in the result set initially.
    Only after did I add space was it listed in the output. It is a permanent Tablespace.

  7. Kirill Loifman says:

    Yes, you are correct, very special case. I fixed the script.
    Thanks
    — Kirill

  8. Jose Luis Castaneda says:

    Hi,

    I think the way to calculate the number of objects is not accurate. I have several partitioned tables and their local indexes in my DB; that produce a wrong number of objects because you are using DBA_SEGMENTS like source. Remeber, a partitioned tables has a segment for each partition. Maybe a DISTINCT in the segment_name column could help.

    Regards,

    Jose Luis Castaneda

  9. Kirill Loifman says:

    Thanks Jose
    Yes, but the idea of tablespace report to show exactly number of segments (not number of objects).
    Maybe the column header name is confusing.. will adjust it accordingly.
    — Kirill

  10. stann says:

    Hi,

    I use above script and find something strange in my db for TEMP tablespace (oracle 10g):
    Result for TEMP tablespace is

    TABLESPACE_NAME ALLOCATED_MB USED_MB MAX_SIZE_MB MAX_FREE_MB
    —————————————————————————————————————————
    TEMP 85120 85084 85120 36

    This result is totally different as in Oracle Enterprise Manager: USED_MB is only 7 and FREE_MB is 85113.
    I need help to resolve this problem.
    Thank you

  11. Kirill Loifman says:

    Hi Stann
    I made a note that used space for TEMP TS is HWM in my script, so that was an expected behavior.
    However, i took time and rewrite the script completely improving it in this and some other areas.
    Try the new version 2.0 from the same post.
    — Kirill

  12. GS says:

    Really good script to use! Thank you for the effort.

  13. Asad says:

    Do you know how can I create a report either through OEM 12c or or some kind of query that runs automatically and sends me a report on tablespace used/free space for all the databases in our system..

    Thanks

  14. FADI says:

    please
    I need this SQL statement that will give me
    all the tablespaces in the ORCL database
    by displaying the total available space, used space, free space, free space percentage, status and type
    and thank you

Add a Comment

We welcome thoughtful and constructive comments from readers.
If you want your own picture to show with your comment?
Go get a Globally Recognized Avatar!

DBMS Blog Updates : Subscribe RSS RSS: Subscribe to Articles · Subscribe to Comments Subscribe RSS Receive site updates via email