How to check Tablespace Utilization in Oracle ?

by

Hey Folks,

As a DBA, performing routine checks for tablespace monitoring is a fundamental task. Here are some scripts that can assist in monitoring tablespace usage within the database.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
set pages 999
set lines 400
SELECT df.tablespace_name tablespace_name,
 max(df.autoextensible) auto_ext,
 round(df.maxbytes / (1024 * 1024), 2) max_ts_size,
 round((df.bytes - sum(fs.bytes)) / (df.maxbytes) * 100, 2) max_ts_pct_used,
 round(df.bytes / (1024 * 1024), 2) curr_ts_size,
 round((df.bytes - sum(fs.bytes)) / (1024 * 1024), 2) used_ts_size,
 round((df.bytes-sum(fs.bytes)) * 100 / df.bytes, 2) ts_pct_used,
 round(sum(fs.bytes) / (1024 * 1024), 2) free_ts_size,
 nvl(round(sum(fs.bytes) * 100 / df.bytes), 2) ts_pct_free
FROM dba_free_space fs,
 (select tablespace_name,
 sum(bytes) bytes,
 sum(decode(maxbytes, 0, bytes, maxbytes)) maxbytes,
 max(autoextensible) autoextensible
 from dba_data_files
 group by tablespace_name) df
WHERE fs.tablespace_name (+) = df.tablespace_name
GROUP BY df.tablespace_name, df.bytes, df.maxbytes
UNION ALL
SELECT df.tablespace_name tablespace_name,
 max(df.autoextensible) auto_ext,
 round(df.maxbytes / (1024 * 1024), 2) max_ts_size,
 round((df.bytes - sum(fs.bytes)) / (df.maxbytes) * 100, 2) max_ts_pct_used,
 round(df.bytes / (1024 * 1024), 2) curr_ts_size,
 round((df.bytes - sum(fs.bytes)) / (1024 * 1024), 2) used_ts_size,
 round((df.bytes-sum(fs.bytes)) * 100 / df.bytes, 2) ts_pct_used,
 round(sum(fs.bytes) / (1024 * 1024), 2) free_ts_size,
 nvl(round(sum(fs.bytes) * 100 / df.bytes), 2) ts_pct_free
FROM (select tablespace_name, bytes_used bytes
 from V$temp_space_header
 group by tablespace_name, bytes_free, bytes_used) fs,
 (select tablespace_name,
 sum(bytes) bytes,
 sum(decode(maxbytes, 0, bytes, maxbytes)) maxbytes,
 max(autoextensible) autoextensible
 from dba_temp_files
 group by tablespace_name) df
WHERE fs.tablespace_name (+) = df.tablespace_name
GROUP BY df.tablespace_name, df.bytes, df.maxbytes
ORDER BY 4 DESC;
 
 
TABLESPACE_NAME               |AUT|   MAX_TS_SIZE|MAX_TS_PCT_USED|  CURR_TS_SIZE|  USED_TS_SIZE|   TS_PCT_USED|  FREE_TS_SIZE|   TS_PCT_FREE
------------------------------|---|--------------|---------------|--------------|--------------|--------------|--------------|--------------
SYSAUX                        |YES|      32767.98|          10.16|          3520|       3330.25|         94.61|        189.75|             5
UNDOTBS1                      |YES|      32767.98|           4.47|          2360|       1463.81|         62.03|        896.19|            38
SYSTEM                        |YES|      32767.98|           3.39|          1110|       1109.69|         99.97|           .31|             0
USERS                         |YES|      32767.98|            .01|             5|          2.69|         53.75|          2.31|            46
TEMP                          |YES|      32767.98|              0|          1427|             0|             0|          1427|           100
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
set colsep |
set linesize 100 pages 100 trimspool on numwidth 14
col name format a25
col owner format a15
col "Used (GB)" format a15
col "Free (GB)" format a15
col "(Used) %" format a15
col "Size (M)" format a15
SELECT d.status "Status", d.tablespace_name "Name",
 TO_CHAR(NVL(a.bytes / 1024 / 1024 /1024, 0),'99,999,990.90') "Size (GB)",
 TO_CHAR(NVL(a.bytes - NVL(f.bytes, 0), 0)/1024/1024 /1024,'99999999.99') "Used (GB)",
 TO_CHAR(NVL(f.bytes / 1024 / 1024 /1024, 0),'99,999,990.90') "Free (GB)",
 TO_CHAR(NVL((a.bytes - NVL(f.bytes, 0)) / a.bytes * 100, 0), '990.00') "(Used) %"
 FROM sys.dba_tablespaces d,
 (select tablespace_name, sum(bytes) bytes from dba_data_files group by tablespace_name) a,
 (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 NOT
 (d.extent_management like 'LOCAL' AND d.contents like 'TEMPORARY')
UNION ALL
SELECT d.status
 "Status", d.tablespace_name "Name",
 TO_CHAR(NVL(a.bytes / 1024 / 1024 /1024, 0),'99,999,990.90') "Size (GB)",
 TO_CHAR(NVL(t.bytes,0)/1024/1024 /1024,'99999999.99') "Used (GB)",
 TO_CHAR(NVL((a.bytes -NVL(t.bytes, 0)) / 1024 / 1024 /1024, 0),'99,999,990.90') "Free (GB)",
 TO_CHAR(NVL(t.bytes / a.bytes * 100, 0), '990.00') "(Used) %"
 FROM sys.dba_tablespaces d,
 (select tablespace_name, sum(bytes) bytes from dba_temp_files group by tablespace_name) a,
 (select tablespace_name, sum(bytes_cached) bytes from v$temp_extent_pool group by tablespace_name) t
 WHERE d.tablespace_name = a.tablespace_name(+) AND d.tablespace_name = t.tablespace_name(+) AND
 d.extent_management like 'LOCAL' AND d.contents like 'TEMPORARY';
 
 
Status   |Name                     |Size (GB)     |Used (GB)      |Free (GB)      |(Used) %
---------|-------------------------|--------------|---------------|---------------|---------------
ONLINE   |SYSTEM                   |          1.08|        1.08   |          0.00 |  99.97
ONLINE   |SYSAUX                   |          3.44|        3.25   |          0.19 |  94.61
ONLINE   |UNDOTBS1                 |          2.30|        1.44   |          0.87 |  62.28
ONLINE   |USERS                    |          0.00|         .00   |          0.00 |  53.75
ONLINE   |TEMP                     |          1.39|        1.39   |          0.00 |  99.93
1
2
3
4
5
6
7
8
9
10
11
12
13
14
Select a.tablespace_name,sum(a.tots/1048576) Tot_Size, sum(a.sumb/1024) Tot_Free, sum(a.sumb)*100/sum(a.tots) Pct_Free, ceil((((sum(a.tots) * 15) - (sum(a.sumb)*100))/85 )/1048576) Min_Add
from (select tablespace_name,0 tots,sum(bytes) sumb
from dba_free_space a
group by tablespace_name
union
Select tablespace_name,sum(bytes) tots,0 from dba_data_files
group by tablespace_name) a group by a.tablespace_name
having sum(a.sumb)*100/sum(a.tots) < 10
order by pct_free;
 
TABLESPACE_NAME               |      TOT_SIZE|      TOT_FREE|      PCT_FREE|       MIN_ADD
------------------------------|--------------|--------------|--------------|--------------
SYSTEM                        |          1110|           320|.0281531531532|           196
SYSAUX                        |          3520|        194304|      5.390625|           398

Track all Tablespaces with free space < 10%

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
Select a.tablespace_name,sum(a.tots/1048576) Tot_Size, sum(a.sumb/1024) Tot_Free, sum(a.sumb)*100/sum(a.tots) Pct_Free, ceil((((sum(a.tots) * 15) - (sum(a.sumb)*100))/85 )/1048576) Min_Add
from (select tablespace_name,0 tots,sum(bytes) sumb
from dba_free_space a
group by tablespace_name
union
Select tablespace_name,sum(bytes) tots,0 from dba_data_files
group by tablespace_name) a group by a.tablespace_name
having sum(a.sumb)*100/sum(a.tots) < 10
order by pct_free;
 
 
TABLESPACE_NAME               |      TOT_SIZE|      TOT_FREE|      PCT_FREE|       MIN_ADD
------------------------------|--------------|--------------|--------------|--------------
SYSTEM                        |          1110|           320|.0281531531532|           196
SYSAUX                        |          3520|        194304|      5.390625|           398
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
set feedback off
set pagesize 70;
set linesize 2000
set head on
COLUMN Tablespace format a25 heading 'Tablespace Name'
COLUMN autoextensible format a11 heading 'AutoExtend'
COLUMN files_in_tablespace format 999 heading 'Files'
COLUMN total_tablespace_space format 99999999 heading 'TotalSpace'
COLUMN total_used_space format 99999999 heading 'UsedSpace'
COLUMN total_tablespace_free_space format 99999999 heading 'FreeSpace'
COLUMN total_used_pct format 9999 heading '%Used'
COLUMN total_free_pct format 9999 heading '%Free'
COLUMN max_size_of_tablespace format 99999999 heading 'ExtendUpto'
COLUM total_auto_used_pct format 999.99 heading 'Max%Used'
COLUMN total_auto_free_pct format 999.99 heading 'Max%Free'
WITH tbs_auto AS
(SELECT DISTINCT tablespace_name, autoextensible
FROM dba_data_files
WHERE autoextensible = 'YES'),
files AS
(SELECT tablespace_name, COUNT (*) tbs_files,
SUM (BYTES/1024/1024) total_tbs_bytes
FROM dba_data_files
GROUP BY tablespace_name),
fragments AS
(SELECT tablespace_name, COUNT (*) tbs_fragments,
SUM (BYTES)/1024/1024 total_tbs_free_bytes,
MAX (BYTES)/1024/1024 max_free_chunk_bytes
FROM dba_free_space
GROUP BY tablespace_name),
AUTOEXTEND AS
(SELECT tablespace_name, SUM (size_to_grow) total_growth_tbs
FROM (SELECT tablespace_name, SUM (maxbytes)/1024/1024 size_to_grow
FROM dba_data_files
WHERE autoextensible = 'YES'
GROUP BY tablespace_name
UNION
SELECT tablespace_name, SUM (BYTES)/1024/1024 size_to_grow
FROM dba_data_files
WHERE autoextensible = 'NO'
GROUP BY tablespace_name)
GROUP BY tablespace_name)
SELECT c.instance_name,a.tablespace_name Tablespace,
CASE tbs_auto.autoextensible
WHEN 'YES'
THEN 'YES'
ELSE 'NO'
END AS autoextensible,
files.tbs_files files_in_tablespace,
files.total_tbs_bytes total_tablespace_space,
(files.total_tbs_bytes - fragments.total_tbs_free_bytes
) total_used_space,
fragments.total_tbs_free_bytes total_tablespace_free_space,
round(( ( (files.total_tbs_bytes - fragments.total_tbs_free_bytes)
/ files.total_tbs_bytes
)
* 100
)) total_used_pct,
round(((fragments.total_tbs_free_bytes / files.total_tbs_bytes) * 100
)) total_free_pct
FROM dba_tablespaces a,v$instance c , files, fragments, AUTOEXTEND, tbs_auto
WHERE a.tablespace_name = files.tablespace_name
AND a.tablespace_name = fragments.tablespace_name
AND a.tablespace_name = AUTOEXTEND.tablespace_name
AND a.tablespace_name = tbs_auto.tablespace_name(+)
order by total_free_pct;
 
 
INSTANCE_NAME   |Tablespace Name          |AutoExtend |Files|TotalSpace|UsedSpace|FreeSpace|%Used|%Free
----------------|-------------------------|-----------|-----|----------|---------|---------|-----|-----
asrblg         |SYSTEM                   |YES        |    1|      1110|     1110|        0|  100|    0
asrblg         |SYSAUX                   |YES        |    1|      3520|     3330|      190|   95|    5
asrblg         |UNDOTBS1                 |YES        |    1|      2360|     1492|      868|   63|   37
asrblg         |USERS                    |YES        |    1|         5|        3|        2|   54|   46

Check Tablespace Utilization in Multitenant Database

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
SET LINESIZE 132 PAGESIZE 100
COLUMN con_name FORMAT A15 HEADING "Container|Name"
COLUMN tablespace_name FORMAT A15
COLUMN fsm FORMAT 999,999,999,999 HEADING "Free|Space (MB)"
COLUMN apm FORMAT 999,999,999,999 HEADING "Allocated|Space (MB)"
 
-- Compute and Break for Summaries
COMPUTE SUM OF fsm apm ON REPORT
BREAK ON REPORT ON con_id ON con_name ON tablespace_name
 
-- Query to Get Tablespace Utilization
WITH x AS (
    SELECT
        c1.con_id,
        cf1.tablespace_name,
        SUM(cf1.bytes) / 1024 / 1024 AS fsm
    FROM
        cdb_free_space cf1,
        v$containers c1
    WHERE
        cf1.con_id = c1.con_id
    GROUP BY
        c1.con_id,
        cf1.tablespace_name
),
y AS (
    SELECT
        c2.con_id,
        cd.tablespace_name,
        SUM(cd.bytes) / 1024 / 1024 AS apm
    FROM
        cdb_data_files cd,
        v$containers c2
    WHERE
        cd.con_id = c2.con_id
    GROUP BY
        c2.con_id,
        cd.tablespace_name
)
SELECT
    x.con_id,
    v.name AS con_name,
    x.tablespace_name,
    x.fsm,
    y.apm
FROM
    x,
    y,
    v$containers v
WHERE
    x.con_id = y.con_id
    AND x.tablespace_name = y.tablespace_name
    AND v.con_id = y.con_id
UNION
SELECT
    vc2.con_id,
    vc2.name,
    tf.tablespace_name,
    NULL AS fsm,
    SUM(tf.bytes) / 1024 / 1024 AS apm
FROM
    v$containers vc2,
    cdb_temp_files tf
WHERE
    vc2.con_id = tf.con_id
GROUP BY
    vc2.con_id,
    vc2.name,
    tf.tablespace_name
ORDER BY
    1, 2;
 
 
              |Container      |               |            Free|       Allocated
        CON_ID|Name           |TABLESPACE_NAME|      Space (MB)|      Space (MB)
--------------|---------------|---------------|----------------|----------------
             0|asrblg         |CUSTOMER       |           6,188|          16,384
                              |CUSTOMER       |           1,980|           2,048
                              |IDX            |                |
 
                              |SYSAUX         |             190|           3,520
                              |SYSTEM         |               0|           1,110
                              |TEMP           |                |           1,427
                              |UNDOTBS1       |             866|           2,360
                              |USERS          |               2|               5
 
**************|***************|***************|----------------|----------------
sum           |               |               |          20,292|          45,286

Hope it helped !! 🙂