查询表空间使用情况

青旅半醒 2023-10-18 21:04 31阅读 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> 
   <th>TS_NAME</th> 
   <th>EXTENSIBLE</th> 
   <th>ALLOCATED</th> 
   <th>ALLOC_FREE</th> 
   <th>USED</th> 
   <th>PCT_USED</th> 
   <th>NE</th> 
   <th>PCT_USED_EXT</th> 
  </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>