Select sysdate "统计时间",d.Tablespace_Name "表空间名称",
d.Status "状态",
d.Contents "类型",
d.Extent_Management "区管理",
Nvl(Total.Bytes, -1) "总容量(MB)",
Nvl((Total.Bytes - Free.Bytes), -1) "已使用(MB)",
Nvl(Round((1 - Free.Bytes / Total.Bytes) * 100, 2), -1) "使用占比",
Total.Autoextensible "自动增长",
Total.Increment_By "增长量",
Total.File_Name "文件地址"
From Sys.Dba_Tablespaces d,
(Select Tablespace_Name,
Round(Nvl(Sum(Bytes) / 1024 / 1024, 0), 2) Bytes,
File_Name,
Autoextensible,
Increment_By
From Dba_Data_Files
Group By Tablespace_Name, File_Name, Autoextensible, Increment_By) Total,
(Select Tablespace_Name,
Round(Nvl(Sum(Bytes) / 1024 / 1024, 0), 2) Bytes
From Dba_Free_Space
Group By Tablespace_Name) Free
Where d.Tablespace_Name = Total.Tablespace_Name(+)
And d.Tablespace_Name = Free.Tablespace_Name(+)
And Not
(d.Extent_Management Like 'LOCAL' And d.Contents Like 'TEMPORARY')
Union All
Select sysdate,d.Tablespace_Name "表空间名称",
d.Status "状态",
d.Contents "类型",
d.Extent_Management "区管理",
Nvl(Total.Bytes, -1) "总容量(MB)",
Nvl((Total.Bytes - Free.Bytes), -1) "已使用(MB)",
Nvl(Round((1 - Free.Bytes / Total.Bytes) * 100, 2), -1) "使用占比",
Total.Autoextensible "自动增长",
Total.Increment_By "增长量",
Total.File_Name "文件地址"
From Sys.Dba_Tablespaces d,
(Select Tablespace_Name,
Round(Nvl(Sum(Bytes) / 1024 / 1024, 0), 2) Bytes,
File_Name,
Autoextensible,
Increment_By
From Dba_Temp_Files
Group By Tablespace_Name, File_Name, Autoextensible, Increment_By) Total,
(Select Tablespace_Name,
Round(Nvl(Sum(Bytes_Cached) / 1024 / 1024, 0), 2) Bytes
From V$temp_Extent_Pool
Group By Tablespace_Name) Free
Where d.Tablespace_Name = Total.Tablespace_Name(+)
And d.Tablespace_Name = Free.Tablespace_Name(+)
And d.Extent_Management Like 'LOCAL'
And d.Contents Like 'TEMPORARY'
order by "表空间名称";
d.Status "状态",
d.Contents "类型",
d.Extent_Management "区管理",
Nvl(Total.Bytes, -1) "总容量(MB)",
Nvl((Total.Bytes - Free.Bytes), -1) "已使用(MB)",
Nvl(Round((1 - Free.Bytes / Total.Bytes) * 100, 2), -1) "使用占比",
Total.Autoextensible "自动增长",
Total.Increment_By "增长量",
Total.File_Name "文件地址"
From Sys.Dba_Tablespaces d,
(Select Tablespace_Name,
Round(Nvl(Sum(Bytes) / 1024 / 1024, 0), 2) Bytes,
File_Name,
Autoextensible,
Increment_By
From Dba_Data_Files
Group By Tablespace_Name, File_Name, Autoextensible, Increment_By) Total,
(Select Tablespace_Name,
Round(Nvl(Sum(Bytes) / 1024 / 1024, 0), 2) Bytes
From Dba_Free_Space
Group By Tablespace_Name) Free
Where d.Tablespace_Name = Total.Tablespace_Name(+)
And d.Tablespace_Name = Free.Tablespace_Name(+)
And Not
(d.Extent_Management Like 'LOCAL' And d.Contents Like 'TEMPORARY')
Union All
Select sysdate,d.Tablespace_Name "表空间名称",
d.Status "状态",
d.Contents "类型",
d.Extent_Management "区管理",
Nvl(Total.Bytes, -1) "总容量(MB)",
Nvl((Total.Bytes - Free.Bytes), -1) "已使用(MB)",
Nvl(Round((1 - Free.Bytes / Total.Bytes) * 100, 2), -1) "使用占比",
Total.Autoextensible "自动增长",
Total.Increment_By "增长量",
Total.File_Name "文件地址"
From Sys.Dba_Tablespaces d,
(Select Tablespace_Name,
Round(Nvl(Sum(Bytes) / 1024 / 1024, 0), 2) Bytes,
File_Name,
Autoextensible,
Increment_By
From Dba_Temp_Files
Group By Tablespace_Name, File_Name, Autoextensible, Increment_By) Total,
(Select Tablespace_Name,
Round(Nvl(Sum(Bytes_Cached) / 1024 / 1024, 0), 2) Bytes
From V$temp_Extent_Pool
Group By Tablespace_Name) Free
Where d.Tablespace_Name = Total.Tablespace_Name(+)
And d.Tablespace_Name = Free.Tablespace_Name(+)
And d.Extent_Management Like 'LOCAL'
And d.Contents Like 'TEMPORARY'
order by "表空间名称";