SELECT d.tablespace_name TS_Name,
d.contents TS_Type,
d.status TS_Status,
d.extent_management TS_ExtentManagement,
trunc(NVL(a.bytes / 1024 / 1024, 0)) TS_Size,
trunc(NVL(a.bytes - NVL(f.bytes, 0), 0) / 1024 / 1024) TS_UsedSize,
trunc(NVL((a.bytes - NVL(f.bytes, 0)) / a.bytes * 100, 0)) TS_Used
FROM sys.dba_tablespaces d,
(select tablespace_name, sum(bytes) bytes
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.extent_management like 'LOCAL' AND d.contents like 'TEMPORARY')
UNION ALL
SELECT d.tablespace_name TS_Name,
d.contents TS_Type,
d.status TS_Status,
d.extent_management TS_ExtentManagement,
trunc(NVL(a.bytes / 1024 / 1024, 0)) TS_Size,
trunc(NVL(t.bytes, 0) / 1024 / 1024) TS_UsedSize,
trunc(NVL(t.bytes / a.bytes * 100, 0)) TS_Used
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'
order by TS_Name;
TS_NAME TS_TYPE TS_STATUS TS_EXTENTMANAGEMENT TS_SIZE_MB TS_USEDSIZE_MB TS_USED
------------------------------ --------- --------- ------------------- ---------- -------------- ----------
EXAMPLE PERMANENT ONLINE LOCAL 100 77 77
SYSAUX PERMANENT ONLINE LOCAL 240 238 99
SYSTEM PERMANENT ONLINE LOCAL 480 475 99
TEMP TEMPORARY ONLINE LOCAL 20 18 90
UNDOTBS1 UNDO ONLINE LOCAL 35 28 81
USERS PERMANENT ONLINE LOCAL 5 3 65
--查看数据文件大小
select a.tablespace_name,a.file_name,a.bytes/1024/1024 total_mb, (a.bytes - nvl(c.bytes, 0))/1024/1024 use_mb
from (select a.*,
d.STATUS file_status,
a.increment_by * b.block_size extendbytes
from dba_data_files a, dba_tablespaces b, v$datafile d
where a.tablespace_name = b.tablespace_name
and a.file_id = d.FILE#
/*and a.file_id = :file_id*/) a
left join (select file_id, sum(bytes) bytes
from dba_free_space
/*where file_id = :file_id*/
group by file_id) c on a.file_id = c.file_id
union all
select a.tablespace_name,a.file_name,a.bytes/1024/1024 total_mb, c.bytes/1024/1024 use_mb
from (select a.*,
d.STATUS file_status,
a.increment_by * b.block_size extendbytes
from dba_temp_files a, dba_tablespaces b, v$tempfile d
where a.tablespace_name= b.tablespace_name
and a.file_id = d.FILE#
/*and a.file_id = :file_id*/) a
left join (select file_id, sum(bytes_cached) bytes
from v$temp_extent_pool
/*where file_id = :file_id*/
group by file_id) c on a.file_id = c.file_id
TABLESPACE_NAME FILE_NAME TOTAL_MB USE_MB
------------------------------ -------------------------------------------------------------------------------- ---------- ----------
SYSTEM D:\ORACLE\PRODUCT\10.2.0\ORADATA\JINGYONG\SYSTEM01.DBF 480 475.5
UNDOTBS1 D:\ORACLE\PRODUCT\10.2.0\ORADATA\JINGYONG\UNDOTBS01.DBF 35 28.4375
USERS D:\ORACLE\PRODUCT\10.2.0\ORADATA\JINGYONG\USERS01.DBF 5 3.25
EXAMPLE D:\ORACLE\PRODUCT\10.2.0\ORADATA\JINGYONG\EXAMPLE01.DBF 100 77.6875
SYSAUX D:\ORACLE\PRODUCT\10.2.0\ORADATA\JINGYONG\SYSAUX01.DBF 240 238.8125
TEMP D:\ORACLE\PRODUCT\10.2.0\ORADATA\JINGYONG\TEMP01.DBF 20 18
