oracle中临时表空间剩余大小,查询Oracle剩余表空间语句+temp临时表空间使用率语句...

剩余表空间语句，很多人都会用会写，temp临时表空间使用率语句就复杂点了。经过日常使用和搜索回来的资料，下面总结下吧！语句都验证过的，sys用户可用

--查看剩余表空间使用情况 语句

set pagesize 100;

set linesize 200;

select df.tablespace_name "表空间名 ",totalspace "总空间M",freespace " 剩余空间M",round((1 -freespace/totalspace)*100, 2) "使用率 %",(totalspace-freespace)/1024 " 已用空间G"

from

(select tablespace_name,round( sum(bytes)/1024 /1024) totalspace

from dba_data_files

group by tablespace_name) df,

(select tablespace_name,round( sum(bytes)/1024 /1024) freespace

from dba_free_space

group by tablespace_name) fs

where df.tablespace_name=fs.tablespace_name ORDER BY "使用率 %" DESC;

=============================================================================

--查看oracle temp临时表空间使用率

SELECT d.tablespace_name tableSpaceName,

NVL(a.BYTES / 1024 / 1024, 0) totalSize,

NVL(a.BYTES / 1024 / 1024, 0) - NVL(t.BYTES, 0) / 1024 / 1024 freeSize,

NVL(t.BYTES, 0) / 1024 / 1024 usedSize,

round(NVL(t.BYTES / a.BYTES * 100, 0), 2) usedPercent

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';

=============================================================================

查看oracle 剩余表空间和temp临时表空间使用率

select *

from (SELECT a.tablespace_name tableSpaceName,

NVL(a.BYTES / 1024 / 1024,0) totalSize,

NVL(b.largest / 1024 / 1024,0) freeSize,

NVL((a.BYTES - b.BYTES) / 1024 / 1024,0) usedSize,

round(NVL((a.BYTES - b.BYTES) / a.BYTES * 100, 0), 2) usedPercent

from (SELECT tablespace_name, sum(BYTES) bytes

FROM dba_data_files

GROUP BY tablespace_name) a,

(SELECT tablespace_name, sum(BYTES) bytes, max(BYTES) largest

FROM dba_free_space

GROUP BY tablespace_name) b

WHERE a.tablespace_name = b.tablespace_name

ORDER BY a.tablespace_name)

UNION

SELECT d.tablespace_name tableSpaceName,

NVL(a.BYTES / 1024 / 1024, 0) totalSize,

NVL(a.BYTES / 1024 / 1024, 0) - NVL(t.BYTES, 0) / 1024 / 1024 freeSize,

NVL(t.BYTES, 0) / 1024 / 1024 usedSize,

round(NVL(t.BYTES / a.BYTES * 100, 0), 2) usedPercent

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';