DBMS_SPACE包中常用的过程

分手后的思念是犯贱 2022-02-05 04:47 97阅读 0赞

s\_space程序包中包括查找各种段中使用了多少空间以及还有多少空间可用，主要的一下三个过程

unused\_space过程提供一个对象未使用的空间信息

free\_blocks过程提供一个段中的可用块数

space\_usage过程给出块空间使用细节

下面过程unused\_space过程演示:

SQL> begin  
  2  for i in 1..5000 loop  
  3  insert into name values(i,i);  
  4  end loop;  
  5  commit;  
  6  end;  
  7  /

PL/SQL 过程已成功完成。

SQL> /

PL/SQL 过程已成功完成。

SQL> /

PL/SQL 过程已成功完成。

SQL> /

PL/SQL 过程已成功完成。

SQL> select count(1) from name;

COUNT(1)  
\----------  
     20000

SQL> delete from name where rownum <6000;

已删除5999行。

SQL> select count(1) from name;

COUNT(1)  
\----------  
     14001

SQL> show user  
USER 为 "HA"  
SQL> set serveroutput on  
SQL>  
SQL>  
SQL> declare  
  2  v\_total\_blocks number;  
  3  v\_total\_bytes number;  
  4  v\_unused\_blocks number;  
  5  v\_unused\_bytes number;  
  6  v\_last\_used\_extent\_file\_id number;  
  7  v\_last\_used\_extent\_block\_id number;  
  8  v\_last\_used\_block number;  
  9  begin  
 10  DBMS\_SPACE.UNUSED\_SPACE (  
 11  segment\_owner =>'HA',  
 12  segment\_name =>'NAME',  
 13  segment\_type =>'TABLE',  
 14  total\_blocks =>v\_total\_blocks ,  
 15  total\_bytes =>v\_total\_bytes  ,  
 16  unused\_blocks =>v\_unused\_blocks ,  
 17  unused\_bytes =>v\_unused\_bytes ,  
 18  last\_used\_extent\_file\_id =>v\_last\_used\_extent\_file\_id ,  
 19  last\_used\_extent\_block\_id =>v\_last\_used\_extent\_block\_id ,  
 20  last\_used\_block =>v\_last\_used\_block ,  
 21  partition\_name => NULL);  
 22  dbms\_output.put\_line ('number of total blocks :' ||v\_total\_blocks);  
 23  dbms\_output.put\_line ('number of total bytes :' ||v\_total\_bytes);  
 24  dbms\_output.put\_line ('number of unused blocks  :' ||v\_unused\_blocks);  
 25  dbms\_output.put\_line ('number of unused bytes :' ||v\_unused\_bytes );  
 26  dbms\_output.put\_line ('number of last used extent fileid:' ||v\_last\_used\_ex  
tent\_file\_id);  
 27  dbms\_output.put\_line ('number of last used extent blockid :'||v\_last\_used\_e  
xtent\_block\_id);  
 28  end;  
 29  /  
number of total blocks :48  
number of total bytes :393216  
number of unused blocks  :0  
number of unused bytes :0  
number of last used extent fileid:4  
number of last used extent blockid :560

PL/SQL 过程已成功完成。

SQL>

dbms\_space包的其他过程和方法请参见PL/SQL Packages and Types Reference