ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 收缩表段(shrink space)

收缩表段(shrink space)

原创 Linux操作系统 作者:beatony 时间:2011-07-29 14:06:29 0 删除 编辑
 

收缩表段(shrink space)

分类: Oracle 性能优化 14人阅读 评论(0) 收藏 举报
--====================
-- 收缩表段(shrink space)
--====================


一、表的增长方式
    当表被创建后,随着记录的不断插入,组成表的区间会被填满,如果启用了自动扩展,则当区间填满后,会分配新的区间。假定高水
    位线随着记录的增加从最左端往右端来移动,当到底部区间的尾端时,则新的区间将会被分配。
    
二、表可收缩的原理
    随着记录的增加高水位线不断的右移,记录的删除不会导致高水位线往回(左)移动
    删除记录后的空闲空间(高水位线左侧)尽管可以使用,但其稀疏性导致空间空闲
    注:完整的表扫描所耗费的时间不会因为记录的减少(删除)而减少

三、使用 alter table tbname shrink space 来收缩表段
    1. 实现原理
        实质上构造一个新表(在内部表现为一系列的DML操作,即将副本插入新位置,删除原来位置的记录)
        靠近末尾处(右端)数据块中的记录往开始处(左端)的空闲空间处移动(DML操作),不会引起DML触发器
        当所有可能的移动被完成,高水位线将会往左端移动(DDL操作)
        新的高水位线右边的空闲空间被释放(DDL操作)
        
    2. 实现前提条件
        必须启用行记录转移(enable row movement)
        仅仅适用于堆表,且位于自动段空间管理的表空间(堆表包括:标准表,分区表,物化视图容器,物化视图日志表)
    
    3. 不能实现收缩的表
        群集表
        具有LONG类型列的表
        LOB段(尽管表本身可以被缩小),注,10gR2以后版本支持对LOB段的收缩
        具有基于提交的物化视图的表(因为禁用了触发器)
        具有rowid物化视图的表(因为rowid发生了变化)
        IOT映射表IOT溢出段
        索引基于函数的表
        未启用行记录转移的堆表
        
    4. 段收缩的优点
        提高缓存利用率,提高OLTP的性能
         减少磁盘I/O,提高访问速度,节省磁盘空间
         段收缩是在线的,索引在段收缩期间维护,不要求额外的磁盘空间
         
    5. 两个选项
        cascade:缩小表及其索引,并移动高水位线,释放空间
        compact:仅仅是缩小表和索引,并不移动高水位线,不释放空间
        alter table tbname shrink space 相当于带cascade参数

四、实战演习

1. 查看需要收缩的表段的基本情况,此处为表big_table   
  1. SQL> col SEGMENT_NAME format a20                                                                                         
  2. SQL> select OWNER,SEGMENT_NAME,BYTES/1024/1024,BLOCKS from dba_segments                                                  
  3.   2  where owner='SCOTT' and SEGMENT_NAME='BIG_TABLE';                                                                   
  4.                                                                                                                          
  5. OWNER                          SEGMENT_NAME         BYTES/1024/1024     BLOCKS                                           
  6. ------------------------------ -------------------- --------------- ----------                                            
  7. SCOTT                          BIG_TABLE                        120      15360                                           
  8.                                                                                                                          
  9. SQL> select table_name,blocks,empty_blocks from dba_tables                                                               
  10.   2  where table_name='big_table' and owner='scott';    -->未更新统计信息前BLOCKS,EMPTY_BLOCKS列为空                     
  11.                                                                                                                          
  12. TABLE_NAME                         BLOCKS EMPTY_BLOCKS                                                                   
  13. ------------------------------ ---------- ------------                                                                    
  14. BIG_TABLE                                                                                                                
  15.                                                                                                                          
  16. SQL> exec dbms_stats.gather_table_stats(ownname=>'SCOTT',tabname=>'BIG_TABLE',estimate_percent=>30);                     
  17.                                                                                                                          
  18. PL/SQL procedure successfully completed.                                                                                 
  19.                                                                                                                          
  20. SQL> select TABLE_NAME,BLOCKS,EMPTY_BLOCKS from dba_tables                                                               
  21.   2  where TABLE_NAME='BIG_TABLE' and owner='SCOTT';                                                                     
  22.                                                                                                                          
  23. TABLE_NAME                         BLOCKS EMPTY_BLOCKS                                                                   
  24. ------------------------------ ---------- ------------                                                                    
  25. BIG_TABLE                           14590            0   -->使用gather_table_stats时不会统计EMPTY_BLOCKS块                
  26.                                                                                                                          
  27. SQL> analyze table big_table compute statistics;         -->使用analyze更新统计信息后EMPTY_BLOCKS得到数据                 
  28.                                                                                                                          
  29. Table analyzed.                                                                                                          
  30.                                                                                                                          
  31. SQL> select TABLE_NAME,BLOCKS,EMPTY_BLOCKS from dba_tables                                                               
  32.   2  where TABLE_NAME='BIG_TABLE' and owner='SCOTT';                                                                     
  33.                                                                                                                          
  34. TABLE_NAME                         BLOCKS EMPTY_BLOCKS                                                                   
  35. ------------------------------ ---------- ------------                                                                    
  36. BIG_TABLE                           14590          770                                                                   
  37.                                                                                                                          
  38. SQL> set serveroutput on;                                                                                                
  39. SQL> exec show_space('BIG_TABLE','SCOTT');                -->使用show_space过程或的BIG_TABLE上的空间分配信息              
  40. Unformatted Blocks .....................               0                                                                 
  41. FS1 Blocks (0-25) ......................               0  -->空闲度为0-25%的块数。FS1,FS2,FS3,FS4为空闲度所占的百分比     
  42. FS2 Blocks (25-50) .....................               0                                                                 
  43. FS3 Blocks (50-75) .....................               0                                                                 
  44. FS4 Blocks (75-100).....................               0                                                                 
  45. Full Blocks ............................          14,427                                                                 
  46. Total Blocks............................          15,360                                                                 
  47. Total Bytes.............................     125,829,120                                                                 
  48. Total MBytes............................             120                                                                 
  49. Unused Blocks...........................             770                                                                 
  50. Unused Bytes............................       6,307,840                                                                 
  51. Last Used Ext FileId....................               4                                                                 
  52. Last Used Ext BlockId...................          16,521                                                                 
  53. Last Used Block.........................             254                                                                 
  54.                                                                                                                          
  55. PL/SQL procedure successfully completed.                                                                                 
SQL> col SEGMENT_NAME format a20 SQL> select OWNER,SEGMENT_NAME,BYTES/1024/1024,BLOCKS from dba_segments 2 where wner='SCOTT' and SEGMENT_NAME='BIG_TABLE'; OWNER SEGMENT_NAME BYTES/1024/1024 BLOCKS ------------------------------ -------------------- --------------- ---------- SCOTT BIG_TABLE 120 15360 SQL> select table_name,blocks,empty_blocks from dba_tables 2 where table_name='big_table' and wner='scott'; -->未更新统计信息前BLOCKS,EMPTY_BLOCKS列为空 TABLE_NAME BLOCKS EMPTY_BLOCKS ------------------------------ ---------- ------------ BIG_TABLE SQL> exec dbms_stats.gather_table_stats(ownname=>'SCOTT',tabname=>'BIG_TABLE',estimate_percent=>30); PL/SQL procedure successfully completed. SQL> select TABLE_NAME,BLOCKS,EMPTY_BLOCKS from dba_tables 2 where TABLE_NAME='BIG_TABLE' and wner='SCOTT'; TABLE_NAME BLOCKS EMPTY_BLOCKS ------------------------------ ---------- ------------ BIG_TABLE 14590 0 -->使用gather_table_stats时不会统计EMPTY_BLOCKS块 SQL> analyze table big_table compute statistics; -->使用analyze更新统计信息后EMPTY_BLOCKS得到数据 Table analyzed. SQL> select TABLE_NAME,BLOCKS,EMPTY_BLOCKS from dba_tables 2 where TABLE_NAME='BIG_TABLE' and wner='SCOTT'; TABLE_NAME BLOCKS EMPTY_BLOCKS ------------------------------ ---------- ------------ BIG_TABLE 14590 770 SQL> set serveroutput on; SQL> exec show_space('BIG_TABLE','SCOTT'); -->使用show_space过程或的BIG_TABLE上的空间分配信息 Unformatted Blocks ..................... 0 FS1 Blocks (0-25) ...................... 0 -->空闲度为0-25%的块数。FS1,FS2,FS3,FS4为空闲度所占的百分比 FS2 Blocks (25-50) ..................... 0 FS3 Blocks (50-75) ..................... 0 FS4 Blocks (75-100)..................... 0 Full Blocks ............................ 14,427 Total Blocks............................ 15,360 Total Bytes............................. 125,829,120 Total MBytes............................ 120 Unused Blocks........................... 770 Unused Bytes............................ 6,307,840 Last Used Ext FileId.................... 4 Last Used Ext BlockId................... 16,521 Last Used Block......................... 254 PL/SQL procedure successfully completed.
2. 删除记录之后,进行收缩表段   
  1. SQL> delete from big_table where owner in ('SCOTT','SYSTEM');   -->删除记录                                    
  2.                                                                                                               
  3. 8715 rows deleted.                                                                                            
  4.                                                                                                               
  5. SQL> commit;                                                                                                  
  6.                                                                                                               
  7. Commit complete.                                                                                              
  8.                                                                                                               
  9. SQL> alter table big_table shrink space;                        -->实施shrink,提示没有启用ROW MOVEMENT         
  10. alter table big_table shrink space                                                                            
  11. *                                                                                                             
  12. ERROR at line 1:                                                                                              
  13. ORA-10636: ROW MOVEMENT is not enabled                                                                        
  14.                                                                                                               
  15. SQL> alter table big_table enable row movement;                 -->开启row movement                            
  16.                                                                                                               
  17. Table altered.                                                                                                
  18.                                                                                                               
  19. SQL> alter table big_table shrink space;                        -->shrink成功                                  
  20.                                                                                                               
  21. Table altered.                                                                                                
  22.                                                                                                               
  23. SQL> exec show_space('BIG_TABLE','SCOTT');         -->从下面的结果中可以看到块数以及总大小已经变小             
  24. Unformatted Blocks .....................               0                                                      
  25. FS1 Blocks (0-25) ......................               1                                                      
  26. FS2 Blocks (25-50) .....................               1                                                      
  27. FS3 Blocks (50-75) .....................               0                                                      
  28. FS4 Blocks (75-100).....................               0                                                      
  29. Full Blocks ............................          14,318                                                      
  30. Total Blocks............................          14,488                                                      
  31. Total Bytes.............................     118,685,696                                                      
  32. Total MBytes............................             113                                                      
  33. Unused Blocks...........................               5                                                      
  34. Unused Bytes............................          40,960                                                      
  35. Last Used Ext FileId....................               4                                                      
  36. Last Used Ext BlockId...................          16,521                                                      
  37. Last Used Block.........................             147                                                      
  38.                                                                                                               
  39. PL/SQL procedure successfully completed.                                                                      
SQL> delete from big_table where owner in ('SCOTT','SYSTEM'); -->删除记录 8715 rows deleted. SQL> commit; Commit complete. SQL> alter table big_table shrink space; -->实施shrink,提示没有启用ROW MOVEMENT alter table big_table shrink space * ERROR at line 1: ORA-10636: ROW MOVEMENT is not enabled SQL> alter table big_table enable row movement; -->开启row movement Table altered. SQL> alter table big_table shrink space; -->shrink成功 Table altered. SQL> exec show_space('BIG_TABLE','SCOTT'); -->从下面的结果中可以看到块数以及总大小已经变小 Unformatted Blocks ..................... 0 FS1 Blocks (0-25) ...................... 1 FS2 Blocks (25-50) ..................... 1 FS3 Blocks (50-75) ..................... 0 FS4 Blocks (75-100)..................... 0 Full Blocks ............................ 14,318 Total Blocks............................ 14,488 Total Bytes............................. 118,685,696 Total MBytes............................ 113 Unused Blocks........................... 5 Unused Bytes............................ 40,960 Last Used Ext FileId.................... 4 Last Used Ext BlockId................... 16,521 Last Used Block......................... 147 PL/SQL procedure successfully completed.
3. 验证cascade与compact的差异   
  1. SQL> delete from big_table where rownum<8000;     -->再次删除一些记录                                                   
  2.                                                                                                                        
  3. 7999 rows deleted.                                                                                                     
  4.                                                                                                                        
  5. SQL> alter table big_table shrink space compact;  -->使用compact方式收缩表段                                            
  6.                                                                                                                        
  7. Table altered.                                                                                                         
  8.                                                                                                                        
  9. SQL> exec show_space('BIG_TABLE','SCOTT');                                                                             
  10. Unformatted Blocks .....................               0                                                               
  11. FS1 Blocks (0-25) ......................               1                                                               
  12. FS2 Blocks (25-50) .....................               2                                                               
  13. FS3 Blocks (50-75) .....................               0                                                               
  14. FS4 Blocks (75-100).....................             103                                                               
  15. Full Blocks ............................          14,214 --仅有的变化为14318-14214=104块,即完全填满的数据块减少了104块   
  16. Total Blocks............................          14,488 --数据的总块数及总大小并没有减少,即未移动高水位线             
  17. Total Bytes.............................     118,685,696                                                               
  18. Total MBytes............................             113                                                               
  19. Unused Blocks...........................               5                                                               
  20. Unused Bytes............................          40,960                                                               
  21. Last Used Ext FileId....................               4                                                               
  22. Last Used Ext BlockId...................          16,521                                                               
  23. Last Used Block.........................             147                                                               
  24.                                                                                                                        
  25. PL/SQL procedure successfully completed.                                                                               
  26.                                                                                                                        
  27. SQL> alter table big_table shrink space cascade;  -->使用cascade方式收缩,                                              
  28.                                                                                                                        
  29. Table altered.                                                                                                         
  30.                                                                                                                        
  31. SQL> exec show_space('BIG_TABLE','SCOTT');                                                                             
  32. Unformatted Blocks .....................               0                                                               
  33. FS1 Blocks (0-25) ......................               1                                                               
  34. FS2 Blocks (25-50) .....................               2                                                               
  35. FS3 Blocks (50-75) .....................               0                                                               
  36. FS4 Blocks (75-100).....................               0                                                               
  37. Full Blocks ............................          14,214                                                               
  38. Total Blocks............................          14,384   -->总块数及总大小均已减少                                    
  39. Total Bytes.............................     117,833,728                                                               
  40. Total MBytes............................             112                                                               
  41. Unused Blocks...........................               4                                                               
  42. Unused Bytes............................          32,768                                                               
  43. Last Used Ext FileId....................               4                                                               
  44. Last Used Ext BlockId...................          16,521                                                               
  45. Last Used Block.........................              44                                                               
  46.                                                                                                                        
  47. PL/SQL procedure successfully completed.                                                                               
  48.                                                                                                                        
  49. -->收缩之后索引依然有效                                                                                                 
  50. SQL> select OWNER,INDEX_NAME,STATUS from dba_indexes where TABLE_NAME='BIG_TABLE';                                     
  51.                                                                                                                        
  52. OWNER                          INDEX_NAME                     STATUS                                                   
  53. ------------------------------ ------------------------------ --------                                                  
  54. SCOTT                          BIG_TABLE_PK                   VALID                                                    
SQL> delete from big_table where rownum<8000; -->再次删除一些记录 7999 rows deleted. SQL> alter table big_table shrink space compact; -->使用compact方式收缩表段 Table altered. SQL> exec show_space('BIG_TABLE','SCOTT'); Unformatted Blocks ..................... 0 FS1 Blocks (0-25) ...................... 1 FS2 Blocks (25-50) ..................... 2 FS3 Blocks (50-75) ..................... 0 FS4 Blocks (75-100)..................... 103 Full Blocks ............................ 14,214 --仅有的变化为14318-14214=104块,即完全填满的数据块减少了104块 Total Blocks............................ 14,488 --数据的总块数及总大小并没有减少,即未移动高水位线 Total Bytes............................. 118,685,696 Total MBytes............................ 113 Unused Blocks........................... 5 Unused Bytes............................ 40,960 Last Used Ext FileId.................... 4 Last Used Ext BlockId................... 16,521 Last Used Block......................... 147 PL/SQL procedure successfully completed. SQL> alter table big_table shrink space cascade; -->使用cascade方式收缩, Table altered. SQL> exec show_space('BIG_TABLE','SCOTT'); Unformatted Blocks ..................... 0 FS1 Blocks (0-25) ...................... 1 FS2 Blocks (25-50) ..................... 2 FS3 Blocks (50-75) ..................... 0 FS4 Blocks (75-100)..................... 0 Full Blocks ............................ 14,214 Total Blocks............................ 14,384 -->总块数及总大小均已减少 Total Bytes............................. 117,833,728 Total MBytes............................ 112 Unused Blocks........................... 4 Unused Bytes............................ 32,768 Last Used Ext FileId.................... 4 Last Used Ext BlockId................... 16,521 Last Used Block......................... 44 PL/SQL procedure successfully completed. -->收缩之后索引依然有效 SQL> select OWNER,INDEX_NAME,STATUS from dba_indexes where TABLE_NAME='BIG_TABLE'; OWNER INDEX_NAME STATUS ------------------------------ ------------------------------ -------- SCOTT BIG_TABLE_PK VALID
五、语法总结:      
  1. ALTER TABLE  ENABLE ROW MOVEMENT   -->前提条件                                          
  2.                                                                                                    
  3. ALTER TABLE  SHRINK SPACE [ <NULL> | COMPACT | CASCADE ];                              
  4.                                                                                                    
  5. ALTER TABLE  SHRINK SPACE COMPCAT;  -->缩小表和索引,不移动高水位线,不释放空间         
  6.                                                                                                    
  7. ALTER TABLE  SHRINK SPACE;     -->收缩表,降低高水位线;                                 
  8.                                                                                                    
  9. ALTER TABLE  SHRINK SPACE CASCADE-->收缩表,降低高水位线,并且相关索引也要收缩一下    
  10.                                                                                                    
  11. ALTER TABLE  MODIFY LOB (lob_column) (SHRINK SPACE);  -->收缩LOB段                      
  12.                                                                                                    
  13. ALTER INDEX IDXNAME SHRINK SPACE;     -->索引段的收缩,同表段                                      
ALTER TABLE ENABLE ROW MOVEMENT -->前提条件 ALTER TABLE SHRINK SPACE [ | COMPACT | CASCADE ]; ALTER TABLE SHRINK SPACE COMPCAT; -->缩小表和索引,不移动高水位线,不释放空间 ALTER TABLE SHRINK SPACE; -->收缩表,降低高水位线; ALTER TABLE SHRINK SPACE CASCADE; -->收缩表,降低高水位线,并且相关索引也要收缩一下 ALTER TABLE MODIFY LOB (lob_column) (SHRINK SPACE); -->收缩LOB段 ALTER INDEX IDXNAME SHRINK SPACE; -->索引段的收缩,同表段

六、批量收缩脚本
1. 普通表(根据相应需求修改下面的语句生产相应脚本)   
  1. select'alter table '||table_name||' enable row movement;'                          
  2. ||chr(10)||'alter table '||table_name||' shrink space;'||chr(10)from user_tables;  
  3.                                                                                    
  4. select'alter index '||index_name||' shrink space;'||chr(10)from user_indexes;      
select'alter table '||table_name||' enable row movement;' ||chr(10)||'alter table '||table_name||' shrink space;'||chr(10)from user_tables; select'alter index '||index_name||' shrink space;'||chr(10)from user_indexes;
2. 分区表的处理

    分区表进行shrink space时发生ORA-10631错误.shrink space有一些限制.

    在表上建有函数索引(包括全文索引)会失败。   
  1. --根据相应需求修改下面的语句生产相应脚本                                                                   
  2. select 'alter table '||owner||'.'||table_name||' enable row movement;'                                    
  3. ||chr(10)||'alter table '||owner||'.'||table_name||' shrink space;'||chr(10) from dba_tables              
  4. where owner=upper('&input_owner');                                                                        
  5.                                                                                                           
  6. select 'alter index '||owner||'.'||index_name||' shrink space;'                                           
  7. ||chr(10) from dba_indexes where uniqueness='NONUNIQUE' ;                                                 
  8.                                                                                                           
  9. select 'alter table '||owner||'.'||segment_name||' modify partition '||partition_name||' shrink space;'   
  10. ||chr(10) from dba_segments where segment_type='TABLE PARTITION';                                         
--根据相应需求修改下面的语句生产相应脚本 select 'alter table '||owner||'.'||table_name||' enable row movement;' ||chr(10)||'alter table '||owner||'.'||table_name||' shrink space;'||chr(10) from dba_tables where wner=upper('&input_owner'); select 'alter index '||owner||'.'||index_name||' shrink space;' ||chr(10) from dba_indexes where uniqueness='NONUNIQUE' ; select 'alter table '||owner||'.'||segment_name||' modify partition '||partition_name||' shrink space;' ||chr(10) from dba_segments where segment_type='TABLE PARTITION';
 3. 附show_space脚本(来自Tom大师)   
  1. CREATE OR REPLACE PROCEDURE show_space                                                          
  2. (                                                                                               
  3.     p_segname IN VARCHAR2,                                                                      
  4.     p_owner IN VARCHAR2 DEFAULT USER,                                                           
  5.     p_type IN VARCHAR2 DEFAULT 'TABLE',                                                         
  6.     p_partition IN VARCHAR2 DEFAULT NULL                                                        
  7. )                                                                                               
  8. -- this procedure uses authid current user so it can query DBA_*                                 
  9.     -- views using privileges from a ROLE, and so it can be installed                            
  10.     -- once per database, instead of once per user who wanted to use it                          
  11. AUTHID CURRENT_USER AS                                                                          
  12.     l_free_blks NUMBER;                                                                         
  13.     l_total_blocks NUMBER;                                                                      
  14.     l_total_bytes NUMBER;                                                                       
  15.     l_unused_blocks NUMBER;                                                                     
  16.     l_unused_bytes NUMBER;                                                                      
  17.     l_LastUsedExtFileId NUMBER;                                                                 
  18.     l_LastUsedExtBlockId NUMBER;                                                                
  19.     l_LAST_USED_BLOCK NUMBER;                                                                   
  20.     l_segment_space_mgmt VARCHAR2(255);                                                         
  21.     l_unformatted_blocks NUMBER;                                                                
  22.     l_unformatted_bytes NUMBER;                                                                 
  23.     l_fs1_blocks NUMBER;                                                                        
  24.     l_fs1_bytes NUMBER;                                                                         
  25.     l_fs2_blocks NUMBER;                                                                        
  26.     l_fs2_bytes NUMBER;                                                                         
  27.     l_fs3_blocks NUMBER;                                                                        
  28.     l_fs3_bytes NUMBER;                                                                         
  29.     l_fs4_blocks NUMBER;                                                                        
  30.     l_fs4_bytes NUMBER;                                                                         
  31.     l_full_blocks NUMBER;                                                                       
  32.     l_full_bytes NUMBER;                                                                        
  33.     -- inline procedure to print out numbers nicely formatted                                    
  34.     -- with a simple label                                                                       
  35.     PROCEDURE p                                                                                 
  36.     (                                                                                           
  37.         p_label IN VARCHAR2,                                                                    
  38.         p_num IN NUMBER                                                                         
  39.     ) IS                                                                                        
  40.     BEGIN                                                                                       
  41.         dbms_output.put_line(rpad(p_label, 40, '.') ||                                          
  42.                              to_char(p_num, '999,999,999,999'));                                
  43.     END;                                                                                        
  44. BEGIN                                                                                           
  45.     -- this query is executed dynamically in order to allow this procedure                       
  46.     -- to be created by a user who has access to DBA_SEGMENTS/TABLESPACES                        
  47.     -- via a role as is customary.                                                               
  48.     -- NOTE: at runtime, the invoker MUST have access to these two                               
  49.     -- views!                                                                                    
  50.     -- this query determines if the object is an ASSM object or not                              
  51.     BEGIN                                                                                       
  52.         EXECUTE IMMEDIATE 'select ts.segment_space_management                                   
  53. from dba_segments seg, dba_tablespaces ts                                                       
  54. where seg.segment_name = :p_segname                                                             
  55. and (:p_partition is null or                                                                    
  56. seg.partition_name = :p_partition)                                                              
  57. and seg.owner = :p_owner                                                                        
  58. and seg.tablespace_name = ts.tablespace_name'                                                   
  59.             INTO l_segment_space_mgmt                                                           
  60.             USING p_segname, p_partition, p_partition, p_owner;                                 
  61.     EXCEPTION                                                                                   
  62.         WHEN too_many_rows THEN                                                                 
  63.             dbms_output.put_line('This must be a partitioned table, use p_partition => ');      
  64.             RETURN;                                                                             
  65.     END;                                                                                        
  66.     -- if the object is in an ASSM tablespace, we must use this API                              
  67.     -- call to get space information, otherwise we use the FREE_BLOCKS                           
  68.     -- API for the user-managed segments                                                         
  69.     IF l_segment_space_mgmt = 'AUTO' THEN                                                       
  70.         dbms_space.space_usage(p_owner,                                                         
  71.                                p_segname,                                                       
  72.                                p_type,                                                          
  73.                                l_unformatted_blocks,                                            
  74.                                l_unformatted_bytes,                                             
  75.                                l_fs1_blocks,                                                    
  76.                                l_fs1_bytes,                                                     
  77.                                l_fs2_blocks,                                                    
  78.                                l_fs2_bytes,                                                     
  79.                                l_fs3_blocks,                                                    
  80.                                l_fs3_bytes,                                                     
  81.                                l_fs4_blocks,                                                    
  82.                                l_fs4_bytes,                                                     
  83.                                l_full_blocks,                                                   
  84.                                l_full_bytes,                                                    
  85.                                p_partition);                                                    
  86.         p('Unformatted Blocks ', l_unformatted_blocks);                                         
  87.         p('FS1 Blocks (0-25) ', l_fs1_blocks);                                                  
  88.         p('FS2 Blocks (25-50) ', l_fs2_blocks);                                                 
  89.         p('FS3 Blocks (50-75) ', l_fs3_blocks);                                                 
  90.         p('FS4 Blocks (75-100)', l_fs4_blocks);                                                 
  91.         p('Full Blocks ', l_full_blocks);                                                       
  92.     ELSE                                                                                        
  93.         dbms_space.free_blocks(segment_owner => p_owner,                                        
  94.                                segment_name => p_segname,                                       
  95.                                segment_type => p_type,                                          
  96.                                freelist_group_id => 0,                                          
  97.                                free_blks => l_free_blks);                                       
  98.         p('Free Blocks', l_free_blks);                                                          
  99.     END IF;                                                                                     
  100.     -- and then the unused space API call to get the rest of the                                 
  101.     -- information                                                                               
  102.     dbms_space.unused_space(segment_owner => p_owner,                                           
  103.                             segment_name => p_segname,                                          
  104.                             segment_type => p_type,                                             
  105.                             partition_name => p_partition,                                      
  106.                             total_blocks => l_total_blocks,                                     
  107.                             total_bytes => l_total_bytes,                                       
  108.                             unused_blocks => l_unused_blocks,                                   
  109.                             unused_bytes => l_unused_bytes,                                     
  110.                             LAST_USED_EXTENT_FILE_ID => l_LastUsedExtFileId,                    
  111.                             LAST_USED_EXTENT_BLOCK_ID => l_LastUsedExtBlockId,                  
  112.                             LAST_USED_BLOCK => l_LAST_USED_BLOCK);                              
  113.     p('Total Blocks', l_total_blocks);                                                          
  114.     p('Total Bytes', l_total_bytes);                                                            
  115.     p('Total MBytes', trunc(l_total_bytes / 1024 / 1024));                                      
  116.     p('Unused Blocks', l_unused_blocks);                                                        
  117.     p('Unused Bytes', l_unused_bytes);                                                          
  118.     p('Last Used Ext FileId', l_LastUsedExtFileId);                                             
  119.     p('Last Used Ext BlockId', l_LastUsedExtBlockId);                                           
  120.     p('Last Used Block', l_LAST_USED_BLOCK);                                                    
  121. END;  

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/22578826/viewspace-703509/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2010-05-18

  • 博文量
    192
  • 访问量
    455695