HWM&&碎片整理之move操作

针对ASSM segment管理方式的HWM的降法：impdp/expdp,exp/imp,ctas,insert into,move,shrink,在线重定义等方法；
前面已经讨论过在线重定义，impdp/expdp的用法，这里不作讨论，至于ctas，不作讨论；
前面，已经用tom大师的工具来做hwm的判定了；
这里主要说明move用法
move操作：
SQL> create table hwm as select * from dba_objects where 0=1;
Table created.

SQL> begin
  2  for i in 1..10 loop
  3  insert into hwm select * from dba_objects order by i;
  4  commit;
  5  end loop;
  6  end;
  7  /

PL/SQL procedure successfully completed.

SQL> EXEC SHOW_SPACE('HWM','T','AUTO','Y',NULL,'TRSEN');
Total Blocks............................11264======>hwm=11264
Total Bytes.............................92274688
Unused Blocks...........................0
Unused Bytes............................0
Last Used Ext FileId....................4
Last Used Ext BlockId...................64640
Last Used Block.........................1024
################################
The segment is analyzed
0% -- 25% free space_blocks.............0
0% -- 25% free space_bytes..............0
25% -- 50% free space_blocks............0
25% -- 50% free space_bytes.............0
50% -- 75% free space_blocks............0
50% -- 75% free space_bytes.............0
75% -- 100% free space_blocks...........187
75% -- 100% free space_bytes............1531904
full_blocks.............................10674
full_bytes.............................87441408
Unused Blocks...........................256
Unused Bytes............................2097152
Total Blocks............................10674
Total bytes.............................87441408

PL/SQL procedure successfully completed.

SQL> select count(1) from hwm;
  COUNT(1)
----------
    753000

SQL> delete hwm where rownum<=50000;
50000 rows deleted.

SQL> commit;
Commit complete.

SQL> select count(1) from hwm;
  COUNT(1)
----------
    703000
    

SQL> EXEC SHOW_SPACE('HWM','T','AUTO','Y',NULL,'TRSEN');
Total Blocks............................11264==>HWM没有变化
Total Bytes.............................92274688
Unused Blocks...........................0
Unused Bytes............................0
Last Used Ext FileId....................4
Last Used Ext BlockId...................64640
Last Used Block.........................1024
################################
The segment is analyzed
0% -- 25% free space_blocks.............0
0% -- 25% free space_bytes..............0
25% -- 50% free space_blocks............0
25% -- 50% free space_bytes.............0
50% -- 75% free space_blocks............0
50% -- 75% free space_bytes.............0
75% -- 100% free space_blocks...........894
75% -- 100% free space_bytes............7323648
full_blocks.............................9967
full_bytes.............................81649664
Unused Blocks...........................256
Unused Bytes............................2097152
Total Blocks............................9967
Total bytes.............................81649664

PL/SQL procedure successfully completed.


==>move HWM表，后再看HWM
alter table HWM move;

SQL> EXEC SHOW_SPACE('HWM','T','AUTO','Y',NULL,'TRSEN');
Total Blocks............................10240===>HWM=10240-90+1=10150,高水位降了
Total Bytes.............................83886080
Unused Blocks...........................90
Unused Bytes............................737280
Last Used Ext FileId....................4
Last Used Ext BlockId...................74752
Last Used Block.........................934
################################
The segment is analyzed
0% -- 25% free space_blocks.............0
0% -- 25% free space_bytes..............0
25% -- 50% free space_blocks............0
25% -- 50% free space_bytes.............0
50% -- 75% free space_blocks............0
50% -- 75% free space_bytes.............0
75% -- 100% free space_blocks...........0
75% -- 100% free space_bytes............0
full_blocks.............................10007
full_bytes.............................81977344
Unused Blocks...........................0
Unused Bytes............................0
Total Blocks............................10007
Total bytes.............................81977344

PL/SQL procedure successfully completed.

那么这里需要问问move到底做了什么动作呢？
我们通常使用这个命令，将一个table segment从一个tablespace移动到另一个tablespace。
Move实际上是在block之间物理的copy数据，那么，我们可以通过这种方式来降低table的HWM。
table里rowid构造，是16进制编码格式
例如：
AAAS6P AAE AAAQFv AAW
分别
数据对象号
文件号
数据块号
行号
查看对象号：
SQL> col object_name for a15;
SQL> col owner for a25;
SQL> SELECT od.object_name, od.owner
  2    FROM dba_objects od
  3   WHERE od.DATA_OBJECT_ID =
  4         (SELECT dbms_rowid.rowid_object('AAAS6PAAEAAAQFvAAW') FROM dual) ;
OBJECT_NAME     OWNER
--------------- -------------------------
HWM             TRSEN

一般情况下，object_id=data_object_id，但是如果做过truncate,move等动作更改块的动作，data_object_id值发生了变化，所以匹配object_id
就定位不到对象，通过data_object_id来处理

查看文件号：
SQL> set linesize 110;
SQL> col file_name for a80;
SQL> col tablespace_name for a15;
SQL> col AUTOEXTENSIBLE for a10
SQL> SELECT ddf.file_name,ddf.tablespace_name,ddf.AUTOEXTENSIBLE
  2    FROM dba_data_files ddf
  3   WHERE ddf.FILE_ID =
  4         (SELECT dbms_rowid.rowid_relative_fno('AAAS6PAAEAAAQFvAAW') FROM dual);
FILE_NAME                                                                        TABLESPACE_NAME AUTOEXTENS
-------------------------------------------------------------------------------- --------------- ----------
/u01/app/oacle/oradata/orcl/users01.dbf                                          USERS           YES

块号及行号
dbms_rowid.rowid_block_number
dbms_rowid.rowid_row_number

我们现在来看看做了move动作后，数据存放的变化
SQL> create table hwm1 (id int,name char(2000));
Table created.

SQL> insert into hwm1 values(1,'a');
1 row created.

SQL> insert into hwm1 values(1,'aa');
1 row created.

SQL> insert into hwm1 values(3,'df');
1 row created.

SQL> insert into hwm1 values(6,'eeee');
1 row created.

SQL> commit;
Commit complete.

SQL> select rowid from hwm1;
ROWID
------------------
AAAS6RAAEAAAAINAAA
AAAS6RAAEAAAAINAAB
AAAS6RAAEAAAAINAAC
AAAS6RAAEAAAAIOAAA
4条数据分别存放在AAAAIN和AAAAIO两个块上

做move动作
alter table HWM1 move;

SQL> select rowid from hwm1;
ROWID
------------------
AAAS6TAAEAAAAILAAA
AAAS6TAAEAAAAILAAB
AAAS6TAAEAAAAILAAC
AAAS6TAAEAAAAIMAAA

4条数据分别存放在AAAAIL一个块上

move对索引的影响
SQL>  create index idx_hwm1 on hwm1 (id);
Index created

SQL> alter table my_objects move;
Table altered

SQL> select index_name,status from user_indexes where index_name='IDX_HWM1';
INDEX_NAME                     STATUS
------------------------------ --------
IDX_HWM1                   UNUSABLE

该table上的inedx的状态为UNUSABLE，这时，我们可以使用alter index IDX_HWM1 rebuild online的命令，对IDX_HWM1进行在线rebuild。


move时对table的锁定
当我们对表HWM进行move操作时，查询v$locked_objects视图可以发现,在表HWM上加了exclusive lock：
SQL>select OBJECT_ID, SESSION_ID,ORACLE_USERNAME,LOCKED_MODE from v$locked_objects;
 OBJECT_ID SESSION_ID ORACLE_USERNAME    LOCKED_MODE
---------- ---------- ------------------ -----------
     77455          11 DLINGER                      6
     
SQL> select object_id from user_objects where object_name = 'HWM';
 OBJECT_ID
----------
     77455

对table进行move操作时，我们只能对它进行select的操作；
反过来说，当我们的一个session对table进行DML操作且没有commit时，在另一个session中是不能对这个table进行move操作的，
否则oracle会返回这样的错误信息：ORA-00054: 资源正忙，要求指定 NOWAIT。

SQL> alter table hwm1 move;
alter table hwm1 move
            *
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired

使用alter table move来降低table的HWM时，有一点是需要注意的，这时，当前的tablespace中需要有1倍于table的空闲空间以供使