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 move 和 alter table shrink space的區別
- alter table move 與shrink space的區別
- alter table move跟shrink space的區別
- alter table move跟shrink space的區別(轉)
- Oracle 11g alter table move與shrink spaceOracle
- [Oracle] Shrink space & Table move比較Oracle
- table move 與 shrink 的區別
- oracle 10g__alter table shrink space compactOracle 10g
- Oracle 10g Shrink Table - Shrink Space 收縮空間Oracle 10g
- alter table table_name move ; 在自身表空間move是如何操作的?
- oracle10g_alter table shrink space_compact_cascade回收空間測試(一)Oracle
- 表、索引遷移表空間alter table move索引
- v$lock之alter table drop column與alter table set unused column區別系列五
- 測試alter table shrink space compact cascade及學習user_tables相關列的含義
- alter table列管理的一些區別
- Oracle IZ0-053 Q277(Table shrink space)Oracle
- How to adjust the high watermark in ORACLE 10g – ALTER TABLE SHRINKOracle 10g
- 測試alter table storage及dbms_space_admin包
- oracle shrink tableOracle
- [重慶思莊每日技術分享]-在為表新增了列後執行ALTER TABLE SHRINK SPACE 提示ORA-8102
- shrink space的最佳實踐
- 【轉】Oracle:MOVE與SHRINK命令相比較Oracle
- alter database和alter system和alter session的區別DatabaseSession
- Oracle中shrink space命令詳解Oracle
- Alter table for ORACLEOracle
- Oracle move和shrink釋放高水位空間Oracle
- oracle10g shrink space 降低HWMOracle
- alter system events與alter system event的區別
- 7 、shrink table and its dependent segments
- ALTER DATABASE 與 ALTER TABLESPACE OFFLINE的區別Database
- 忍不住問下alter system 和alter database的區別Database
- ALTER SYSTEM SWITCH LOGFILE ALTER SYSTEM ARCHIVELOG CURRENT 區別Hive
- mysql的ALTER TABLE命令MySql
- oracle alter table詳解Oracle
- alter table using indexIndex
- Oracle ASM Free Space TableOracleASM
- drop table和truncate table的區別