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

怼烎@ 2023-10-18 21:03 30阅读 0赞

<?php

$conn = oci\_connect('system', ' password', '127.0.0.1/orcl');  
if (!$conn) \{  
$e = oci\_error();  
trigger\_error(htmlentities($e\['message'\], ENT\_QUOTES), E\_USER\_ERROR);  
\}

// Prepare the statement  
$stid = oci\_parse($conn, "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");  
if (!$stid) \{  
$e = oci\_error($conn);  
trigger\_error(htmlentities($e\['message'\], ENT\_QUOTES), E\_USER\_ERROR);  
\}

// Perform the logic of the query  
$r = oci\_execute($stid);  
if (!$r) \{  
$e = oci\_error($stid);  
trigger\_error(htmlentities($e\['message'\], ENT\_QUOTES), E\_USER\_ERROR);  
\}

// Fetch the results of the query  
print "<table border='1'>\\n";  
echo "<tr>";  
$ncols = oci\_num\_fields($stid);

for ($i = 1; $i <= $ncols; $i++) \{  
$column\_name = oci\_field\_name($stid, $i);  
echo "<td>$column\_name</td>";  
\}  
echo "</tr>";

while ($row = oci\_fetch\_array($stid, OCI\_ASSOC+OCI\_RETURN\_NULLS)) \{  
print "<tr>\\n";  
foreach ($row as $item) \{  
print " <td>" . ($item !== null ? htmlentities($item, ENT\_QUOTES) : "&nbsp;") . "</td>\\n";  
\}  
print "</tr>\\n";  
\}  
print "</table>\\n";

oci\_free\_statement($stid);  
oci\_close($conn);

<table> 
 <tbody> 
  <tr> 
   <td>TABLESPACE_NAME</td> 
   <td>MAX_GB</td> 
   <td>USED_GB</td> 
   <td>PCT_USED</td> 
   <td>STATUS</td> 
   <td>LOGGING</td> 
   <td>FORCE_LOGGING</td> 
   <td>EXTENT_MANAGEMENT</td> 
   <td>ALLOCATION_TYPE</td> 
  </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>64</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>.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>.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>.51</td> 
   <td>2</td> 
   <td>ONLINE</td> 
   <td>LOGGING</td> 
   <td>NO</td> 
   <td>LOCAL</td> 
   <td>SYSTEM</td> 
  </tr> 
 </tbody> 
</table>