HWM&&碎片整理之move操作
来源:互联网 发布:python软件哪里下载 编辑:程序博客网 时间:2024/06/06 02:25
针对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的空闲空间以供使
前面已经讨论过在线重定义,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的空闲空间以供使
0 0
- HWM&&碎片整理之move操作
- 表级碎片整理shrink/move
- 知识碎片之整理
- MOVE降低高水位 HWM
- mysql优化之碎片整理
- 降低HWM方法实验move shrink
- Oracle --- HWM 表碎片起因及解决办法
- dom操作之文档碎片
- 表碎片整理时shrink和move如何选择(转)——写的很好
- AD 实战之七:脱机碎片整理
- MongoDB碎片整理之compact详解
- MongoDB碎片整理之repairDatabase详解
- 碎片整理
- 碎片整理
- Oracle之move操作导致索引失效
- move 操作
- HWM
- linux内核网络代码学习之碎片整理篇
- servlet/filter/listener/interceptor区别与联系
- 4Fang打印控件直接指定打印机打印网页
- UISearchBar 如何隐藏按钮
- Winform TextBox中只能输入数字的几种常用方法(C#)
- Writing and Compiling A Simple Program For OpenWrt
- HWM&&碎片整理之move操作
- Android自动化测试之MonkeyRunner录制和回放脚本(四)
- 牛顿迭代法解非线性方程组(MATLAB版)
- ANDROID中的(udev)VOLD分析
- tiny210(s5pv210)移植u-boot(基于 2014.4 版本)——命令补全和历史命令
- 代理技术
- 后台执行命令
- 一张图理解OpenStack Neutron中的调用
- Linux rpm 命令参数使用详解