SQL：表空间、数据文件，以及使用率
以下的SQL将呈现表空间及其包含的数据文件和使用情况。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 |
SQL> set linesize 300; SQL> select 2 b.tablespace_name "Tablespace Name",b.datafilelist "Datafile List",b.totalmb "Total MB",(b.totalmb - a.freemb) "Used MB",a.freemb "Free MB", 3 Round((1- a.freemb/b.totalmb)*100,2)||' %' "Usage Percent" 4 from 5 (select tablespace_name,sum(bytes)/1024/1024 freemb from dba_free_space group by tablespace_name) a, 6 (select tablespace_name,sum(bytes)/1024/1024 totalmb,wm_concat(file_name) datafilelist from dba_data_files group by tablespace_name) b 7 where 8 a.tablespace_name=b.tablespace_name 9 ; Tablespace Name Datafile List Total MB Used MB Free MB Usage Percent ------------------------------ -------------------------------------------------------------------------------- ---------- ---------- ---------- ------------------------------------------ UNDOTBS1 /Data/system/misrepdb/undotbs01.dbf 200 12.3125 187.6875 6.16 % SYSAUX /Data/system/misrepdb/sysaux01.dbf 300 271.875 28.125 90.63 % USERS /Data/system/misrepdb/users01.dbf 5 2 3 40 % ADAMHUAN /Data/system/misrepdb/adamhuan01.dbf,/Data/system/misrepdb/adamhuan02.dbf 220 .125 219.875 .06 % SYSTEM /Data/system/misrepdb/system01.dbf 300 201.6875 98.3125 67.23 % UNDOTBS2 /Data/system/misrepdb/undotbs02.dbf 200 4.3125 195.6875 2.16 % 6 rows selected. SQL> |
————————————————————————
Ending。
The Command is：
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
;
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;
col “Datafile List” for a80col “Tablespace Name” for a23set linesize 400
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;
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;