查询表空间使用情况

电玩女神 2023-10-18 21:07 36阅读 0赞

select ts\_name  
, extensible\_bytes extensible  
, allocated\_bytes allocated  
, alloc\_free  
, allocated\_bytes - alloc\_free used  
, 100 \* (allocated\_bytes - alloc\_free) / allocated\_bytes pct\_used  
, to\_number(decode(allocated\_bytes, extensible\_bytes, NULL,  
extensible\_bytes  
\- (allocated\_bytes - alloc\_free))) ne  
, to\_number(decode(allocated\_bytes, extensible\_bytes, NULL,  
100 \* (extensible\_bytes - (extensible\_bytes - (allocated\_bytes - alloc\_free)))  
/ extensible\_bytes)) pct\_used\_ext  
from (  
select a.tablespace\_name ts\_name  
, sum(decode(b.autoextensible, 'YES', b.maxbytes, b.bytes))  
/ count(distinct a.file\_id||'.'||a.block\_id) extensible\_bytes  
, sum(b.bytes)/count(distinct a.file\_id||'.'||a.block\_id) allocated\_bytes  
, sum(a.bytes)/count(distinct b.file\_id) alloc\_free  
from sys.dba\_free\_space a  
, sys.dba\_data\_files b  
where a.tablespace\_name = b.tablespace\_name (+)  
group by a.tablespace\_name  
, b.tablespace\_name)  
order by 6 desc;

<table> 
 <tbody> 
 <tr> 
 <td> TS_NAME </td> 
 <td> EXTENSIBLE </td> 
 <td> ALLOCATED </td> 
 <td> ALLOC_FREE </td> 
 <td> USED </td> 
 <td> PCT_USED </td> 
 <td> NE </td> 
 <td> PCT_USED_EXT </td> 
 </tr> 
 <tr> 
 <td> SYSTEM </td> 
 <td> 34,359,721,984 </td> 
 <td> 786,432,000 </td> 
 <td> 6,684,672 </td> 
 <td> 779,747,328 </td> 
 <td> 99.15 </td> 
 <td> 33,579,974,656 </td> 
 <td> 2.2693644854 </td> 
 </tr> 
 <tr> 
 <td> SYSAUX </td> 
 <td> 34,359,721,984 </td> 
 <td> 587,202,560 </td> 
 <td> 37,617,664 </td> 
 <td> 549,584,896 </td> 
 <td> 93.59375 </td> 
 <td> 33,810,137,088 </td> 
 <td> 1.5995033262 </td> 
 </tr> 
 <tr> 
 <td> USERS </td> 
 <td> 34,359,721,984 </td> 
 <td> 5,242,880 </td> 
 <td> 3,866,624 </td> 
 <td> 1,376,256 </td> 
 <td> 26.25 </td> 
 <td> 34,358,345,728 </td> 
 <td> 0.004005434 </td> 
 </tr> 
 <tr> 
 <td> UNDOTBS1 </td> 
 <td> 34,359,721,984 </td> 
 <td> 94,371,840 </td> 
 <td> 73,465,856 </td> 
 <td> 20,905,984 </td> 
 <td> 22.1527777778 </td> 
 <td> 34,338,816,000 </td> 
 <td> 0.0608444504 </td> 
 </tr> 
 <tr> 
 <td> TESTTBS </td> 
 <td> 68,719,443,968 </td> 
 <td> 209,715,200 </td> 
 <td> 207,618,048 </td> 
 <td> 2,097,152 </td> 
 <td> 1 </td> 
 <td> 68,717,346,816 </td> 
 <td> 0.0030517593 </td> 
 </tr> 
 </tbody> 
</table>