ORACLE表空间使用情况及状态SQL

短命女 2023-10-18 21:09 32阅读 0赞

select  
tablespace\_name,  
max,  
alloc,  
used,  
round(100 \* used / alloc, 2) pct\_used\_alloc,  
round(100 \* used / max, 2) pct\_used\_max,  
status,  
logging,  
force\_logging,  
extent\_management,  
allocation\_type  
from  
(  
select  
t.status status,  
t.logging logging,  
t.force\_logging force\_logging,  
t.extent\_management extent\_management,  
t.allocation\_type allocation\_type,  
a.tablespace\_name tablespace\_name,  
round(a.bytes\_alloc , 2) alloc,  
round((a.bytes\_alloc - nvl(b.bytes\_free, 0)) , 2) used,  
round(a.maxbytes , 2) max  
from  
dba\_tablespaces t,  
(  
select  
f.tablespace\_name,  
sum(f.bytes) bytes\_alloc,  
sum(decode(f.autoextensible, 'YES', f.maxbytes, 'NO', f.bytes)) maxbytes  
from  
dba\_data\_files f  
group by  
tablespace\_name) a,  
(  
select  
f.tablespace\_name,  
sum(f.bytes) bytes\_free  
from  
dba\_free\_space f  
group by  
tablespace\_name) b  
where  
a.tablespace\_name = b.tablespace\_name(+)  
and a.tablespace\_name = t.tablespace\_name  
union all  
select  
t.status status,  
t.logging logging,  
t.force\_logging force\_logging,  
t.extent\_management extent\_management,  
t.allocation\_type allocation\_type,  
h.tablespace\_name tablespace\_name,  
round(sum(nvl(f.bytes, 0)) , 2) alloc,  
round(sum(nvl(p.bytes\_used, 0)) , 2) used,  
round(sum(decode(f.autoextensible, 'YES', f.maxbytes, 'NO', f.bytes)) , 2) max  
from  
dba\_tablespaces t,  
v$temp\_space\_header h,  
v$temp\_extent\_pool p,  
dba\_temp\_files f  
where  
p.file\_id(+) = h.file\_id  
and p.tablespace\_name(+) = h.tablespace\_name  
and f.file\_id = h.file\_id  
and f.tablespace\_name = h.tablespace\_name  
and t.tablespace\_name = h.tablespace\_name  
group by  
h.tablespace\_name,  
t.status,  
t.logging,  
t.force\_logging,  
t.extent\_management,  
t.allocation\_type )  
order by  
4

<table> 
 <tbody> 
  <tr> 
   <th>TABLESPACE_NAME</th> 
   <th>MAX</th> 
   <th>ALLOC</th> 
   <th>USED</th> 
   <th>PCT_USED_ALLOC</th> 
   <th>PCT_USED_MAX</th> 
   <th>STATUS</th> 
   <th>LOGGING</th> 
   <th>FORCE_LOGGING</th> 
   <th>EXTENT_MANAGEMENT</th> 
   <th>ALLOCATION_TYPE</th> 
  </tr> 
  <tr> 
   <td>TEMP</td> 
   <td>34,359,721,984</td> 
   <td>1,342,177,280</td> 
   <td>1,048,576</td> 
   <td>0.08</td> 
   <td>0</td> 
   <td>ONLINE</td> 
   <td>NOLOGGING</td> 
   <td>NO</td> 
   <td>LOCAL</td> 
   <td>UNIFORM</td> 
  </tr> 
  <tr> 
   <td>USERS</td> 
   <td>34,359,721,984</td> 
   <td>5,242,880</td> 
   <td>1,376,256</td> 
   <td>26.25</td> 
   <td>0</td> 
   <td>ONLINE</td> 
   <td>LOGGING</td> 
   <td>NO</td> 
   <td>LOCAL</td> 
   <td>SYSTEM</td> 
  </tr> 
  <tr> 
   <td>TESTTBS</td> 
   <td>68,719,443,968</td> 
   <td>209,715,200</td> 
   <td>2,097,152</td> 
   <td>1</td> 
   <td>0</td> 
   <td>ONLINE</td> 
   <td>LOGGING</td> 
   <td>NO</td> 
   <td>LOCAL</td> 
   <td>SYSTEM</td> 
  </tr> 
  <tr> 
   <td>UNDOTBS1</td> 
   <td>34,359,721,984</td> 
   <td>94,371,840</td> 
   <td>21,626,880</td> 
   <td>22.92</td> 
   <td>0.06</td> 
   <td>ONLINE</td> 
   <td>LOGGING</td> 
   <td>NO</td> 
   <td>LOCAL</td> 
   <td>SYSTEM</td> 
  </tr> 
  <tr> 
   <td>SYSAUX</td> 
   <td>34,359,721,984</td> 
   <td>587,202,560</td> 
   <td>553,451,520</td> 
   <td>94.25</td> 
   <td>1.61</td> 
   <td>ONLINE</td> 
   <td>LOGGING</td> 
   <td>NO</td> 
   <td>LOCAL</td> 
   <td>SYSTEM</td> 
  </tr> 
  <tr> 
   <td>SYSTEM</td> 
   <td>34,359,721,984</td> 
   <td>786,432,000</td> 
   <td>779,747,328</td> 
   <td>99.15</td> 
   <td>2.27</td> 
   <td>ONLINE</td> 
   <td>LOGGING</td> 
   <td>NO</td> 
   <td>LOCAL</td> 
   <td>SYSTEM</td> 
  </tr> 
 </tbody> 
</table>