SQL脚本如下：

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
SELECT nvl(t.name, 'CDB$ROOT') as DB_NAME,
       D.TABLESPACE_NAME,
       TOTAL_SPACE,
       (SPACE - NVL(FREE_SPACE, 0)),
       ROUND((SPACE - NVL(FREE_SPACE, 0)) / TOTAL_SPACE * 100, 2) ratio,
       FREE_SPACE
  FROM (SELECT TABLESPACE_NAME,
               con_id,
               ROUND(SUM(BYTES) / (1024 * 1024), 2) SPACE,
               ROUND(SUM(decode(maxbytes, 0, bytes, maxbytes)) /
                     (1024 * 1024),
                     2) TOTAL_SPACE,
               SUM(BLOCKS) BLOCKS
          FROM containers(DBA_DATA_FILES)
         GROUP BY TABLESPACE_NAME, con_id) D,
       (SELECT TABLESPACE_NAME,
               con_id,
               ROUND(SUM(BYTES) / (1024 * 1024), 2) FREE_SPACE
          FROM containers(DBA_FREE_SPACE)
         GROUP BY TABLESPACE_NAME, con_id) F,
       v$pdbs t
 WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME(+)
   and D.CON_ID = F.CON_ID
   and F.con_id = t.CON_ID(+)
UNION ALL
SELECT nvl(t.name, 'CDB$ROOT') as DB_NAME,
       D.TABLESPACE_NAME,
       SPACE,
       USED_SPACE,
       ROUND(NVL(USED_SPACE, 0) / SPACE * 100, 2) ratio,
       NVL(FREE_SPACE, 0)
  FROM (SELECT TABLESPACE_NAME,
               con_id,
               ROUND(SUM(decode(maxbytes, 0, bytes, maxbytes)) /
                     (1024 * 1024),
                     2) SPACE,
               SUM(BLOCKS) BLOCKS
          FROM containers(DBA_TEMP_FILES)
         GROUP BY TABLESPACE_NAME, con_id) D,
       (SELECT TABLESPACE_NAME,
               con_id,
               ROUND(SUM(BYTES_USED) / (1024 * 1024), 2) USED_SPACE,
               ROUND(SUM(BYTES_FREE) / (1024 * 1024), 2) FREE_SPACE
          FROM containers(V$TEMP_SPACE_HEADER)
         GROUP BY TABLESPACE_NAME, con_id) F,
       v$pdbs t
 WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME(+)
   and D.CON_ID = F.CON_ID
   and F.con_id = t.CON_ID(+)
 ORDER BY 1;