查询ORACLE自动扩展表空间占用和状态

爱被打了一巴掌 2023-10-18 21:03 60阅读 0赞

select tablespace\_name,  
max\_gb,  
used\_gb,  
round(100 \* used\_gb / max\_gb) pct\_used,  
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 - nvl(b.bytes\_free, 0)) / power(2, 30),  
2) used\_gb,  
round(a.maxbytes / power(2, 30), 2) max\_gb  
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(p.bytes\_used, 0)) / power(2, 30), 2) used\_gb,  
round(sum(decode(f.autoextensible,  
'YES',  
f.maxbytes,  
'NO',  
f.bytes)) / power(2, 30),  
2) max\_gb  
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_GB</th> 
   <th>USED_GB</th> 
   <th>PCT_USED</th> 
   <th>STATUS</th> 
   <th>LOGGING</th> 
   <th>FORCE_LOGGING</th> 
   <th>EXTENT_MANAGEMENT</th> 
   <th>ALLOCATION_TYPE</th> 
  </tr> 
  <tr> 
   <td>USERS</td> 
   <td>32</td> 
   <td>0</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>0.1</td> 
   <td>0</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>32</td> 
   <td>0.02</td> 
   <td>0</td> 
   <td>ONLINE</td> 
   <td>LOGGING</td> 
   <td>NO</td> 
   <td>LOCAL</td> 
   <td>SYSTEM</td> 
  </tr> 
  <tr> 
   <td>TEMP</td> 
   <td>32</td> 
   <td>0</td> 
   <td>0</td> 
   <td>ONLINE</td> 
   <td>NOLOGGING</td> 
   <td>NO</td> 
   <td>LOCAL</td> 
   <td>UNIFORM</td> 
  </tr> 
  <tr> 
   <td>SYSTEM</td> 
   <td>32</td> 
   <td>0.73</td> 
   <td>2</td> 
   <td>ONLINE</td> 
   <td>LOGGING</td> 
   <td>NO</td> 
   <td>LOCAL</td> 
   <td>SYSTEM</td> 
  </tr> 
  <tr> 
   <td>SYSAUX</td> 
   <td>32</td> 
   <td>0.5</td> 
   <td>2</td> 
   <td>ONLINE</td> 
   <td>LOGGING</td> 
   <td>NO</td> 
   <td>LOCAL</td> 
   <td>SYSTEM</td> 
  </tr> 
 </tbody> 
</table>

<table> 
 <tbody> 
  <tr> 
   <th>TABLESPACE_NAME</th> 
   <th>MAX_GB</th> 
   <th>USED_GB</th> 
   <th>PCT_USED</th> 
   <th>STATUS</th> 
   <th>LOGGING</th> 
   <th>FORCE_LOGGING</th> 
   <th>EXTENT_MANAGEMENT</th> 
   <th>ALLOCATION_TYPE</th> 
  </tr> 
  <tr> 
   <td>USERS</td> 
   <td>32</td> 
   <td>0</td> 
   <td>0</td> 
   <td>ONLINE</td> 
   <td>LOGGING</td> 
   <td>NO</td> 
   <td>LOCAL</td> 
   <td>SYSTEM</td> 
  </tr> 
  <tr> 
   <td>TEMP</td> 
   <td>32</td> 
   <td>0</td> 
   <td>0</td> 
   <td>ONLINE</td> 
   <td>NOLOGGING</td> 
   <td>NO</td> 
   <td>LOCAL</td> 
   <td>UNIFORM</td> 
  </tr> 
  <tr> 
   <td>UNDOTBS1</td> 
   <td>32</td> 
   <td>0.02</td> 
   <td>0</td> 
   <td>ONLINE</td> 
   <td>LOGGING</td> 
   <td>NO</td> 
   <td>LOCAL</td> 
   <td>SYSTEM</td> 
  </tr> 
  <tr> 
   <td>SYSTEM</td> 
   <td>32</td> 
   <td>0.73</td> 
   <td>2</td> 
   <td>ONLINE</td> 
   <td>LOGGING</td> 
   <td>NO</td> 
   <td>LOCAL</td> 
   <td>SYSTEM</td> 
  </tr> 
  <tr> 
   <td>SYSAUX</td> 
   <td>32</td> 
   <td>0.5</td> 
   <td>2</td> 
   <td>ONLINE</td> 
   <td>LOGGING</td> 
   <td>NO</td> 
   <td>LOCAL</td> 
   <td>SYSTEM</td> 
  </tr> 
  <tr> 
   <td>TESTTBS</td> 
   <td>&nbsp;</td> 
   <td>&nbsp;</td> 
   <td>&nbsp;</td> 
   <td>OFFLINE</td> 
   <td>LOGGING</td> 
   <td>NO</td> 
   <td>LOCAL</td> 
   <td>SYSTEM</td> 
  </tr> 
 </tbody> 
</table>