Oracle 高水位查询和处理方法汇总

一 查询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

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]

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);

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

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

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.

###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存储过程
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 table t1;

ALTER TABLE TABLE_NAME MOVE;
1 会锁表
2 move是以block为单位重组数据，行的rowid都会跟着变化，索引会失效，需要重建索引。
3 需要准备两倍的空间。
3 MOVE之后,HWM降低了，空闲块也上去了。
但是分配的空间并没有改变，仍然是1280个BLOCKS。

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。

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