查詢高水位方法：

一 查詢dba_tables(user_tables)
二 dump SEGMENT HEADER block
三 Segment Advisor
四 show_space

高水位處理方法：

一 truncate
二 SHRINK SPACE CASCADE
三 MOVE
四 exp/imp或expdp/impdp
五 複製要保留的資料到臨時表T，DROP原表，然後RENAME臨時表T為原表。

建立測試資料

SQL> conn / as sysdba
Connected.
SQL> create tablespace cjctbs datafile '/u01/app/oracle/oradata/chendb/cjctbs01.dbf' size 10M autoextend on;
create user c##cjc identified by a default tablespace cjctbs;
grant connect,resource,dba to c##cjc;
conn c##cjc/a
create table t1 as select * from dba_objects;
insert into t1 select * from t1;
/
/
/
SQL> select count(*) from t1;
  COUNT(*)
----------
   881698
SQL> delete t1 where rownum<881688;
Commit complete

查詢高水位，方法一：查詢dba_tables(user_tables)

How to find Objects Fragmented below High water mark (Doc ID 337651.1)
Oracle Database - Enterprise Edition - Version 9.2.0.1 to 11.2.0.1 [Release 9.2 to 11.2]

1 收集表統計資訊

EXEC DBMS_STATS.GATHER_TABLE_STATS(ownname => 'C##CJC',tabname => 't1',estimate_percent=>100,method_opt=> 'FOR ALL INDEXED COLUMNS',CASCADE=> TRUE,no_invalidate=> FALSE,degree=>2);

2 查詢高水位

set line 300
col table_name for a20
SELECT table_name,
                  ROUND ( (blocks * 8), 2) "High_Water(K)",
                  ROUND ( (num_rows * avg_row_len / 1024), 2) "USED_Space(K)",
                  ROUND ( (blocks * 10 / 100) * 8, 2) "Reserve_Space(K)",
                  ROUND (
                        (  blocks * 8
                          - (num_rows * avg_row_len / 1024)
                          - blocks * 8 * 10 / 100),
                        2)
                        "RECOVERY_Space(K)"
        FROM user_tables
      WHERE table_name='T1'
ORDER BY 5 DESC;
TABLE_NAME         High_Water(K) USED_Space(K) Reserve_Space(K) RECOVERY_Space(K)
-------------------- ------------- ------------- ---------------- -----------------
T1                137896        1.33      13789.6      124105.07
SQL> select segment_name,bytes/1024 from user_segments where segment_name='T1';
SEGMENT_NA BYTES/1024
---------- ----------
T1           139264

或透過如下指令碼查詢：

[oracle@cjcos02 ~]$ cat high_water.sql
REM This is an example SQL*Plus Script to find tables fragmentated below high water mark
REM set heading off verify off echo off
set line 300
col table_name for a20
REM The below queries gives information about the size of the table with respect to the High water Mark
REM note that BLOCKS*8192 is BLOCKS times the block size: 8192. Substitue your DB blocksize.
PROMPT Please enter the schema name
SELECT TABLE_NAME,BLOCKS*8192/1024/1024 MB FROM DBA_TABLES WHERE owner='&OWNER';
REM The below queries gives the actual size in MB used by the table in terms of data .
REM You can use the difference of the two sql statements specified above to get the table which
REM has fragementation below high water mark prompt Enter name(s) of schema for which you want to find fragemented object.
PROMPT Please enter the schema name
SELECT TABLE_NAME , (BLOCKS *8192 / 1024/1024 ) - (NUM_ROWS*AVG_ROW_LEN/1024/1024)
"Data lower than HWM in MB" FROM DBA_TABLES WHERE UPPER(owner) =UPPER('&OWNER') order by 2 desc;
SQL> @high_water.sql
Please enter the schema name
Enter value for owner: c##cjc
old   1: SELECT TABLE_NAME,BLOCKS*8192/1024/1024 MB FROM DBA_TABLES WHERE owner='&OWNER'
new   1: SELECT TABLE_NAME,BLOCKS*8192/1024/1024 MB FROM DBA_TABLES WHERE owner='c##cjc'
no rows selected
Please enter the schema name
Enter value for owner: c##cjc
old   2: "Data lower than HWM in MB" FROM DBA_TABLES WHERE UPPER(owner) =UPPER('&OWNER') order by 2 desc
new   2: "Data lower than HWM in MB" FROM DBA_TABLES WHERE UPPER(owner) =UPPER('c##cjc') order by 2 desc
TABLE_NAME         Data lower than HWM in MB
-------------------- -------------------------
T1                    134.662766

查詢高水位，方法二：dump SEGMENT HEADER block

set linesize 200 pagesize 200
col owner for a10
col segment_name for a10
select owner,segment_name,header_file,header_block,SEGMENT_TYPE from dba_segments where segment_name='T1';
OWNER       SEGMENT_NA HEADER_FILE HEADER_BLOCK SEGMENT_TYPE
---------- ---------- ----------- ------------ ------------------
C##CJC       T1               17       130 TABLE

SQL> alter system dump datafile 17 block 130;
System altered.

SQL>select value from v$diag_info where name='Default Trace File';
VALUE
---------------------------------------------------------------------
/u01/app/oracle/diag/rdbms/chendb/chendb/trace/chendb_ora_26119.trc

[oracle@cjcos02 ~]$ vim /u01/app/oracle/diag/rdbms/chendb/chendb/trace/chendb_ora_26119.trc
Trace file /u01/app/oracle/diag/rdbms/chendb/chendb/trace/chendb_ora_26119.trc
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
Build label:    RDBMS_19.3.0.0.0DBRU_LINUX.X64_190417
ORACLE_HOME:    /u01/app/oracle/product/19.0.0/dbhome_1
System name:    Linux
Node name:      cjcos02
Release:        4.1.12-112.16.4.el7uek.x86_64
Version:        #2 SMP Mon Mar 12 23:57:12 PDT 2018
Machine:        x86_64
Instance name: chendb
Redo thread mounted by this instance: 1
Oracle process number: 58
Unix process pid: 26119, image: oracle@cjcos02 (TNS V1-V3)
......
Extent Control Header
  -----------------------------------------------------------------
  Extent Header:: spare1: 0      spare2: 0      #extents: 88     #blocks: 17408
                  last map  0x00000000  #maps: 0      offset: 2716
      Highwater::  0x04404480  ext#: 87     blk#: 1024   ext size: 1024
  #blocks in seg. hdr's freelists: 0
  #blocks below: 17237
  mapblk  0x00000000  offset: 87
                   Unlocked
  --------------------------------------------------------
  Low HighWater Mark :
      Highwater::  0x04404480  ext#: 87     blk#: 1024   ext size: 1024
  #blocks in seg. hdr's freelists: 0
  #blocks below: 17237
  mapblk  0x00000000  offset: 87
  Level 1 BMB for High HWM block: 0x04404083
  Level 1 BMB for Low HWM block: 0x04404083
  --------------------------------------------------------
  Segment Type: 1 nl2: 1      blksz: 8192   fbsz: 0
  L2 Array start offset:  0x00001434
  First Level 3 BMB:  0x00000000
  L2 Hint for inserts:  0x04400081
  Last Level 1 BMB:  0x04404083
  Last Level II BMB:  0x04400081
  Last Level III BMB:  0x00000000
     Map Header:: next  0x00000000  #extents: 88   obj#: 76295  flag: 0x10000000
  Inc # 1

高水位=17237*8192/1024=137896KB

查詢高水位，方法三：Segment Advisor

SQL>
declare
  my_task_id number;
  obj_id number;
  my_task_name varchar2(100);
  my_task_desc varchar2(500);
begin
  my_task_name :='advisor_test tab Advice';  
  my_task_desc :='Manual Segment Advisor Run';
-----step 1
  /* 建立一個段顧問任務 */
  dbms_advisor.create_task(
  advisor_name => 'Segment Advisor',  
  task_id => my_task_id,
  task_name => my_task_name,
  task_desc =>my_task_desc);
-----step 2
  /* 為這個任務分配一個物件 */
  dbms_advisor.create_object(
  task_name=>my_task_name,
  object_type=>'TABLE',       --指定物件級別，假設為表物件則為'TABLE'，假設為表空間級別則為'TABLESPACE'
  attr1=>'C##CJC',             ---假設在表物件級別執行，這個屬性為username，表空間級別這個屬性為表空間名字
  attr2 => 'T1',    ---假設在表物件級別執行，這個屬性為表名，表空間級別這個屬性為null
  attr3 => NULL,
  attr4=>null,
  attr5=>null,
  object_id=>obj_id);
-----step 3
/* 設定任務引數 */
  dbms_advisor.set_task_parameter(
  task_name => my_task_name,
/* 設定段顧問執行引數"ecommend_all"的值,為TRUE則為全部型別的物件的生成建議，為FALSE則僅生成與空間相關的建議 */
/* 還有一個滾問執行引數"time_limit",制定顧問執行的時間限制，預設值為無限制 */
  parameter=>'recommend_all',
  value=>'TRUE');
-----step 4
/* 執行這個任務 */
  dbms_advisor.execute_task(my_task_name);
end;
/
PL/SQL procedure successfully completed.

##3 刪除
###SQL> exec dbms_advisor.delete_task(task_name => 'advisor_test tab Advice');

查詢建議

select
  /* "|chr(13)||chr(10)"為windows平臺的換行符，假設是linux等其他平臺，請用"chr(10)"取代 */
'Task name     :'||f.task_name||chr(13)||chr(10)||  
'Segment name  :'||o.attr2    ||chr(13)||chr(10)||
'Sement type   :'||o.type     ||chr(13)||chr(10)||
'partition name:'||o.attr3    ||chr(13)||chr(10)||
'Message       :'||f.message  ||chr(13)||chr(10)||
'More info     :'||f.more_info TASK_ADVICE
from dba_advisor_findings f,dba_advisor_objects o
where o.task_id=f.task_id
  and o.object_id=f.object_id
  and f.task_name = 'advisor_test tab Advice'
order by f.task_name;

TASK_ADVICE
---------------------------------------------------------------------------------------------------
Task name     :advisor_test tab Advice
Segment name  :T1
Sement type   :TABLE
partition name:
Message       :Enable row movement of the table C##CJC.T1 and perform shrink, estimated savings is 142605304 bytes.
More info     :Allocated Space:142606336: Used Space:1032: Reclaimable Space :142605304:

查詢高水位，方法四：show_space

建立show_space儲存過程
create or replace procedure show_space
( p_segname in varchar2,
  p_owner   in varchar2 default user,
  p_type    in varchar2 default 'TABLE',
  p_partition in varchar2 default NULL )
-- this procedure uses authid current user so it can query DBA_*
-- views using privileges from a ROLE and so it can be installed
-- once per database, instead of once per user that wanted to use it
authid current_user
as
    l_free_blks                 number;
    l_total_blocks              number;
    l_total_bytes               number;
    l_unused_blocks             number;
    l_unused_bytes              number;
    l_LastUsedExtFileId         number;
    l_LastUsedExtBlockId        number;
    l_LAST_USED_BLOCK           number;
    l_segment_space_mgmt        varchar2(255);
    l_unformatted_blocks number;
    l_unformatted_bytes number;
    l_fs1_blocks number; l_fs1_bytes number;
    l_fs2_blocks number; l_fs2_bytes number;
    l_fs3_blocks number; l_fs3_bytes number;
    l_fs4_blocks number; l_fs4_bytes number;
    l_full_blocks number; l_full_bytes number;
        -- inline procedure to print out numbers nicely formatted
        -- with a simple label
    procedure p( p_label in varchar2, p_num in number )
    is
    begin
        dbms_output.put_line( rpad(p_label,40,'.') ||
                              to_char(p_num,'999,999,999,999') );
    end;
begin
   -- this query is executed dynamically in order to allow this procedure
   -- to be created by a user who has access to DBA_SEGMENTS/TABLESPACES
   -- via a role as is customary.
   -- NOTE: at runtime, the invoker MUST have access to these two
   -- views!
   -- this query determines if the object is a ASSM object or not
   begin
      execute immediate
          'select ts.segment_space_management
             from dba_segments seg, dba_tablespaces ts
            where seg.segment_name      = :p_segname
              and (:p_partition is null or
                  seg.partition_name = :p_partition)
              and seg.owner = :p_owner
              and seg.tablespace_name = ts.tablespace_name'
             into l_segment_space_mgmt
            using p_segname, p_partition, p_partition, p_owner;
   exception
       when too_many_rows then
          dbms_output.put_line
          ( 'This must be a partitioned table, use p_partition => ');
          return;
   end;
   -- if the object is in an ASSM tablespace, we must use this API
   -- call to get space information, else we use the FREE_BLOCKS
   -- API for the user managed segments
   if l_segment_space_mgmt = 'AUTO'
   then
     dbms_space.space_usage
     ( p_owner, p_segname, p_type, l_unformatted_blocks,
       l_unformatted_bytes, l_fs1_blocks, l_fs1_bytes,
       l_fs2_blocks, l_fs2_bytes, l_fs3_blocks, l_fs3_bytes,
       l_fs4_blocks, l_fs4_bytes, l_full_blocks, l_full_bytes, p_partition);
     p( 'Unformatted Blocks ', l_unformatted_blocks );
     p( 'FS1 Blocks (0-25)  ', l_fs1_blocks );
     p( 'FS2 Blocks (25-50) ', l_fs2_blocks );
     p( 'FS3 Blocks (50-75) ', l_fs3_blocks );
     p( 'FS4 Blocks (75-100)', l_fs4_blocks );
     p( 'Full Blocks        ', l_full_blocks );
  else
     dbms_space.free_blocks(
       segment_owner     => p_owner,
       segment_name      => p_segname,
       segment_type      => p_type,
       freelist_group_id => 0,
       free_blks         => l_free_blks);
     p( 'Free Blocks', l_free_blks );
  end if;
  -- and then the unused space API call to get the rest of the
  -- information
  dbms_space.unused_space
  ( segment_owner     => p_owner,
    segment_name      => p_segname,
    segment_type      => p_type,
    partition_name    => p_partition,
    total_blocks      => l_total_blocks,
    total_bytes       => l_total_bytes,
    unused_blocks     => l_unused_blocks,
    unused_bytes      => l_unused_bytes,
    LAST_USED_EXTENT_FILE_ID => l_LastUsedExtFileId,
    LAST_USED_EXTENT_BLOCK_ID => l_LastUsedExtBlockId,
    LAST_USED_BLOCK => l_LAST_USED_BLOCK );
    p( 'Total Blocks', l_total_blocks );
    p( 'Total Bytes', l_total_bytes );
    p( 'Total MBytes', trunc(l_total_bytes/1024/1024) );
    p( 'Unused Blocks', l_unused_blocks );
    p( 'Unused Bytes', l_unused_bytes );
    p( 'Last Used Ext FileId', l_LastUsedExtFileId );
    p( 'Last Used Ext BlockId', l_LastUsedExtBlockId );
    p( 'Last Used Block', l_LAST_USED_BLOCK );
end;
/
SQL> exec show_space('T1');
PL/SQL procedure successfully completed.

SQL>  set serveroutput on
SQL>  exec show_space('T1');
Unformatted Blocks .....................           0
FS1 Blocks (0-25)  .....................           0
FS2 Blocks (25-50) .....................           0
FS3 Blocks (50-75) .....................           0
FS4 Blocks (75-100).....................         209
Full Blocks       .....................      17,028
Total Blocks............................      17,408
Total Bytes.............................     142,606,336
Total MBytes............................         136
Unused Blocks...........................           0
Unused Bytes............................           0
Last Used Ext FileId....................          17
Last Used Ext BlockId...................      16,512
Last Used Block.........................       1,024
PL/SQL procedure successfully completed.

高水位處理方法：

一 truncate

如果表裡資料可以全部清空，可以透過truncate降低高水位
truncate table t1;

二 MOVE

ALTER TABLE TABLE_NAME MOVE;
1 會鎖表
2 move是以block為單位重組資料，行的rowid都會跟著變化，索引會失效，需要重建索引。
3 需要準備兩倍的空間。
3 MOVE之後,HWM降低了，空閒塊也上去了。
但是分配的空間並沒有改變，仍然是1280個BLOCKS。

三 SHRINK SPACE CASCADE

ALTER TABLE TABLE_NAME SHRINK SPACE;
在執行該指令之前必須開啟行移動。
1 壓縮segment,調整hwm,並馬上釋放空間
2 shrink是以行為單位重組資料，根據複雜演算法從邏輯+物理重組資料
3 shrink的演算法是從segment的底部開始，移動row到segment的頂部，移動的過程相當於delete/insert操作的組合，在這個過程中會產生大量的undo和redo資訊。
4 耗時可能非常長，通常慢於move。
5 對於空間的要求，shrink不需要額外的空間。
ALTER TABLE TABLE_NAME ENABLE ROW MOVEMENT;
alter table <table_name> shrink space [ <null> | compact | cascade ];
alter table shrink space compact cascade;
ALTER TABLE TABLE_NAME DISABLE ROW MOVEMENT;
cascade: 縮小表及其索引，並移動高水位線，釋放空間，這個引數是在shrink table的時候自動級聯索引，相當於rebulid index。
compact: 僅僅是縮小表和索引，並不移動高水位線，不釋放空間
加上compact選項僅重新整理segment 空間，並壓縮表的記錄在以後進行release空間。
但資料庫並不調整hwm及釋放空間.
為了釋放空間.你必須再發布alter table shrink space
--compact用於把一個長操作分割為兩個較短的操作
如果在業務繁忙時做壓縮，可以使用alter table shrink space compact來對錶格進行碎片整理，而不調整高水位線，之後再次呼叫alter table table_name shrink space來釋放空間。
也可以使用alter table table_name shrink space cascade來同時對索引都進行收縮，這等同於同時執行alter index idxname shrink space。

四 exp/imp或expdp/impdp

五複製要保留的資料到臨時表T，DROP原表，然後RENAME臨時表T為原表。

例如：
create table t2 as select * from t1;
drop table t1 purge;
rename t2 to t1;

###2021-03-01 22:10 chenjuchao###

Oracle 高水位查詢和處理方法彙總

相關文章