有關表空間查詢的sql指令碼

select f.tablespace_name,a.total,u.used,f.free,round((u.used/a.total)*100) "% used",
  round((f.free/a.total)*100) "% Free"
  from
  (select tablespace_name, sum(bytes/(1024*1024)) total
       from dba_data_files group by tablespace_name) a,
  (select tablespace_name, round(sum(bytes/(1024*1024))) used
       from dba_extents group by tablespace_name) u,
  (select tablespace_name, round(sum(bytes/(1024*1024))) free
       from dba_free_space group by tablespace_name) f
WHERE a.tablespace_name = f.tablespace_name
and a.tablespace_name = u.tablespace_name;

--檢視當前使用者表空間段的分配情況
select tablespace_name,sum(bytes)/(1024*1024),sum(blocks),sum(extents) from user_segments
group by tablespace_name  ---當前使用者為nihl 查詢結果就是使用者nihl的表空間分配情況

--檢查各使用者表空間段情況
select owner,segment_type,tablespace_name,sum(bytes)/(1024*1024),sum(blocks),sum(extents) from dba_segments
where wner='NIHL'
group by owner,segment_type,tablespace_name
--檢查當前使用者表空間區的使用情況
select tablespace_name,sum(extent_id),sum(bytes)/(1024*1024),sum(blocks) from user_extents
group by tablespace_name

--檢查所有使用者表空間區的使用情況
select owner,segment_type,tablespace_name,sum(bytes)/(1024*1024),sum(blocks) from dba_extents
where wner='NIHL'
group by owner,segment_type,tablespace_name

select owner,tablespace_name,sum(bytes)/(1024*1024),sum(blocks) from dba_extents
where wner='NIHL'
group by owner,tablespace_name

---檢視當前使用者空閒表空間的情況
select tablespace_name,sum(bytes)/(1024*1024),sum(blocks) from user_free_space
group by tablespace_name

select tablespace_name,sum(bytes)/(1024*1024),sum(blocks) from dba_free_space
group by tablespace_name

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/14766526/viewspace-557771/，如需轉載，請註明出處，否則將追究法律責任。