查看Oracle表空间使用情况

查看Oracle表空间使用情况

𝓓𝓸𝓷 Lv6
  2024-07-05 16:30:29 2024-07-05 16:30:29 Created   2025-08-01 17:04:52 2025-08-01 17:04:52 Updated    
一、查看Oracle表空间使用率

查询Oracle数据库表空间使用率

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
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
set linesize 500 pages 500;
col tablespace_name for a30
col USED_PCT for a15
col global_name for a20

select to_char(sysdate,'yyyymmdd') check_date,
       a.tablespace_name,
       round(a.total_mb, 1) total_mb,
       round((a.allocation_mb - b.free_space_mb), 1) used_mb,
       to_char((nvl(a.allocation_mb - b.free_space_mb, 0) / a.total_mb) * 100,
               '90.00') || '%' USED_PCT,
       global_name
  from (select tablespace_name,
               SUM(case autoextensible
                     when 'YES' THEN
                      CASE
                        WHEN maxbytes > bytes THEN
                         maxbytes
                        ELSE
                         bytes
                      END
                     ELSE
                      bytes
                   END) / 1024 / 1024 total_mb,
               sum(bytes) / 1024 / 1024 allocation_mb
          from dba_data_files
         group by tablespace_name) a,
       (select tablespace_name, sum(bytes) / 1024 / 1024 free_space_mb
          from dba_free_space
         group by tablespace_name) b,
       global_name
 where a.tablespace_name = b.tablespace_name order by 5 desc;



SET LINES 400
SET PAGES 400
SET FEEDBACK OFF
COL owner FOR a20
COL platform_name FOR a36
COL COMP_NAME FOR a40
COL name FOR a45
COL tablespace_name FOR a20
COL CURRENT_USED_PCT FOR a16
COL CONTENTS FOR a10
COL MAX_USED_PCT FOR a8
COL value FOR a45
COL HOST_NAME FOR a20
COL job_user FOR a13
COL what FOR a30
COL interval FOR a20
COL file_name FOR a40
COL report_User FOR a12
COL db_unique_name FOR a20
COL PARAMETER FOR a40
COL DESCRIPTION FOR a65
COL MEMBER FOR a45
COL ELAPSED_TIME FOR a20
COL INPUT_SIZE FOR a20
COL OUTPUT_SIZE FOR a20
COL OUTPUT_RATE_PER_SEC FOR a30
COL HANDLE FOR a100
SET HEADING ON

  SELECT d.tablespace_name,
         ROUND (d.bytes / 1048576, 2)
             AS current_size_mb,
         ROUND (NVL (f.bytes, 0) / 1048576, 2)
             AS current_free_mb,
         ROUND ((d.bytes - NVL (f.bytes, 0)) / 1048576, 2)
             AS current_used_mb,
         ROUND ((d.bytes - NVL (f.bytes, 0)) / d.bytes, 4) * 100 || '%'
             AS current_used_pct,
         ROUND (d.max_bytes / 1048576, 2)
             AS max_size_mb,
         ROUND ((d.bytes - NVL (f.bytes, 0)) / d.max_bytes, 4) * 100 || '%'
             AS max_used_pct,
         d.file_count,
         d.status_count,
         t.contents
    FROM (  SELECT tablespace_name,
                   SUM (bytes)        bytes,
                   SUM (
                       CASE
                           WHEN AUTOEXTENSIBLE = 'YES' THEN maxbytes
                           ELSE bytes
                       END)           max_bytes,
                   COUNT (file_id)    file_count,
                   SUM (
                       CASE
                           WHEN online_status = 'ONLINE' THEN 1
                           WHEN online_status = 'SYSTEM' THEN 1
                           ELSE 0
                       END)           status_count
              FROM sys.DBA_DATA_FILES
          GROUP BY tablespace_name
          UNION ALL
            SELECT tablespace_name,
                   SUM (bytes)        bytes,
                   SUM (
                       CASE
                           WHEN AUTOEXTENSIBLE = 'YES' THEN maxbytes
                           ELSE bytes
                       END)           max_bytes,
                   COUNT (file_id)    file_count,
                   SUM (
                       CASE
                           WHEN status = 'ONLINE' THEN 1
                           WHEN status = 'AVAILABLE' THEN 1
                           ELSE 0
                       END)           status_count
              FROM sys.DBA_temp_FILES
          GROUP BY tablespace_name) d,
         (  SELECT tablespace_name, SUM (bytes) bytes
              FROM sys.DBA_FREE_SPACE
          GROUP BY tablespace_name) f,
         dba_tablespaces t
   WHERE     d.tablespace_name = f.tablespace_name(+)
         AND t.tablespace_name = d.tablespace_name
ORDER BY current_used_pct DESC;




SET LINESIZE 500 PAGESIZE 500
SET tab OFF
COL TABLESPACENAME FORMAT a26
SPOOL /tmp/tablespace.log

  SELECT SUBSTR (a.TABLESPACE_NAME, 1, 30)
             TablespaceName,
         ROUND (SUM (a.bytes / 1024 / 1024 / 1024), 2)
             AS "Totle_size(G)",
         ROUND (SUM (NVL (b.free_space1 / 1024 / 1024 / 1024, 0)), 2)
             AS "Free_space(G)",
           ROUND (SUM (a.bytes / 1024 / 1024 / 1024), 2)
         - ROUND (SUM (NVL (b.free_space1 / 1024 / 1024 / 1024, 0)), 2)
             AS "Used_space(G)",
         ROUND (
               (  SUM (a.bytes / 1024 / 1024 / 1024)
                - SUM (NVL (b.free_space1 / 1024 / 1024 / 1024, 0)))
             * 100
             / SUM (a.bytes / 1024 / 1024 / 1024),
             2)
             AS "Used_percent%",
         ROUND (
             SUM (
                   (CASE WHEN a.MAXBYTES = 0 THEN a.bytes ELSE a.MAXBYTES END)
                 / 1024
                 / 1024
                 / 1024),
             2)
             AS "Max_size(G)",
         ROUND (
               (  SUM (a.bytes / 1024 / 1024 / 1024)
                - SUM (NVL (b.free_space1 / 1024 / 1024 / 1024, 0)))
             * 100
             / SUM (
                     (CASE WHEN a.MAXBYTES = 0 THEN a.bytes ELSE a.MAXBYTES END)
                   / 1024
                   / 1024
                   / 1024),
             2)
             AS "Max_percent%"
    FROM dba_data_files a,
         (  SELECT SUM (NVL (bytes, 0)) free_space1, file_id
              FROM dba_free_space
          GROUP BY file_id) b
   WHERE a.file_id = b.file_id(+)
GROUP BY a.TABLESPACE_NAME
ORDER BY "Used_percent%" DESC;


/* Formatted on 2024/09/18 19:47:09 (QP5 v5.388) */
SET LINES 1000 PAGES 500;
COL tablespacename FOR a30;

SELECT *
  FROM (  SELECT SUBSTR (a.TABLESPACE_NAME, 1, 30)
                     TablespaceName,
                 ROUND (SUM (a.bytes / 1024 / 1024 / 1024), 2)
                     AS "Totle_size(G)",
                 ROUND (SUM (NVL (b.free_space1 / 1024 / 1024 / 1024, 0)), 2)
                     AS "Free_space(G)",
                   ROUND (SUM (a.bytes / 1024 / 1024 / 1024), 2)
                 - ROUND (SUM (NVL (b.free_space1 / 1024 / 1024 / 1024, 0)), 2)
                     AS "Used_space(G)",
                 ROUND (
                       (  SUM (a.bytes / 1024 / 1024 / 1024)
                        - SUM (NVL (b.free_space1 / 1024 / 1024 / 1024, 0)))
                     * 100
                     / SUM (a.bytes / 1024 / 1024 / 1024),
                     2)
                     AS "Used_percent%",
                 ROUND (
                     SUM (
                           (CASE
                                WHEN a.MAXBYTES = 0 THEN a.bytes
                                ELSE a.MAXBYTES
                            END)
                         / 1024
                         / 1024
                         / 1024),
                     2)
                     AS "Max_size(G)",
                 ROUND (
                       (  SUM (a.bytes / 1024 / 1024 / 1024)
                        - SUM (NVL (b.free_space1 / 1024 / 1024 / 1024, 0)))
                     * 100
                     / SUM (
                             (CASE
                                  WHEN a.MAXBYTES = 0 THEN a.bytes
                                  ELSE a.MAXBYTES
                              END)
                           / 1024
                           / 1024
                           / 1024),
                     2)
                     AS "Max_percent%"
            FROM dba_data_files a,
                 (  SELECT SUM (NVL (bytes, 0)) free_space1, file_id
                      FROM dba_free_space
                  GROUP BY file_id) b
           WHERE a.file_id = b.file_id(+)
        GROUP BY a.TABLESPACE_NAME
        ORDER BY "Max_percent%" DESC)
 WHERE ROWNUM < 10;
 

SELECT  UPPER(F.TABLESPACE_NAME)                           AS "TABLESPACE_NAME",
        ROUND(D.MAX_BYTES,2)                               AS "TBS_MAX_SIZE" ,
        ROUND(D.AVAILB_BYTES ,2)                           AS "TBS_ACT_SIZE",
        ROUND((D.AVAILB_BYTES - F.USED_BYTES),2)           AS "TBS_USED_SIZE",
        ROUND(F.USED_BYTES, 2)                             AS "FREE_SIZE(GB)",
        TO_CHAR(ROUND((D.AVAILB_BYTES - F.USED_BYTES) / D.AVAILB_BYTES * 100,
                     2),
               '999.99')                                   AS "USED_RATE(%)",
        TO_CHAR(ROUND((D.AVAILB_BYTES - F.USED_BYTES)/D.MAX_BYTES*100,
                     2),
               '999.99')                                   AS "ACT_USED_RATE(%)",
        ROUND(D.MAX_BYTES - D.AVAILB_BYTES +USED_BYTES,2)  AS "TBS_AVABLE_SIZE"
  FROM (SELECT TABLESPACE_NAME,
               ROUND(SUM(BYTES) / (1024 * 1024 * 1024), 6) USED_BYTES,
               ROUND(MAX(BYTES) / (1024 * 1024 * 1024), 6) MAX_BYTES
          FROM SYS.DBA_FREE_SPACE
         GROUP BY TABLESPACE_NAME) F,
       (SELECT DD.TABLESPACE_NAME,
               ROUND(SUM(DD.BYTES) / (1024 * 1024 * 1024), 6)  AVAILB_BYTES,
               ROUND(SUM(DECODE(DD.MAXBYTES, 0, DD.BYTES, DD.MAXBYTES))/(1024*1024*1024),6)   MAX_BYTES
          FROM SYS.DBA_DATA_FILES DD
         GROUP BY DD.TABLESPACE_NAME) D
WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME
ORDER BY "ACT_USED_RATE(%)" DESC;


非自动扩展表空间使用率查询:

SELECT
    df.tablespace_name,
    total_space,
    (total_space - free_space) AS used_space,
    free_space,
    ROUND((total_space - free_space) / total_space * 100, 2) AS used_percent
FROM
    (SELECT
         tablespace_name,
         ROUND(SUM(bytes) / 1024 / 1024, 2) AS total_space
     FROM
         dba_data_files
     GROUP BY
         tablespace_name) df,
    (SELECT
         tablespace_name,
         ROUND(SUM(bytes) / 1024 / 1024, 2) AS free_space
     FROM
         dba_free_space
     GROUP BY
         tablespace_name) fs
WHERE
    df.tablespace_name = fs.tablespace_name;
二、查看Oracle临时表空间使用率

查询Oracle数据库临时表空间使用情况

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
查询用户默认表空间和临时表空间:
set linesize 500
set pagesize 500
select username,default_tablespace,temporary_tablespace from dba_users;

查看临时表空间:
 set linesize 200 pagesize 200
 SELECT d.tablespace_name "Name",
       TO_CHAR(NVL(a.bytes / 1024 / 1024 / 1024, 0), '99,999,990.900') "Size (G)",
       TO_CHAR(NVL(t.hwm, 0) / 1024 / 1024 / 1024, '99999999.999') "HWM (G)",
       TO_CHAR(NVL(t.hwm / a.bytes * 100, 0), '990.00') "HWM % ",
       TO_CHAR(NVL(t.bytes / 1024 / 1024 / 1024, 0), '99999999.999') "Using (G)",
       TO_CHAR(NVL(t.bytes / a.bytes * 100, 0), '990.00') "Using %"
  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) hwm, sum(bytes_used) 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';
 
 
查看临时表空间使用了多少G:
select sum(blocks)*8/1024/1024 GB from V$TEMPSEG_USAGE where TABLESPACE='TEMP2';
 
查看占用高的SQL语句:
SELECT username,
		segtype,
		SQL_ID,
		sum(blocks*8/1024/1024) size_G
FROM V$TEMPSEG_USAGE
WHERE TABLESPACE='&tsn'
GROUP BY  username,segtype,SQL_ID
ORDER BY  4 desc;
三、查看Oracle撤销表空间使用率

查询Oracle数据库undo表空间使用情况

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
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
---查看undo表空间数据文件是否自动扩展

col FILE_NAME format a50  
col TABLESPACE_NAME format a20 
select file_id,file_name,tablespace_name,autoextensible from dba_data_files where tablespace_name in ('UNDOTBS1', 'UNDOTBS2') order by file_id; 


---查看undo段
select SEGMENT_NAME,TABLESPACE_NAME,file_id ,STATUS from DBA_ROLLBACK_SEGS order by 2,3;

select tablespace_name,
         round(sum(case
               when status = 'EXPIRED' then
                blocks
             end) * 8 /1024/1024,2) expired_gb,        
         round(sum(case
               when status in ('ACTIVE') then
                blocks
             end) * 8 /1024/1024,2) active_gb,
         round(sum(case
               when status in ('UNEXPIRED') then
                blocks
             end) * 8 /1024/1024,2) unexpired_gb
    from dba_undo_extents
   where status in ('ACTIVE', 'EXPIRED', 'UNEXPIRED')
   group by tablespace_name;



set linesize 300 pagesize 300
with free_sz as
(select tablespace_name, round(sum(f.bytes) /1024/1024/1024,2) free_gb
    from dba_free_space f
   group by tablespace_name),
a as
(select tablespace_name,
         round(sum(case
               when status = 'EXPIRED' then
                blocks
             end) * 8 /1024/1024,2) expired_gb,        
         round(sum(case
               when status in ('ACTIVE', 'UNEXPIRED') then
                blocks
             end) * 8 /1024/1024,2) active_unexpired_gb       
    from dba_undo_extents
   where status in ('ACTIVE', 'EXPIRED', 'UNEXPIRED')
   group by tablespace_name),
undo_sz as
(select tablespace_name, round(sum(df.user_bytes) /1024/1024/1024,2) user_sz_gb
    from dba_tablespaces ts
    join dba_data_files df
   using (tablespace_name)
   where ts.contents = 'UNDO'
     and ts.status = 'ONLINE'
     group by tablespace_name)
select tablespace_name,
       user_sz_gb,
       free_gb,
       expired_gb,
       active_unexpired_gb,
       free_gb + expired_gb + active_unexpired_gb total_gb
  from undo_sz
  join free_sz using (tablespace_name)
  join a using (tablespace_name);



set linesize 300 pagesize 300
with free_sz as
(select tablespace_name, round(sum(f.bytes) /1024/1024/1024,2) free_gb
    from dba_free_space f
   group by tablespace_name),
a as
(select tablespace_name,
         round(sum(case
               when status = 'ACTIVE' then
                blocks
             end) * 8 /1024/1024,2) active_gb,  
         round(sum(case
               when status = 'EXPIRED' then
                blocks
             end) * 8 /1024/1024,2) expired_gb,        
         round(sum(case
               when status in ('ACTIVE', 'UNEXPIRED') then
                blocks
             end) * 8 /1024/1024,2) active_unexpired_gb       
    from dba_undo_extents
   where status in ('ACTIVE', 'EXPIRED', 'UNEXPIRED')
   group by tablespace_name),
undo_sz as
(select tablespace_name, round(sum(df.user_bytes) /1024/1024/1024,2) user_sz_gb
    from dba_tablespaces ts
    join dba_data_files df
   using (tablespace_name)
   where ts.contents = 'UNDO'
     and ts.status = 'ONLINE'
     group by tablespace_name)
select tablespace_name,
       user_sz_gb,
       free_gb,
       active_gb,
       expired_gb,
       active_unexpired_gb,
       free_gb + expired_gb + active_unexpired_gb total_gb
  from undo_sz
  join free_sz using (tablespace_name)
  join a using (tablespace_name);



set linesize 300 pagesize 300
col active_undo_pct for a15
select a.tablespace_name,
       round(c.active_undo, 2) "ACTIVE_UNDO(GB)",
       round(a.ACTIVE_UNEXPIRED_undo, 2) "ACTIVE_UNEXPIRED_UNDO(GB)",
       b.total_undo "TOTAL_UNDO(GB)",
       trunc(active_undo / total_undo * 100, 2) || '%' active_undo_pct,
       trunc(ACTIVE_UNEXPIRED_UNDO / total_undo * 100, 2) || '%' ACTIVE_UNEXPIRED_UNDO_PCT
  from (select nvl(sum(bytes / 1024 / 1024 / 1024), 0) ACTIVE_UNEXPIRED_UNDO,
               tablespace_name
          from dba_undo_extents
         where status = 'ACTIVE'
            or status = 'UNEXPIRED'
         group by tablespace_name) a,
       (select tablespace_name, sum(bytes / 1024 / 1024 / 1024) total_undo
          from dba_data_files
         group by tablespace_name) b,
       (select nvl(sum(bytes / 1024 / 1024 /1024 ), 0) active_undo, tablespace_name
          from dba_undo_extents
         where status = 'ACTIVE'
         group by tablespace_name) c
 where a.tablespace_name = b.tablespace_name
   and a.tablespace_name = c.tablespace_name(+)
   order by tablespace_name;

TABLESPACE_NAME 	       ACTIVE_UNDO(GB) ACTIVE_UNEXPIRED_UNDO(GB) TOTAL_UNDO(GB) ACTIVE_UNDO_PCT ACTIVE_UNEXPIRED_UNDO_PCT
------------------------------ --------------- ------------------------- -------------- --------------- -----------------------------------------
UNDOTBS1				   .19			  125.74     125.749985 .14%		99.99%
UNDOTBS2				   .07			   33.35      62.488266 .11%		53.36%


select tablespace_name tablespace,
       status,
       sum(bytes) / 1024 / 1024 sum_in_mb,
       count(*) counts
  from dba_undo_extents
 group by tablespace_name, status
 order by 1, 2;

TABLESPACE		       STATUS	  SUM_IN_MB	COUNTS
------------------------------ --------- ---------- ----------
UNDOTBS1		       ACTIVE	    192.125	     5
UNDOTBS1		       EXPIRED		2.5	    40
UNDOTBS1		       UNEXPIRED 128563.375	  5249
UNDOTBS2		       ACTIVE	    17.0625	     4
UNDOTBS2		       EXPIRED	    347.375	    30
UNDOTBS2		       UNEXPIRED    64104.5	 39702

  • Title: 查看Oracle表空间使用情况
  • Author: 𝓓𝓸𝓷
  • Created at : 2024-07-05 16:30:29
  • Updated at : 2025-08-01 17:04:52
  • Link: https://www.zhangdong.me/oracle-tablespace-usage.html
  • License: This work is licensed under CC BY-NC-SA 4.0.
推荐文章
查看Oracle表空间增长过快原因 查看Oracle表空间增长过快原因 处理Oracle undo表空间不释放问题 处理Oracle undo表空间不释放问题 Oracle undo表空间不释放 Oracle undo表空间不释放
推荐文章
查看Oracle表空间增长过快原因 查看Oracle表空间增长过快原因 处理Oracle undo表空间不释放问题 处理Oracle undo表空间不释放问题
评论
  1. 一、查看Oracle表空间使用率
  2. 二、查看Oracle临时表空间使用率
  3. 三、查看Oracle撤销表空间使用率