| 订阅 | 在线投稿
分享
 
 
当前位置: 王朝网络 >> oracle >> shen ru tan tao Oracle shu ju ku 10g de Shrink ji zhi
 

shen ru tan tao Oracle shu ju ku 10g de Shrink ji zhi

2008-07-17 07:49:07 编辑來源:互联网 评论
 
本文为【深入探讨Oracle数据库10g的Shrink机制】的拼音翻译版
  cong 10g kai shi ,oracle kai shi ti gong Shrink de ming ling , jia ru wo men de biao kong jian zhong zhi chi zi dong duan kong jian guan li (ASSM), jiu ke yi shi yong zhe ge te xing suo xiao duan , ji jiang di HWM。 zhe li xu yao qiang tiao yi dian ,10g de zhe ge xin te xing , jin dui ASSM biao kong jian you xiao , fou ze hui bao ORA-10635: Invalid segment or tablespace type。
  zai zhe li , wo men lai tao lun ru he dui yi ge ASSM de segment hui shou lang fei de kong jian 。
  tong yang , wo men yong xi tong shi tu all_objects lai zai tablespace ASSM shang chuang jian ce shi biao my_objects, zhe yi xiao jie de nei rong , shi yan huan jing wei oracle10.1.0.2:
  SQL> select * from v$version;
  BANNER
  ----------------------------------------------------------------
  Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Prod
  PL/SQL Release 10.1.0.2.0 - Production
  CORE 10.1.0.2.0 Production
  TNS for 32-bit Windows: Version 10.1.0.2.0 - Production
  NLSRTL Version 10.1.0.2.0 – Production
  SQL> select TABLESPACE_NAME,BLOCK_SIZE,EXTENT_MANAGEMENT,
  2 ALLOCATION_TYPE, SEGMENT_SPACE_MANAGEMENT
  3 from dba_tablespaces where TABLESPACE_NAME = 'ASSM';
  TABLESPACE_NAME BLOCK_SIZE EXTENT_MANAGEMENT ALLOCATION_TYPE SEGMENT_SPACE_MANAGEMENT
  ---------------- ---------- ----------------- --------------- ------------------------
  ASSM 8192 LOCAL UNIFORM AUTO
  SQL> create table my_objects tablespace assm
  2 as select * from all_objects;
  Table created
  
  ran hou wo men sui ji di cong table MY_OBJECTS zhong shan chu yi bu fen shu ju :
  SQL> select count(*) from my_objects;
  COUNT(*)
  ----------
  47828
  SQL> delete from my_objects where object_name like '%C%';
  16950 rows deleted
  SQL> delete from my_objects where object_name like '%U%';
  4503 rows deleted
  SQL> delete from my_objects where object_name like '%A%';
  6739 rows deleted
  
  xian zai wo men shi yong show_space he show_space_assm lai kan kan my_objects de shu ju cun chu zhuang kuang :
  SQL> exec show_space('MY_OBJECTS','DLINGER');
  Total Blocks............................680
  Total Bytes.............................5570560
  Unused Blocks...........................1
  Unused Bytes............................8192
  Last Used Ext FileId....................6
  Last Used Ext BlockId...................793
  Last Used Block.........................4
  PL/SQL guo cheng yi cheng gong wan cheng 。
  SQL> exec show_space_assm('MY_OBJECTS','DLINGER');
  free space 0-25% Blocks:................0
  free space 25-50% Blocks:...............205
  free space 50-75% Blocks:...............180
  free space 75-100% Blocks:..............229
  Full Blocks:............................45
  Unformatted blocks:.....................0
  PL/SQL guo cheng yi cheng gong wan cheng 。
  
  zhe li ,table my_objects de HWM xia you 679 ge block, qi zhong ,free space wei 25-50% de block you 205 ge ,free space wei 50-75% de block you 180 ge ,free space wei 75-100% de block you 229 ge ,full space de block zhi you 45 ge , zhe zhong qing kuang xia , wo men xu yao dui zhe ge table de xian you shu ju xing jin xing chong zu 。
  yao shi yong assm shang de shink, shou xian wo men xu yao shi gai biao zhi chi xing yi dong , ke yi yong zhe yang de ming ling lai wan cheng :
  alter table my_objects enable row movement;
  xian zai , jiu ke yi lai jiang di my_objects de HWM, hui shou kong jian le , shi yong ming ling :
  alter table bookings shrink space;
  wo men ju ti de kan yi xia shi yan de jie guo :
  SQL> alter table my_objects enable row movement;
  biao yi geng gai 。
  SQL> alter table my_objects shrink space;
  biao yi geng gai 。
  SQL> exec show_space('MY_OBJECTS','DLINGER');
  Total Blocks............................265
  Total Bytes.............................2170880
  Unused Blocks...........................2
  Unused Bytes............................16384
  Last Used Ext FileId....................6
  Last Used Ext BlockId...................308
  Last Used Block.........................3
  PL/SQL guo cheng yi cheng gong wan cheng 。
  SQL> exec show_space_assm('MY_OBJECTS','DLINGER');
  free space 0-25% Blocks:................0
  free space 25-50% Blocks:...............1
  free space 50-75% Blocks:...............0
  free space 75-100% Blocks:..............0
  Full Blocks:............................249
  Unformatted blocks:.....................0
  PL/SQL guo cheng yi cheng gong wan cheng 。
  
  zai zhi xing wan shrink ming ling hou , wo men ke yi kan dao ,table my_objects de HWM xian zai jiang dao le 264 de wei zhi , er qie HWM xia de block de kong jian shi yong zhuang kuang ,full space de block you 249 ge ,free space wei 25-50% Block zhi you 1 ge 。
  wo men jie xia lai tao lun yi xia shrink de shi xian ji zhi , wo men tong yang shi yong tao lun move ji zhi de na ge shi yan lai guan cha 。
  SQL> create table TEST_HWM (id int ,name char(2000)) tablespace ASSM;
  Table created
  
  wang table test_hwm zhong cha ru ru xia de shu ju :
  insert into TEST_HWM values (1,'aa');
  insert into TEST_HWM values (2,'bb');
  insert into TEST_HWM values (2,'cc');
  insert into TEST_HWM values (3,'dd');
  insert into TEST_HWM values (4,'ds');
  insert into TEST_HWM values (5,'dss');
  insert into TEST_HWM values (6,'dss');
  insert into TEST_HWM values (7,'ess');
  insert into TEST_HWM values (8,'es');
  insert into TEST_HWM values (9,'es');
  insert into TEST_HWM values (10,'es');
  wo men lai kan kan zhe ge table de rowid he block de ID he xin xi :
  SQL> select rowid , id,name from TEST_HWM;
  ROWID ID NAME
  ------------------ ---------- ----- ---------
  AAANhqAAGAAAAFHAAA 1 aa
  AAANhqAAGAAAAFHAAB 2 bb
  AAANhqAAGAAAAFHAAC 2 cc
  AAANhqAAGAAAAFIAAA 3 dd
  AAANhqAAGAAAAFIAAB 4 ds
  AAANhqAAGAAAAFIAAC 5 dss
  AAANhqAAGAAAAFJAAA 6 dss
  AAANhqAAGAAAAFJAAB 7 ess
  AAANhqAAGAAAAFJAAC 8 es
  AAANhqAAGAAAAFKAAA 9 es
  AAANhqAAGAAAAFKAAB 10 es
  11 rows selected
  SQL> select EXTENT_ID,FILE_ID,RELATIVE_FNO,BLOCK_ID,BLOCKS
  2 from dba_extents where segment_name='TEST_HWM' ;
  EXTENT_ID FILE_ID RELATIVE_FNO BLOCK_ID BLOCKS
  ---------- ---------- ------------ ---------- ----------
  0 6 6 324 5
  1 6 6 329 5
  
  ran hou cong table test_hwm zhong shan chu yi xie shu ju :
  delete from TEST_HWM where id = 2;
  delete from TEST_HWM where id = 4;
  delete from TEST_HWM where id = 3;
  delete from TEST_HWM where id = 7;
  delete from TEST_HWM where id = 8;
  guan cha table test_hwm de rowid he blockid de xin xi :
  SQL> select rowid , id,name from TEST_HWM;
  ROWID ID NAME
  ------------------ ---------- ----- --------
  AAANhqAAGAAAAFHAAA 1 aa
  AAANhqAAGAAAAFIAAC 5 dss
  AAANhqAAGAAAAFJAAA 6 dss
  AAANhqAAGAAAAFKAAA 9 es
  AAANhqAAGAAAAFKAAB 10 es
  SQL> select EXTENT_ID,FILE_ID,RELATIVE_FNO,BLOCK_ID,BLOCKS
  2 from dba_extents where segment_name='TEST_HWM' ;
  EXTENT_ID FILE_ID RELATIVE_FNO BLOCK_ID BLOCKS
  ---------- ---------- ------------ ---------- ----------
  0 6 6 324 5
  1 6 6 329 5
  
  cong yi shang de xin xi , wo men ke yi kan dao , zai table test_hwm zhong , sheng xia de shu ju shi fen bu zai AAAAFH,AAAAFI,AAAAFJ,AAAAFK zhe yang si ge lian xu de block zhong 。
  SQL> exec show_space_assm('TEST_HWM','DLINGER');
  free space 0-25% Blocks:................0
  free space 25-50% Blocks:...............1
  free space 50-75% Blocks:...............3
  free space 75-100% Blocks:..............3
  Full Blocks:............................0
  Unformatted blocks:.....................0
  
  tong guo show_space_assm wo men ke yi kan dao mu qian zhe si ge block de kong jian shi yong zhuang kuang ,AAAAFH,AAAAFI,AAAAFJ shang ge you yi xing shu ju , wo men cai ce free space wei 50-75% de 3 ge block shi zhe san ge block, na me free space wei 25-50% de 1 ge block jiu shi AAAAFK le , sheng xia free space wei 75-100% de 3 ge block, shi HWM xia yi ge shi hua de shang wei shi yong de block。( guan yu assm xia hwm de yi dong wo men qian mian yi jing xiang xi di tao lun guo le , zai extent bu da yu yu 16 ge block shi , shi yi yi ge extent wei dan wei lai yi dong de )
  ran hou , wo men dui table my_objects zhi xing shtink de cao zuo :
  SQL> alter table test_hwm enable row movement;
  Table altered
  SQL> alter table test_hwm shrink space;
  Table altered
  SQL> select rowid ,id,name from TEST_HWM;
  ROWID ID NAME
  ------------------ ---------- ------ -----------
  AAANhqAAGAAAAFHAAA 1 aa
  AAANhqAAGAAAAFHAAB 10 es
  AAANhqAAGAAAAFHAAD 9 es
  AAANhqAAGAAAAFIAAC 5 dss
  AAANhqAAGAAAAFJAAA 6 dss
  SQL> select EXTENT_ID,FILE_ID,RELATIVE_FNO,BLOCK_ID,BLOCKS
  2 from dba_extents where segment_name='TEST_HWM' ;
  EXTENT_ID FILE_ID RELATIVE_FNO BLOCK_ID BLOCKS
  ---------- ---------- ------------ ---------- ----------
  0 6 6 324 5
  1 6 6 329 5
  
  dang zhi xing le shrink cao zuo hou , you yi si de xian xiang chu xian le 。 wo men lai kan kan oracle shi ru he yi dong xing shu ju de , zhe li de qing kuang he move yi jing bu tai yi yang le 。 wo men zhi dao , zai move cao zuo de shi hou , suo you xing de rowid dou fa sheng le bian hua ,table suo wei yu de block de qu yu ye fa sheng le bian hua , dan shi suo you xing wu li cun chu de shun xu dou mei you fa sheng bian hua , suo yi wo men de dao de jie lun shi ,oracle yi block wei dan wei , jin xing le block jian de shu ju copy。 na me shrink hou , wo men fa xian , bu fen xing shu ju de rowid fa sheng le bian hua , tong shi , bu fen xing shu ju de wu li cun chu de shun xu ye fa sheng le bian hua , er table suo wei yu de block de qu yu que mei you bian hua , zhe jiu shuo ming ,shrink zhi yi dong le table qi zhong yi bu fen de xing shu ju , lai wan cheng shi fang kong jian , er qie , zhe ge guo cheng shi zai table dang qian suo shi yong de block zhong wan cheng de 。
  na me Oracle ju ti yi dong xing shu ju de guo cheng shi zen yang de ne ? wo men gen ju zhe yang de shi yan jie guo , ke yi lai cai ce yi xia :
  Oracle shi yi xing wei dan wei lai yi dong shu ju de 。Oracle cong dang qian table cun chu de zui hou yi xing shu ju kai shi yi dong , cong dang qian table zui xian shi yong de block kai shi sou suo kong jian , suo yi ,shrink zhi qian ,rownum=10 de na xing shu ju (10,es), bei yi dong dao block AAAAFH shang , xie dao (1,aa) zhe xing shu ju de hou mian , suo yi (10,es) de rownum he rowid tong shi fa sheng gai bian 。 ran hou shi (9,es) zhe xing shu ju , chong fu shang shu guo cheng 。 zhe shi oracle cong hou xiang qian yi dong xing shu ju de da zhi zun xun de gui ze , na me ju ti yi dong xing shu ju de de suan fa shi bi jiao fu duo de , bao kuo xiang ASSM de table zhong insert shu ju shi yong block de shun xu de suan fa ye shi bi jiao fu duo de , da jia you xing qu de ke yi zi ji lai yan jiu , zai zhe li wo men bu duo zuo tao lun 。
  wo men huan ke yi zai shrink table de tong shi shrink zhe ge table shang de index:
  alter table my_objects shrink space cascade;
  tong yang di , zhe ge cao zuo zhi you dang table shang de index ye shi ASSM shi , cai neng shi yong 。
  guan yu ri zhi de wen ti , wo men dui bi le tong yang shu ju liang he fen bu zhuang kuang de liang zhang table, zai move he shrink xia sheng cheng de redo size(table shang mei you index de qing kuang xia ):
  SQL> select tablespace_name,SEGMENT_SPACE_MANAGEMENT from dba_tablespaces
  2 where tablespace_name in('ASSM','HWM');
  TABLESPACE_NAME SEGMENT_SPACE_MANAGEMENT
  ------------------------------ ------------------------
  ASSM AUTO
  HWM MANUAL
  SQL> create table my_objects tablespace ASSM as select * from all_objects where rownum<20000;
  Table created
  SQL> create table my_objects1 tablespace HWM as select * from all_objects where rownum<20000;
  Table created
  SQL> select bytes/1024/1024 from user_segments where segment_name = 'MY_OBJECTS';
  BYTES/1024/1024
  ---------------
  2.1875
  SQL> delete from my_objects where object_name like '%C%';
  7278 rows deleted
  SQL> delete from my_objects1 where object_name like '%C%';
  7278 rows deleted
  SQL> delete from my_objects where object_name like '%U%';
  2732 rows deleted
  SQL> delete from my_objects1 where object_name like '%U%';
  2732 rows deleted
  SQL> commit;
  Commit complete
  SQL> alter table my_objects enable row movement;
  Table altered
  SQL> select value from v$mystat, v$statname
  2 where v$mystat.statistic# = v$statname.statistic#
  3 and v$statname.name = 'redo size';
  VALUE
  ----------
  27808792
  SQL> alter table my_objects shrink space;
  Table altered
  SQL> select value from v$mystat, v$statname
  2 where v$mystat.statistic# = v$statname.statistic#
  3 and v$statname.name = 'redo size';
  VALUE
  ----------
  32579712
  SQL> alter table my_objects1 move;
  Table altered
  SQL> select value from v$mystat, v$statname
  2 where v$mystat.statistic# = v$statname.statistic#
  3 and v$statname.name = 'redo size';
  VALUE
  ----------
  32676784
  dui yu table my_objects, jin xing shrink, chan sheng le 32579712 – 27808792=4770920, yue 4.5M de redo ; dui table my_objects1 jin xing move, chan sheng le 32676784-32579712= 97072, yue 95K de redo size。 na me , yu move bi jiao qi lai ,shrink de ri zhi xie yao da de duo 。
  
  Shrink de ji dian wen ti :
  a. shrink hou index shi fou xu yao rebuild:
  yin wei shrink de cao zuo ye hui gai bian xing shu ju de rowid, na me , ru guo table shang you index shi ,shrink table hou index hui bu hui bian wei UNUSABLE ne ? wo men lai kan zhe yang de shi yan , tong yang gou jian my_objects de ce shi biao :
  create table my_objects tablespace ASSM as select * from all_objects where rownum<20000;
  create index i_my_objects on my_objects (object_id);
  delete from my_objects where object_name like '%C%';
  delete from my_objects where object_name like '%U%';
  xian zai wo men lai shrink table my_objects:
  SQL> alter table my_objects enable row movement;
  Table altered
  SQL> alter table my_objects shrink space;
  Table altered
  SQL> select index_name,status from user_indexes where index_name='I_MY_OBJECTS';
  INDEX_NAME STATUS
  ------------------------------ --------
  I_MY_OBJECTS VALID
  wo men fa xian ,table my_objects shang de index de zhuang tai wei VALID, gu ji shrink zai yi dong xing shu ju shi , ye yi qi wei hu le index shang xiang ying xing de shu ju rowid de xin xi 。 wo men ren wei , zhe shi dui yu move cao zuo hou xu yao rebuild index de gai jin 。 dan shi ru guo yi ge table shang de index shu liang jiao duo , wo men zhi dao , wei hu index de cheng ben shi bi jiao gao de ,shrink guo cheng zhong yong lai wei hu index de cheng ben ye hui bi jiao gao 。
  b. shrink shi dui table de lock
  zai dui table jin xing shrink shi , hui dui table jin xing zen yang de suo ding ne ? dang wo men dui table MY_OBJECTS jin xing shrink cao zuo shi , cha xun v$locked_objects shi tu ke yi fa xian ,table MY_OBJECTS shang jia le row-X (SX) de lock:
  SQL>select OBJECT_ID, SESSION_ID,ORACLE_USERNAME,LOCKED_MODE from v$locked_objects;
  OBJECT_ID SESSION_ID ORACLE_USERNAME LOCKED_MODE
  ---------- ---------- ------------------ -----------
  55422 153 DLINGER 3
  SQL> select object_id from user_objects where object_name = 'MY_OBJECTS';
  OBJECT_ID
  ----------
  55422
  na me , dang table zai jin xing shrink shi , wo men dui table shi ke yi jin xing DML cao zuo de 。
  c. shrink dui kong jian de yao qiu
  wo men zai qian mian tao lun le shrink de shu ju de yi dong ji zhi , ji ran oracle shi cong hou xiang qian yi dong xing shu ju , na me ,shrink de cao zuo jiu bu hui xiang move yi yang ,shrink bu xu yao shi yong e wai de kong xian kong jian 。【原文】【汉音对照
 
 
 
 
 
 
 
 
日版宠物情人插曲《Winding Road》歌词

日版宠物情人2017的插曲,很带节奏感,日语的,女生唱的。 最后听见是在第8集的时候女主手割伤了,然后男主用嘴帮她吸了一下,插曲就出来了。 歌手:Def...

兄弟共妻,我成了他们夜里的美食

老钟家的两个儿子很特别,就是跟其他的人不太一样,魔一般的执着。兄弟俩都到了要结婚的年龄了,不管自家老爹怎么磨破嘴皮子,兄弟俩说不娶就不娶,老父母为兄弟两操碎了心...

如何磨出破洞牛仔裤?牛仔裤怎么剪破洞?

把牛仔裤磨出有线的破洞 1、具体工具就是磨脚石,下面垫一个硬物,然后用磨脚石一直磨一直磨,到把那块磨薄了,用手撕开就好了。出来的洞啊很自然的。需要猫须的话调几...

我就是扫描下图得到了敬业福和爱国福

先来看下敬业福和爱国福 今年春节,支付宝再次推出了“五福红包”活动,表示要“把欠大家的敬业福都还给大家”。 今天该活动正式启动,和去年一样,需要收集“五福”...

冰箱异味产生的原因和臭味去除的方法

有时候我们打开冰箱就会闻到一股异味,冰箱里的这种异味是因为一些物质发出的气味的混合体,闻起来让人恶心。 产生这些异味的主要原因有以下几点。 1、很多人有这种习...

 
 
cong 10g kai shi ,oracle kai shi ti gong Shrink de ming ling , jia ru wo men de biao kong jian zhong zhi chi zi dong duan kong jian guan li (ASSM), jiu ke yi shi yong zhe ge te xing suo xiao duan , ji jiang di HWM。 zhe li xu yao qiang tiao yi dian ,10g de zhe ge xin te xing , jin dui ASSM biao kong jian you xiao , fou ze hui bao ORA-10635: Invalid segment or tablespace type。 zai zhe li , wo men lai tao lun ru he dui yi ge ASSM de segment hui shou lang fei de kong jian 。 tong yang , wo men yong xi tong shi tu all_objects lai zai tablespace ASSM shang chuang jian ce shi biao my_objects, zhe yi xiao jie de nei rong , shi yan huan jing wei oracle10.1.0.2: SQL> select * from v$version; BANNER ---------------------------------------------------------------- Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Prod PL/SQL Release 10.1.0.2.0 - Production CORE 10.1.0.2.0 Production TNS for 32-bit Windows: Version 10.1.0.2.0 - Production NLSRTL Version 10.1.0.2.0 – Production SQL> select TABLESPACE_NAME,BLOCK_SIZE,EXTENT_MANAGEMENT, 2 ALLOCATION_TYPE, SEGMENT_SPACE_MANAGEMENT 3 from dba_tablespaces where TABLESPACE_NAME = 'ASSM'; TABLESPACE_NAME BLOCK_SIZE EXTENT_MANAGEMENT ALLOCATION_TYPE SEGMENT_SPACE_MANAGEMENT ---------------- ---------- ----------------- --------------- ------------------------ ASSM 8192 LOCAL UNIFORM AUTO SQL> create table my_objects tablespace assm 2 as select * from all_objects; Table created ran hou wo men sui ji di cong table MY_OBJECTS zhong shan chu yi bu fen shu ju : SQL> select count(*) from my_objects; COUNT(*) ---------- 47828 SQL> delete from my_objects where object_name like '%C%'; 16950 rows deleted SQL> delete from my_objects where object_name like '%U%'; 4503 rows deleted SQL> delete from my_objects where object_name like '%A%'; 6739 rows deleted xian zai wo men shi yong show_space he show_space_assm lai kan kan my_objects de shu ju cun chu zhuang kuang : SQL> exec show_space('MY_OBJECTS','DLINGER'); Total Blocks............................680 Total Bytes.............................5570560 Unused Blocks...........................1 Unused Bytes............................8192 Last Used Ext FileId....................6 Last Used Ext BlockId...................793 Last Used Block.........................4 PL/SQL guo cheng yi cheng gong wan cheng 。 SQL> exec show_space_assm('MY_OBJECTS','DLINGER'); free space 0-25% Blocks:................0 free space 25-50% Blocks:...............205 free space 50-75% Blocks:...............180 free space 75-100% Blocks:..............229 Full Blocks:............................45 Unformatted blocks:.....................0 PL/SQL guo cheng yi cheng gong wan cheng 。 zhe li ,table my_objects de HWM xia you 679 ge block, qi zhong ,free space wei 25-50% de block you 205 ge ,free space wei 50-75% de block you 180 ge ,free space wei 75-100% de block you 229 ge ,full space de block zhi you 45 ge , zhe zhong qing kuang xia , wo men xu yao dui zhe ge table de xian you shu ju xing jin xing chong zu 。 yao shi yong assm shang de shink, shou xian wo men xu yao shi gai biao zhi chi xing yi dong , ke yi yong zhe yang de ming ling lai wan cheng : alter table my_objects enable row movement; xian zai , jiu ke yi lai jiang di my_objects de HWM, hui shou kong jian le , shi yong ming ling : alter table bookings shrink space; wo men ju ti de kan yi xia shi yan de jie guo : SQL> alter table my_objects enable row movement; biao yi geng gai 。 SQL> alter table my_objects shrink space; biao yi geng gai 。 SQL> exec show_space('MY_OBJECTS','DLINGER'); Total Blocks............................265 Total Bytes.............................2170880 Unused Blocks...........................2 Unused Bytes............................16384 Last Used Ext FileId....................6 Last Used Ext BlockId...................308 Last Used Block.........................3 PL/SQL guo cheng yi cheng gong wan cheng 。 SQL> exec show_space_assm('MY_OBJECTS','DLINGER'); free space 0-25% Blocks:................0 free space 25-50% Blocks:...............1 free space 50-75% Blocks:...............0 free space 75-100% Blocks:..............0 Full Blocks:............................249 Unformatted blocks:.....................0 PL/SQL guo cheng yi cheng gong wan cheng 。 zai zhi xing wan shrink ming ling hou , wo men ke yi kan dao ,table my_objects de HWM xian zai jiang dao le 264 de wei zhi , er qie HWM xia de block de kong jian shi yong zhuang kuang ,full space de block you 249 ge ,free space wei 25-50% Block zhi you 1 ge 。 wo men jie xia lai tao lun yi xia shrink de shi xian ji zhi , wo men tong yang shi yong tao lun move ji zhi de na ge shi yan lai guan cha 。 SQL> create table TEST_HWM (id int ,name char(2000)) tablespace ASSM; Table created wang table test_hwm zhong cha ru ru xia de shu ju : insert into TEST_HWM values (1,'aa'); insert into TEST_HWM values (2,'bb'); insert into TEST_HWM values (2,'cc'); insert into TEST_HWM values (3,'dd'); insert into TEST_HWM values (4,'ds'); insert into TEST_HWM values (5,'dss'); insert into TEST_HWM values (6,'dss'); insert into TEST_HWM values (7,'ess'); insert into TEST_HWM values (8,'es'); insert into TEST_HWM values (9,'es'); insert into TEST_HWM values (10,'es'); wo men lai kan kan zhe ge table de rowid he block de ID he xin xi : SQL> select rowid , id,name from TEST_HWM; ROWID ID NAME ------------------ ---------- ----- --------- AAANhqAAGAAAAFHAAA 1 aa AAANhqAAGAAAAFHAAB 2 bb AAANhqAAGAAAAFHAAC 2 cc AAANhqAAGAAAAFIAAA 3 dd AAANhqAAGAAAAFIAAB 4 ds AAANhqAAGAAAAFIAAC 5 dss AAANhqAAGAAAAFJAAA 6 dss AAANhqAAGAAAAFJAAB 7 ess AAANhqAAGAAAAFJAAC 8 es AAANhqAAGAAAAFKAAA 9 es AAANhqAAGAAAAFKAAB 10 es 11 rows selected SQL> select EXTENT_ID,FILE_ID,RELATIVE_FNO,BLOCK_ID,BLOCKS 2 from dba_extents where segment_name='TEST_HWM' ; EXTENT_ID FILE_ID RELATIVE_FNO BLOCK_ID BLOCKS ---------- ---------- ------------ ---------- ---------- 0 6 6 324 5 1 6 6 329 5 ran hou cong table test_hwm zhong shan chu yi xie shu ju : delete from TEST_HWM where id = 2; delete from TEST_HWM where id = 4; delete from TEST_HWM where id = 3; delete from TEST_HWM where id = 7; delete from TEST_HWM where id = 8; guan cha table test_hwm de rowid he blockid de xin xi : SQL> select rowid , id,name from TEST_HWM; ROWID ID NAME ------------------ ---------- ----- -------- AAANhqAAGAAAAFHAAA 1 aa AAANhqAAGAAAAFIAAC 5 dss AAANhqAAGAAAAFJAAA 6 dss AAANhqAAGAAAAFKAAA 9 es AAANhqAAGAAAAFKAAB 10 es SQL> select EXTENT_ID,FILE_ID,RELATIVE_FNO,BLOCK_ID,BLOCKS 2 from dba_extents where segment_name='TEST_HWM' ; EXTENT_ID FILE_ID RELATIVE_FNO BLOCK_ID BLOCKS ---------- ---------- ------------ ---------- ---------- 0 6 6 324 5 1 6 6 329 5 cong yi shang de xin xi , wo men ke yi kan dao , zai table test_hwm zhong , sheng xia de shu ju shi fen bu zai AAAAFH,AAAAFI,AAAAFJ,AAAAFK zhe yang si ge lian xu de block zhong 。 SQL> exec show_space_assm('TEST_HWM','DLINGER'); free space 0-25% Blocks:................0 free space 25-50% Blocks:...............1 free space 50-75% Blocks:...............3 free space 75-100% Blocks:..............3 Full Blocks:............................0 Unformatted blocks:.....................0 tong guo show_space_assm wo men ke yi kan dao mu qian zhe si ge block de kong jian shi yong zhuang kuang ,AAAAFH,AAAAFI,AAAAFJ shang ge you yi xing shu ju , wo men cai ce free space wei 50-75% de 3 ge block shi zhe san ge block, na me free space wei 25-50% de 1 ge block jiu shi AAAAFK le , sheng xia free space wei 75-100% de 3 ge block, shi HWM xia yi ge shi hua de shang wei shi yong de block。( guan yu assm xia hwm de yi dong wo men qian mian yi jing xiang xi di tao lun guo le , zai extent bu da yu yu 16 ge block shi , shi yi yi ge extent wei dan wei lai yi dong de ) ran hou , wo men dui table my_objects zhi xing shtink de cao zuo : SQL> alter table test_hwm enable row movement; Table altered SQL> alter table test_hwm shrink space; Table altered SQL> select rowid ,id,name from TEST_HWM; ROWID ID NAME ------------------ ---------- ------ ----------- AAANhqAAGAAAAFHAAA 1 aa AAANhqAAGAAAAFHAAB 10 es AAANhqAAGAAAAFHAAD 9 es AAANhqAAGAAAAFIAAC 5 dss AAANhqAAGAAAAFJAAA 6 dss SQL> select EXTENT_ID,FILE_ID,RELATIVE_FNO,BLOCK_ID,BLOCKS 2 from dba_extents where segment_name='TEST_HWM' ; EXTENT_ID FILE_ID RELATIVE_FNO BLOCK_ID BLOCKS ---------- ---------- ------------ ---------- ---------- 0 6 6 324 5 1 6 6 329 5 dang zhi xing le shrink cao zuo hou , you yi si de xian xiang chu xian le 。 wo men lai kan kan oracle shi ru he yi dong xing shu ju de , zhe li de qing kuang he move yi jing bu tai yi yang le 。 wo men zhi dao , zai move cao zuo de shi hou , suo you xing de rowid dou fa sheng le bian hua ,table suo wei yu de block de qu yu ye fa sheng le bian hua , dan shi suo you xing wu li cun chu de shun xu dou mei you fa sheng bian hua , suo yi wo men de dao de jie lun shi ,oracle yi block wei dan wei , jin xing le block jian de shu ju copy。 na me shrink hou , wo men fa xian , bu fen xing shu ju de rowid fa sheng le bian hua , tong shi , bu fen xing shu ju de wu li cun chu de shun xu ye fa sheng le bian hua , er table suo wei yu de block de qu yu que mei you bian hua , zhe jiu shuo ming ,shrink zhi yi dong le table qi zhong yi bu fen de xing shu ju , lai wan cheng shi fang kong jian , er qie , zhe ge guo cheng shi zai table dang qian suo shi yong de block zhong wan cheng de 。 na me Oracle ju ti yi dong xing shu ju de guo cheng shi zen yang de ne ? wo men gen ju zhe yang de shi yan jie guo , ke yi lai cai ce yi xia : Oracle shi yi xing wei dan wei lai yi dong shu ju de 。Oracle cong dang qian table cun chu de zui hou yi xing shu ju kai shi yi dong , cong dang qian table zui xian shi yong de block kai shi sou suo kong jian , suo yi ,shrink zhi qian ,rownum=10 de na xing shu ju (10,es), bei yi dong dao block AAAAFH shang , xie dao (1,aa) zhe xing shu ju de hou mian , suo yi (10,es) de rownum he rowid tong shi fa sheng gai bian 。 ran hou shi (9,es) zhe xing shu ju , chong fu shang shu guo cheng 。 zhe shi oracle cong hou xiang qian yi dong xing shu ju de da zhi zun xun de gui ze , na me ju ti yi dong xing shu ju de de suan fa shi bi jiao fu duo de , bao kuo xiang ASSM de table zhong insert shu ju shi yong block de shun xu de suan fa ye shi bi jiao fu duo de , da jia you xing qu de ke yi zi ji lai yan jiu , zai zhe li wo men bu duo zuo tao lun 。 wo men huan ke yi zai shrink table de tong shi shrink zhe ge table shang de index: alter table my_objects shrink space cascade; tong yang di , zhe ge cao zuo zhi you dang table shang de index ye shi ASSM shi , cai neng shi yong 。 guan yu ri zhi de wen ti , wo men dui bi le tong yang shu ju liang he fen bu zhuang kuang de liang zhang table, zai move he shrink xia sheng cheng de redo size(table shang mei you index de qing kuang xia ): SQL> select tablespace_name,SEGMENT_SPACE_MANAGEMENT from dba_tablespaces 2 where tablespace_name in('ASSM','HWM'); TABLESPACE_NAME SEGMENT_SPACE_MANAGEMENT ------------------------------ ------------------------ ASSM AUTO HWM MANUAL SQL> create table my_objects tablespace ASSM as select * from all_objects where rownum<20000; Table created SQL> create table my_objects1 tablespace HWM as select * from all_objects where rownum<20000; Table created SQL> select bytes/1024/1024 from user_segments where segment_name = 'MY_OBJECTS'; BYTES/1024/1024 --------------- 2.1875 SQL> delete from my_objects where object_name like '%C%'; 7278 rows deleted SQL> delete from my_objects1 where object_name like '%C%'; 7278 rows deleted SQL> delete from my_objects where object_name like '%U%'; 2732 rows deleted SQL> delete from my_objects1 where object_name like '%U%'; 2732 rows deleted SQL> commit; Commit complete SQL> alter table my_objects enable row movement; Table altered SQL> select value from v$mystat, v$statname 2 where v$mystat.statistic# = v$statname.statistic# 3 and v$statname.name = 'redo size'; VALUE ---------- 27808792 SQL> alter table my_objects shrink space; Table altered SQL> select value from v$mystat, v$statname 2 where v$mystat.statistic# = v$statname.statistic# 3 and v$statname.name = 'redo size'; VALUE ---------- 32579712 SQL> alter table my_objects1 move; Table altered SQL> select value from v$mystat, v$statname 2 where v$mystat.statistic# = v$statname.statistic# 3 and v$statname.name = 'redo size'; VALUE ---------- 32676784 dui yu table my_objects, jin xing shrink, chan sheng le 32579712 – 27808792=4770920, yue 4.5M de redo ; dui table my_objects1 jin xing move, chan sheng le 32676784-32579712= 97072, yue 95K de redo size。 na me , yu move bi jiao qi lai ,shrink de ri zhi xie yao da de duo 。 Shrink de ji dian wen ti : a. shrink hou index shi fou xu yao rebuild: yin wei shrink de cao zuo ye hui gai bian xing shu ju de rowid, na me , ru guo table shang you index shi ,shrink table hou index hui bu hui bian wei UNUSABLE ne ? wo men lai kan zhe yang de shi yan , tong yang gou jian my_objects de ce shi biao : create table my_objects tablespace ASSM as select * from all_objects where rownum<20000; create index i_my_objects on my_objects (object_id); delete from my_objects where object_name like '%C%'; delete from my_objects where object_name like '%U%'; xian zai wo men lai shrink table my_objects: SQL> alter table my_objects enable row movement; Table altered SQL> alter table my_objects shrink space; Table altered SQL> select index_name,status from user_indexes where index_name='I_MY_OBJECTS'; INDEX_NAME STATUS ------------------------------ -------- I_MY_OBJECTS VALID wo men fa xian ,table my_objects shang de index de zhuang tai wei VALID, gu ji shrink zai yi dong xing shu ju shi , ye yi qi wei hu le index shang xiang ying xing de shu ju rowid de xin xi 。 wo men ren wei , zhe shi dui yu move cao zuo hou xu yao rebuild index de gai jin 。 dan shi ru guo yi ge table shang de index shu liang jiao duo , wo men zhi dao , wei hu index de cheng ben shi bi jiao gao de ,shrink guo cheng zhong yong lai wei hu index de cheng ben ye hui bi jiao gao 。 b. shrink shi dui table de lock zai dui table jin xing shrink shi , hui dui table jin xing zen yang de suo ding ne ? dang wo men dui table MY_OBJECTS jin xing shrink cao zuo shi , cha xun v$locked_objects shi tu ke yi fa xian ,table MY_OBJECTS shang jia le row-X (SX) de lock: SQL>select OBJECT_ID, SESSION_ID,ORACLE_USERNAME,LOCKED_MODE from v$locked_objects; OBJECT_ID SESSION_ID ORACLE_USERNAME LOCKED_MODE ---------- ---------- ------------------ ----------- 55422 153 DLINGER 3 SQL> select object_id from user_objects where object_name = 'MY_OBJECTS'; OBJECT_ID ---------- 55422 na me , dang table zai jin xing shrink shi , wo men dui table shi ke yi jin xing DML cao zuo de 。 c. shrink dui kong jian de yao qiu wo men zai qian mian tao lun le shrink de shu ju de yi dong ji zhi , ji ran oracle shi cong hou xiang qian yi dong xing shu ju , na me ,shrink de cao zuo jiu bu hui xiang move yi yang ,shrink bu xu yao shi yong e wai de kong xian kong jian 。
󰈣󰈤
  免责声明:本文仅代表作者个人观点,与王朝网络无关。王朝网络登载此文出于传递更多信息之目的,并不意味着赞同其观点或证实其描述,其原创性以及文中陈述文字和内容未经本站证实,对本文以及其中全部或者部分内容、文字的真实性、完整性、及时性本站不作任何保证或承诺,请读者仅作参考,并请自行核实相关内容。
 
 
上海柏雅居酒店模拍
清晨美丽的风景
梦清园的靓丽女生
妖艳的食人花(7)
且行且攝------"色"影大連
【做股票的朋友看过来-华尔街的铜牛】
端午节后闲逛
捐款赈灾吴川行 花絮
 
>>返回首页<<
 为你推荐
 
 
 转载本文
 UBB代码 HTML代码
复制到剪贴板...
 
 热帖排行
 
 
 
 
©2005- 王朝网络 版权所有