ALTER TABLE MOVE | SHRINK SPACE區別
ALTER TABLE MOVE | SHRINK SPACE區別
1.move
analyze table GC.R_WIP_KEYPARTS_T compute statistics;
SELECT BLOCKS,EMPTY_BLOCKS FROM DBA_TABLES
WHERE OWNER='GC' AND TABLE_NAME='R_WIP_KEYPARTS_T';
BLOCKS:137452
EMPTY_BLOCKS:1812
SELECT BLOCKS FROM DBA_SEGMENTS
WHERE SEGMENT_NAME='R_WIP_KEYPARTS_T';
BLOCKS:139264
DELETE from GC.R_WIP_KEYPARTS_T where rownum<5000;
這是資料被刪除,但HWM不會下降。
select count(distinct dbms_rowid.rowid_block_number(rowid)) from GC.R_WIP_KEYPARTS_T;
可以看到當前行所佔的block數。肯定比BLOCKS:137452少
ALTER TABLE GC.R_WIP_KEYPARTS_T MOVE
analyze table GC.R_WIP_KEYPARTS_T compute statistics;
SELECT BLOCKS,EMPTY_BLOCKS FROM DBA_TABLES
WHERE OWNER='GC' AND TABLE_NAME='R_WIP_KEYPARTS_T';
BLOCKS:137381 --move之後看到BLOCKS減少
EMPTY_BLOCKS:1883
SELECT BLOCKS FROM DBA_SEGMENTS WHERE SEGMENT_NAME='R_WIP_KEYPARTS_T';
BLOCKS:139264 --空間並沒有回收,總的塊數還是139264,但HWM下降到了137381
注:DBA_TABLES中欄位含義
BLOCKS* |
Number of used data blocks in the table |
EMPTY_BLOCKS* |
Number of empty (never used) data blocks in the table |
2.SHRINK SPACE
ALTER TABLE GC.R_WIP_keyparts_T SHRINK SPACE;
ANALYZE TABLE GC.R_WIP_KEYPARTS_T COMPUTE STATISTICS;
SELECT BLOCKS FROM DBA_SEGMENTS WHERE SEGMENT_NAME='R_WIP_KEYPARTS_T';
BLOCKS:137312
SELECT BLOCKS,EMPTY_BLOCKS FROM DBA_tableS WHERE table_NAME='R_WIP_KEYPARTS_T';
BLOCKS:136688 EMPTY_BLOCKS:624
可以看到除了HWM下的空間下降,HWM上未使用的空塊也會有回收
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/25583515/viewspace-714402/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- alter table move與shrink space
- [重慶思莊每日技術分享]-在為表新增了列後執行ALTER TABLE SHRINK SPACE 提示ORA-8102
- Oracle move和shrink釋放高水位空間Oracle
- mysql的ALTER TABLE命令MySql
- alter table set unused column
- [20190918]shrink space與ORA-08102錯誤.txt
- create table,show tables,describe table,DROP TABLE,ALTER TABLE ,怎麼使用?
- alter table nologging /*+APPEND PARALLEL(n)*/APPParallel
- alter table drop unused columns checkpoint
- mysql alter modify 和 change的區別MySql
- ALTER TABLE修改列的不同方法
- MySQL-ALTER TABLE命令學習[20180503]MySql
- alter system set event和set events的區別
- [20191129]ALTER TABLE MINIMIZE RECORDS_PER_BLOCK.txtBloC
- admin_move_table線上更改分割槽鍵
- MySQL的create table as 與 like區別MySql
- table中cesllspacing與cellpadding的區別詳解padding
- MySQL alter table時執行innobackupex全備再看Seconds_Behind_MasterMySqlAST
- 透過alter table 來實現重建表,同事大呼開眼界了
- word-wrap、word-break和white-space有什麼區別?
- mysql表操作(alter)/mysql欄位型別MySql型別
- 簡單理解 word-wrap、word-break 和 white-space 的區別
- MySQL中的alter table命令的基本使用方法及提速最佳化MySql
- CSS flex-shrinkCSSFlex
- vector::shrink_to_fit()
- Oracle 12.2之後ALTER TABLE .. MODIFY轉換非分割槽表為分割槽表Oracle
- WPF mvvm canvas move elements via mouse down, up and move eventsMVVMCanvas
- SQL__ALTERSQL
- MySQL ALTER命令MySql
- 瞭解區塊鏈虛擬機器:EVM、HVM、WASM、MOVE區塊鏈虛擬機ASM
- 區分 word-wrap/word-break/white-space
- Lua 列印table 實現型別python的repr用於table型別Python
- LeetCode—283—Move ZeroesLeetCode
- 聊聊dba_temp_free_space的allocated_space和free_space
- 線上改表工具oak-online-alter-table和pt-online-schema-change的使用限制總結
- oracle 分割槽表move和包含分割槽表的lob moveOracle
- mysql加快alter操作MySql
- 區分DDD中的Domain, Subdomain, Bounded Context, Problem/Solution SpaceAIContext
- Hilbert Space