当前位置: 首页 > BASIC > 正文

shrink space与move操作中关于锁的获取与段的分配

这次测试主要来源与一个朋友遇到的问题,resize数据文件报错,对表shrink space后还是报错,其实是错误的理解了shrink space的操作,从下面的测试就知道答案了。

数据库的版本

SQL> select * from v$version;

BANNER

——————————————————————————–

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 – 64bit Production

PL/SQL Release 11.2.0.4.0 – Production

CORE 11.2.0.4.0 Production

TNS for Linux: Version 11.2.0.4.0 – Production

NLSRTL Version 11.2.0.4.0 – Production

1,shink space与move关于段的分配

先来看看目前表的段与区的分配情况

SQL> @extent.sql

Enter value for owner: scott

Enter value for segment_name: htz1

BLOCK

OWNER:SEGMENT_NAME EXTENT_ID BEGIN_END BLOCKS BYTES(KB)

———————————- ————————- ——— ———

SCOTT.HTZ1 0 128~135 8 64

1 136~143 8 64

2 144~151 8 64

3 152~159 8 64

4 160~167 8 64

5 168~175 8 64

6 176~183 8 64

7 184~191 8 64

8 192~199 8 64

9 200~207 8 64

10 208~215 8 64

11 216~223 8 64

12 224~231 8 64

13 232~239 8 64

14 240~247 8 64

15 248~255 8 64

16 256~383 128 1024

17 384~511 128 1024

18 512~639 128 1024

19 640~767 128 1024

20 768~895 128 1024

21 896~1023 128 1024

22 1024~1151 128 1024

23 1152~1279 128 1024

24 1280~1407 128 1024

************************ ——— ———

Total: 1280 10240

SQL> @segment.sql

Enter value for owner: scott

Enter value for segment_name: htz1

Enter value for tablespace_name:

HEADER

OWNER:SEGMENT_NAME FILE_BLOCK SIZE(M) BLOCKS EXTENTS

—————————— ——————– ———- ———- ——-

SCOTT.HTZ1 5.130 10 1280 25

****************************** ———- ———-

Total: 10 1280

1.1 shrink space表

SQL> oradebug setmypid

Statement processed.

SQL> oradebug event 10046 trace name context forever,level 12;

Statement processed.

SQL> alter table scott.htz1 shrink space;

Table altered.

SQL> oradebug tracefile_name;

/oracle/app/oracle/diag/rdbms/orcl1124/orcl1124/trace/orcl1124_ora_13776.trc

SQL> @extent.sql

Enter value for owner: scott

Enter value for segment_name: htz1

FILE BLOCK

OWNER:SEGMENT_NAME ID FNO EXTENT_ID BEGIN_END BLOCKS BYTES(KB)

———————————– —– ———- ————————- ——— ———

SCOTT.HTZ1 5 5 0 128~135 8 64

5 5 1 136~143 8 64

5 5 2 144~151 8 64

5 5 3 152~159 8 64

5 5 4 160~167 8 64

5 5 5 168~175 8 64

5 5 6 176~183 8 64

5 5 7 184~191 8 64

5 5 8 192~199 8 64

5 5 9 200~207 8 64

5 5 10 208~215 8 64

5 5 11 216~223 8 64

5 5 12 224~231 8 64

5 5 13 232~239 8 64

5 5 14 240~247 8 64

5 5 15 248~255 8 64

5 5 16 256~383 128 1024

5 5 17 384~511 128 1024

5 5 18 512~639 128 1024

5 5 19 640~767 128 1024

5 5 20 768~895 128 1024

5 5 21 896~1023 128 1024

5 5 22 1024~1151 128 1024

5 5 23 1152~1279 128 1024

5 5 24 1280~1391 112 896

****************************** ——— ———

Total: 1264 10112

SQL> @segment.sql

Enter value for owner: scott

Enter value for segment_name: htz1

Enter value for tablespace_name:

HEADER

OWNER:SEGMENT_NAME TABLESPACE_NAME FILE_BLOCK SIZE(M) BLOCKS EXTENTS

——————– ——————– ——————– ———- ———- ——-

SCOTT.HTZ1 HTZ 5.130 10 1264 25

******************** ———- ———-

Total: 10 1264

这里可以看到段没有发生变化,段头还是130这个块,区里面的信息基本没有变化,但是到最后一个区的块的个数是112个块,不是原来的128个块了,也并不是文档中所讲的需要整个EXTENT中的所有块都为 空块才会释放EXTENT,从这次实验看到是有一个剩余的空块就会释放一个。

1.2 MOVE表

SQL> alter table scott.htz1 move;

Table altered.

SQL> @extent.sql

Enter value for owner: scott

Enter value for segment_name: htz1

Enter value for tablespace_name:

FILE BLOCK

OWNER:SEGMENT_NAME ID FNO EXTENT_ID BEGIN_END BLOCKS BYTES(KB)

—————————- —– ———- ————————- ——— ———

SCOTT.HTZ1 5 5 0 1392~1399 8 64

5 5 1 1400~1407 8 64

5 5 2 3968~3975 8 64

5 5 3 3976~3983 8 64

5 5 4 3984~3991 8 64

5 5 5 3992~3999 8 64

5 5 6 4000~4007 8 64

5 5 7 4008~4015 8 64

5 5 8 4016~4023 8 64

5 5 9 4024~4031 8 64

5 5 10 4032~4039 8 64

5 5 11 4040~4047 8 64

5 5 12 4048~4055 8 64

5 5 13 4056~4063 8 64

5 5 14 4064~4071 8 64

5 5 15 4072~4079 8 64

5 5 16 4096~4223 128 1024

5 5 17 4224~4351 128 1024

5 5 18 4352~4479 128 1024

5 5 19 4480~4607 128 1024

5 5 20 4608~4735 128 1024

5 5 21 4736~4863 128 1024

5 5 22 4864~4991 128 1024

5 5 23 4992~5119 128 1024

5 5 24 5120~5247 128 1024

*********************** ——— ———

Total: 1280 10240

SQL> @segment.sql

Enter value for owner: scott

Enter value for segment_name: htz1

Enter value for tablespace_name:

HEADER

OWNER:SEGMENT_NAME FILE_BLOCK SIZE(M) BLOCKS EXTENTS

—————————- ——————– ———- ———- ——-

SCOTT.HTZ1 5.1394 10 1280 25

**************************** ———- ———-

Total: 10 1280

这里可以看到段头的块发生了变化。区的信息也发生了变化,因为MOVE操作,是新建一个临时段。

通过上面的操作,就很明显我朋友的原因了

2,shrink space与move操作关于锁的情况

2.1 move获取锁的情况

SQL> oradebug setmypid

Statement processed.

SQL> oradebug event 10704 trace name context forever,level 12 ;

Statement processed.

SQL> oradebug tracefile_name;

/oracle/app/oracle/diag/rdbms/orcl1124/orcl1124/trace/orcl1124_ora_14130.trc

SQL> alter table scott.htz1 move;

Table altered.

SQL> oradebug event 10704 trace name context off;

Statement processed.

SQL>

SQL> !cp /oracle/app/oracle/diag/rdbms/orcl1124/orcl1124/trace/orcl1124_ora_14130.trc /soft/tmp

SQL>

SQL> @object.sql

+————————————————————————+

| display one object type,owner,time,status |

+————————————————————————+

Enter Search Object Name (i.e. DEPT) : htz1

Object

Type OBJECT_ID DATA_OBJECT_ID

————— ———- ————–

TABLE 87442 87450

SQL> @1oto16.sql

SP2-0310: unable to open file “1oto16.sql”

SQL> @10to16.sql

Enter value for number10: 87442

TO_CHAR(‘87442’,’XXXXXXXXXXXXXXXXXXXXXXXXXXXX

———————————————

15592

15592

在开始的时候获取TM为6的锁,在结束的时候释放了

85 ksqgtl *** TM-00015592-00000000 mode=6 flags=0x401 timeout=0 ***

3114 ksqrcl: TM,15592,0

E:\install\tmp>cat orcl1124_ora_14130.trc|grep -E “^ksqgtl|^ksqrcl”|grep -v “RETURNS”|grep -v returns|grep -E “TX|TM”

ksqgtl *** TM-00015592-00000000 mode=6 flags=0x401 timeout=0 ***

这行就是对表加上6级别的锁

ksqgtl *** TM-00000012-00000000 mode=3 flags=0x401 timeout=21474836 ***

ksqgtl *** TX-00060018-000004a3 mode=6 flags=0x401 timeout=0 ***

ksqrcl: TX,60018,4a3

ksqrcl: TM,12,0

ksqgtl *** TX-00080006-00000400 mode=6 flags=0x401 timeout=0 ***

ksqgtl *** TX-00080014-00000400 mode=6 flags=0x401 timeout=0 ***

ksqgtl *** TM-0000000e-00000000 mode=3 flags=0x401 timeout=21474836 ***

ksqgtl *** TX-0008001e-000003ff mode=6 flags=0x401 timeout=0 ***

ksqrcl: TX,8001e,3ff

ksqgtl *** TX-00080003-00000400 mode=6 flags=0x401 timeout=0 ***

ksqrcl: TX,80003,400

ksqrcl: TX,80014,400

ksqrcl: TM,e,0

ksqgtl *** TX-00080010-00000400 mode=6 flags=0x401 timeout=0 ***

ksqrcl: TX,80010,400

ksqgtl *** TX-0008001b-00000400 mode=6 flags=0x401 timeout=0 ***

ksqgtl *** TM-0000000e-00000000 mode=3 flags=0x401 timeout=21474836 ***

ksqrcl: TX,8001b,400

ksqrcl: TM,e,0

ksqgtl *** TX-00080012-000003ff mode=6 flags=0x401 timeout=0 ***

这里有很多TX的锁,不知道具体是做什么的

ksqrcl: TX,80012,3ff

ksqgtl *** TX-0008001d-00000400 mode=6 flags=0x401 timeout=0 ***

ksqrcl: TX,8001d,400

ksqgtl *** TX-00080001-00000400 mode=6 flags=0x401 timeout=0 ***

ksqrcl: TX,80001,400

ksqgtl *** TX-00080011-00000400 mode=6 flags=0x401 timeout=0 ***

ksqrcl: TX,80011,400

ksqgtl *** TX-0008000c-00000400 mode=6 flags=0x401 timeout=0 ***

ksqrcl: TX,8000c,400

ksqgtl *** TX-0008001c-00000400 mode=6 flags=0x401 timeout=0 ***

ksqrcl: TX,8001c,400

ksqgtl *** TX-0008001a-00000400 mode=6 flags=0x401 timeout=0 ***

ksqrcl: TX,8001a,400

ksqgtl *** TX-00080013-00000400 mode=6 flags=0x401 timeout=0 ***

ksqrcl: TX,80013,400

ksqgtl *** TX-00080021-00000400 mode=6 flags=0x401 timeout=0 ***

ksqrcl: TX,80021,400

ksqgtl *** TX-00080002-00000400 mode=6 flags=0x401 timeout=0 ***

ksqrcl: TX,80002,400

ksqgtl *** TX-00080016-00000400 mode=6 flags=0x401 timeout=0 ***

ksqrcl: TX,80016,400

ksqgtl *** TX-0008000a-000003ff mode=6 flags=0x401 timeout=0 ***

ksqrcl: TX,8000a,3ff

ksqgtl *** TX-00080008-00000401 mode=6 flags=0x401 timeout=0 ***

ksqrcl: TX,80008,401

ksqgtl *** TX-00080000-00000401 mode=6 flags=0x401 timeout=0 ***

ksqrcl: TX,80000,401

ksqgtl *** TX-00080007-00000401 mode=6 flags=0x401 timeout=0 ***

ksqrcl: TX,80007,401

ksqgtl *** TX-00080018-000003ff mode=6 flags=0x401 timeout=0 ***

ksqrcl: TX,80018,3ff

ksqgtl *** TX-00080009-00000401 mode=6 flags=0x401 timeout=0 ***

ksqrcl: TX,80009,401

ksqgtl *** TX-00080015-00000400 mode=6 flags=0x401 timeout=0 ***

ksqrcl: TX,80015,400

ksqgtl *** TX-00080020-00000400 mode=6 flags=0x401 timeout=0 ***

ksqrcl: TX,80020,400

ksqgtl *** TX-00080019-00000400 mode=6 flags=0x401 timeout=0 ***

ksqrcl: TX,80019,400

ksqgtl *** TX-0008000b-00000401 mode=6 flags=0x401 timeout=0 ***

ksqrcl: TX,8000b,401

ksqgtl *** TX-0008001f-00000400 mode=6 flags=0x401 timeout=0 ***

ksqrcl: TX,8001f,400

ksqgtl *** TX-00080005-00000401 mode=6 flags=0x401 timeout=0 ***

ksqrcl: TX,80005,401

ksqgtl *** TX-0008000f-00000401 mode=6 flags=0x401 timeout=0 ***

ksqrcl: TX,8000f,401

ksqgtl *** TM-000001c9-00000000 mode=3 flags=0x401 timeout=21474836 ***

ksqgtl *** TM-0000028a-00000000 mode=3 flags=0x401 timeout=21474836 ***

ksqgtl *** TM-00000047-00000000 mode=3 flags=0x401 timeout=21474836 ***

ksqgtl *** TM-000001c5-00000000 mode=3 flags=0x401 timeout=21474836 ***

ksqgtl *** TM-00000004-00000000 mode=3 flags=0x401 timeout=21474836 ***

ksqgtl *** TM-0000000e-00000000 mode=3 flags=0x401 timeout=21474836 ***

ksqgtl *** TM-00000012-00000000 mode=3 flags=0x401 timeout=21474836 ***

ksqrcl: TX,80006,400

ksqrcl: TM,12,0

ksqrcl: TM,e,0

ksqrcl: TM,4,0

ksqrcl: TM,1c5,0

ksqrcl: TM,47,0

ksqrcl: TM,28a,0

ksqrcl: TM,1c9,0

ksqrcl: TM,15592,0这里才释放锁

ksqgtl *** TM-0000000e-00000000 mode=3 flags=0x401 timeout=21474836 ***

ksqgtl *** TX-000a001a-00000317 mode=6 flags=0x401 timeout=0 ***

ksqrcl: TX,a001a,317

ksqrcl: TM,e,0

ksqgtl *** TX-0007000f-00000319 mode=6 flags=0x401 timeout=0 ***

ksqgtl *** TM-0000000e-00000000 mode=3 flags=0x401 timeout=21474836 ***

ksqrcl: TX,7000f,319

ksqrcl: TM,e,0

在整个过程我们可以看到在表上TM上一直有6的锁。所以MOVE会影响业务

2.2 shrink space获取锁的情况

SQL> delete scott.htz1 where mod(object_id,4)=0;

21566 rows deleted.

SQL> commit;

Commit complete.

SQL> oradebug setmypid

Statement processed.

SQL> oradebug event 10704 trace name context forever,level 12 ;

Statement processed.

SQL> oradebug tracefile_name;

/oracle/app/oracle/diag/rdbms/orcl1124/orcl1124/trace/orcl1124_ora_14311.trc

这里我们只使用了space compact,只迁移数据,不做HWM的操作

SQL> alter table scott.htz1 shrink space compact;

Table altered.

[oracle@orcl9i sql]$ cat /oracle/app/oracle/diag/rdbms/orcl1124/orcl1124/trace/orcl1124_ora_14311.trc|grep -E “^ksqgtl|^ksqrcl”|grep -v “RETURNS”|grep -v returns|grep -E “TX|TM”

ksqgtl *** TX-00060014-000004a4 mode=6 flags=0x401 timeout=0 ***

ksqrcl: TX,60014,4a4

ksqgtl *** TM-00015592-00000000 mode=3 flags=0x401 timeout=0 ***

获取3级别的锁

ksqgtl *** TX-00080019-00000401 mode=6 flags=0x401 timeout=0 ***

ksqgtl *** TX-0008000b-00000402 mode=6 flags=0x401 timeout=0 ***

ksqrcl: TX,8000b,402

ksqgtl *** TX-0008001f-00000401 mode=6 flags=0x401 timeout=0 ***

ksqrcl: TX,8001f,401

ksqgtl *** TX-00080005-00000402 mode=6 flags=0x401 timeout=0 ***

ksqrcl: TX,80005,402

ksqgtl *** TX-0008000f-00000402 mode=6 flags=0x401 timeout=0 ***

ksqrcl: TX,8000f,402

ksqgtl *** TX-00080006-00000401 mode=6 flags=0x401 timeout=0 ***

ksqrcl: TX,80006,401

…………………………………………..

ksqgtl *** TX-00080014-0000040b mode=6 flags=0x401 timeout=0 ***

ksqrcl: TX,80014,40b

ksqgtl *** TX-00080010-0000040b mode=6 flags=0x401 timeout=0 ***

ksqrcl: TX,80010,40b

ksqrcl: TX,80019,401

ksqrcl: TM,15592,0

最后才释放,所以在整个过程中,我们看到在表上面有3级别的锁,行级排他级,允许其它的DML语句表,不能操作TX获取到的行。

[oracle@orcl9i sql]$ cat /oracle/app/oracle/diag/rdbms/orcl1124/orcl1124/trace/orcl1124_ora_14311.trc|grep “ksqgtl”|grep “TX”|wc -l

316

这里看到了有316个TX锁,不知道这个TX锁跟块的个数或者是其它的有什么关系

下面我们再来看看shrink space整个过程对锁获取的信息

[oracle@orcl9i sql]$ cat /oracle/app/oracle/diag/rdbms/orcl1124/orcl1124/trace/orcl1124_ora_14324.trc|grep -E “^ksqgtl|^ksqrcl”|grep -v “RETURNS”|grep -v returns|grep -E “TX|TM”

ksqgtl *** TX-0007000f-0000031a mode=6 flags=0x401 timeout=0 ***

ksqrcl: TX,7000f,31a

ksqgtl *** TM-00015592-00000000 mode=3 flags=0x401 timeout=0 ***

ksqgtl *** TX-0005000c-000003e9 mode=6 flags=0x401 timeout=0 ***

ksqgtl *** TX-00050012-000003e9 mode=6 flags=0x401 timeout=0 ***

ksqrcl: TX,50012,3e9

ksqgtl *** TX-00050002-000003e9 mode=6 flags=0x401 timeout=0 ***

ksqrcl: TX,50002,3e9

ksqrcl: TX,5000c,3e9

ksqrcl: TM,15592,0

ksqgtl *** TM-00015592-00000000 mode=6 flags=0x401 timeout=21474836 ***

ksqgtl *** TX-00010014-00000316 mode=6 flags=0x401 timeout=0 ***

ksqgtl *** TM-00000047-00000000 mode=3 flags=0x401 timeout=21474836 ***

ksqgtl *** TM-000001c5-00000000 mode=3 flags=0x401 timeout=21474836 ***

ksqgtl *** TM-00000004-00000000 mode=3 flags=0x401 timeout=21474836 ***

ksqgtl *** TM-0000000e-00000000 mode=3 flags=0x401 timeout=21474836 ***

ksqrcl: TX,10014,316

ksqrcl: TM,e,0

ksqrcl: TM,4,0

ksqrcl: TM,1c5,0

ksqrcl: TM,47,0

ksqrcl: TM,15592,0

ksqgtl *** TM-00015592-00000000 mode=6 flags=0x401 timeout=21474836 ***

ksqgtl *** TX-0002000c-000003eb mode=6 flags=0x401 timeout=0 ***

ksqgtl *** TM-0000000e-00000000 mode=3 flags=0x401 timeout=21474836 ***

ksqrcl: TX,2000c,3eb

ksqrcl: TM,e,0

ksqgtl *** TX-0003000a-000003ec mode=6 flags=0x401 timeout=0 ***

ksqgtl *** TM-0000000e-00000000 mode=3 flags=0x401 timeout=21474836 ***

ksqrcl: TX,3000a,3ec

ksqrcl: TM,e,0

ksqgtl *** TX-0004000e-00000359 mode=6 flags=0x401 timeout=0 ***

ksqgtl *** TM-0000000e-00000000 mode=3 flags=0x401 timeout=21474836 ***

ksqrcl: TX,4000e,359

ksqrcl: TM,e,0

ksqrcl: TM,15592,0

所以在整个shrink space过程中,1,在迁移数据的时候,在表上获取3级别的锁。2,2次在表上面获取6级别的锁。

本文固定链接: http://www.htz.pw/2014/04/25/shrink-space%e4%b8%8emove%e6%93%8d%e4%bd%9c%e4%b8%ad%e5%85%b3%e4%ba%8e%e9%94%81%e7%9a%84%e8%8e%b7%e5%8f%96%e4%b8%8e%e6%ae%b5%e7%9a%84%e5%88%86%e9%85%8d.html | 认真就输

该日志由 huangtingzhong 于2014年04月25日发表在 BASIC 分类下, 你可以发表评论,并在保留原文地址及作者的情况下引用到你的网站或博客。
原创文章转载请注明: shrink space与move操作中关于锁的获取与段的分配 | 认真就输