这次测试主要来源与一个朋友遇到的问题，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级别的锁。 |
shrink space与move操作中关于锁的获取与段的分配：等您坐沙发呢！