SQL:表空间、数据文件,以及使用率

By | 2014年7月17日

以下的SQL将呈现表空间及其包含的数据文件和使用情况。

————————————————————————
Ending。

打赏

5
说点什么

avatar
5 Comment threads
0 Thread replies
0 Followers
 
Most reacted comment
Hottest comment thread
5 Comment authors
GDSHelloworldDatabase Sourceadamhuan@wsit钻石之门 Recent comment authors

This site uses Akismet to reduce spam. Learn how your comment data is processed.

  Subscribe  
提醒
adamhuan@wsit
游客

col “Datafile List” for a90
set linesize 900
set pagesize 300

select
b.tablespace_name “Tablespace Name”,b.datafilelist “Datafile List”,b.totalmb “Total MB”,(b.totalmb – a.freemb) “Used MB”,a.freemb “Free MB”,
Round((1- a.freemb/b.totalmb)*100,2)||’ %’ “Usage Percent”
from
(select tablespace_name,sum(bytes)/1024/1024 freemb from dba_free_space group by tablespace_name) a,
(select tablespace_name,sum(bytes)/1024/1024 totalmb,wm_concat(file_name) datafilelist from dba_data_files group by tablespace_name) b
where
a.tablespace_name=b.tablespace_name
order by 6;

Database Source
游客
Database Source

col “Datafile List” for a80col “Tablespace Name” for a23set linesize 400

Helloworld
游客
Helloworld

set linesize 400col “Tablespace Name” for a23selectb.tablespace_name “Tablespace Name”,b.totalmb “Total MB”,a.freemb “Free MB”,Round((1- a.freemb/b.totalmb)*100,2)||’ %’ “Usage Percent”from(select tablespace_name,sum(bytes)/1024/1024 freemb from dba_free_space group by tablespace_name) a,(select tablespace_name,sum(bytes)/1024/1024 totalmb,wm_concat(file_name) datafilelist from dba_data_files group by tablespace_name) bwherea.tablespace_name=b.tablespace_nameorder by “Usage Percent” desc;

GDS
游客
GDS

set linesize 400col “Tablespace Name” for a23selectb.tablespace_name “Tablespace Name”,b.totalmb “Total MB”,a.freemb “Free MB”,Round((a.freemb/b.totalmb)*100,2)||’ %’ “Free Percent”from(select tablespace_name,sum(bytes)/1024/1024 freemb from dba_free_space group by tablespace_name) a,(select tablespace_name,sum(bytes)/1024/1024 totalmb,wm_concat(file_name) datafilelist from dba_data_files group by tablespace_name) bwherea.tablespace_name=b.tablespace_nameorder by “Free Percent” desc;