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

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

上SQL:

SELECT d.Tablespace_Name 表空间名,
   Round(Nvl(a.Bytes / 1024 / 1024, 0), 3) "空间大小(MB)",
   Round(Nvl(a.Bytes - Nvl(f.Bytes, 0), 0) / 1024 / 1024, 3) "使用大小(MB)",
   Round(Nvl((a.Bytes - Nvl(f.Bytes, 0)) / a.Bytes * 100, 0), 3) "使用率%",
   Round(Nvl(f.Bytes, 0) / 1024 / 1024, 3) "空闲大小(MB)",
   d.Status,
   a.COUNT,
   d.Contents,
   d.Extent_Management,
   d.Segment_Space_Management
FROM Sys.Dba_Tablespaces d,
   (SELECT Tablespace_Name, SUM(Bytes) Bytes, COUNT(File_Id) COUNT
      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.Contents LIKE 'UNDO'
AND NOT
    (d.Extent_Management LIKE 'LOCAL' AND d.Contents LIKE 'TEMPORARY')
AND d.Tablespace_Name LIKE '%%'
UNION ALL
SELECT d.Tablespace_Name,
   Round(Nvl(a.Bytes / 1024 / 1024, 0), 3),
   Round(Nvl(t.Bytes, 0) / 1024 / 1024, 3),
   Round(Nvl(t.Bytes / a.Bytes * 100, 0), 3),
   Round((Nvl(a.Bytes, 0) / 1024 / 1024 - Nvl(t.Bytes, 0) / 1024 / 1024), 3),
   d.Status,
   a.COUNT,
   d.Contents,
   d.Extent_Management,
   d.Segment_Space_Management
FROM Sys.Dba_Tablespaces d,
   (SELECT Tablespace_Name, SUM(Bytes) Bytes, COUNT(File_Id) COUNT
      FROM Dba_Temp_Files
     GROUP BY Tablespace_Name) a,
   (SELECT Ss.Tablespace_Name,
           SUM((Ss.Used_Blocks * Ts.Blocksize)) Bytes
      FROM Gv$sort_Segment Ss, Sys.Ts$ Ts
     WHERE Ss.Tablespace_Name = Ts.NAME
     GROUP BY Ss.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'
AND d.Tablespace_Name LIKE '%%'
UNION ALL
SELECT d.Tablespace_Name,
   Round(Nvl(a.Bytes / 1024 / 1024, 0), 3),
   Round(Nvl(u.Bytes, 0) / 1024 / 1024, 3),
   Round(Nvl(u.Bytes / a.Bytes * 100, 0), 3),
   Round(Nvl(a.Bytes - Nvl(u.Bytes, 0), 0) / 1024 / 1024, 3),
   d.Status,
   a.COUNT,
   d.Contents,
   d.Extent_Management,
   d.Segment_Space_Management
FROM Sys.Dba_Tablespaces d,
   (SELECT Tablespace_Name, SUM(Bytes) Bytes, COUNT(File_Id) COUNT
      FROM Dba_Data_Files
     GROUP BY Tablespace_Name) a,
   (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.Contents LIKE 'UNDO'
AND d.Tablespace_Name LIKE '%%'
ORDER BY 2 DESC;
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