測試alter table shrink space compact cascade及學習user_tables相關列的含義
--測試alter table shrink space compact cascade及學習user_tables相關列的含義
SQL> alter table test_shrink enable row movement;--alter table shrink space須開啟行移動
Table altered.
SQL> select table_name,blocks,empty_blocks,num_rows,to_char(last_analyzed,'yyyy-mm-dd hh24:mi:ss'),row_movement from user_tables where table_name='TEST_SHRINK';--查測試表相關資訊
TABLE_NAME BLOCKS EMPTY_BLOCKS NUM_ROWS TO_CHAR(LAST_ANALYZ ROW_MOVE
------------------------------ ---------- ------------ ---------- ------------------- -------- --blocks是真正使用的塊數 num_rows 表中行的記錄數
TEST_SHRINK ENABLED
SQL> select segment_name,blocks from user_segments where segment_type='TABLE' and segment_name='TEST_SHRINK';--查詢測試表segment
SEGMENT_NAME BLOCKS
--------------------------------------------------------------------------------- ----------
TEST_SHRINK 12288
SQL> exec dbms_stats.gather_table_stats(ownname=>NULL,tabname=>'TEST_SHRINK',cascade=>true);--分析下測試表
PL/SQL procedure successfully completed.
SQL> select table_name,blocks,empty_blocks,num_rows,to_char(last_analyzed,'yyyy-mm-dd hh24:mi:ss'),row_movement from user_tables where table_name='TEST_SHRINK';--再次檢視測試表相關資訊
TABLE_NAME BLOCKS EMPTY_BLOCKS NUM_ROWS TO_CHAR(LAST_ANALYZ ROW_MOVE
------------------------------ ---------- ------------ ---------- ------------------- --------
TEST_SHRINK 12040 0 862741 2010-08-28 14:17:40 ENABLED
SQL> select segment_name,blocks from user_segments where segment_type='TABLE' and segment_name='TEST_SHRINK';
SEGMENT_NAME BLOCKS
--------------------------------------------------------------------------------- ----------
TEST_SHRINK 12288
SQL> delete from test_shrink where rownum<=200000;--刪除20w記錄從測試表
200000 rows deleted.
SQL> commit;
Commit complete.
SQL> select segment_name,blocks from user_segments where segment_type='TABLE' and segment_name='TEST_SHRINK';--測試表segment,發現刪除segment沒有變化喲
SEGMENT_NAME BLOCKS
--------------------------------------------------------------------------------- ----------
TEST_SHRINK 12288
SQL> alter table test_shrink shrink space;--沒變化是吧，用alter shrink試下看看測試表segment有變化嗎
Table altered.
SQL> select segment_name,blocks from user_segments where segment_type='TABLE' and segment_name='TEST_SHRINK';---這下測試表(blocks表示分配給測試表segment的block個數）segment變了吧,從原來的12288塊到8952塊，少
了近4000塊
SEGMENT_NAME BLOCKS
--------------------------------------------------------------------------------- ----------
TEST_SHRINK 8952
SQL> exec dbms_stats.gather_table_stats(ownname=>NULL,tabname=>'TEST_SHRINK',cascade=>true);
PL/SQL procedure successfully completed.
SQL> select table_name,blocks,empty_blocks,num_rows,to_char(last_analyzed,'yyyy-mm-dd hh24:mi:ss'),row_movement from user_tables ---user_tables中的blocks總是小於user_segments的blockswhere
table_name='TEST_SHRINK';--
TABLE_NAME BLOCKS EMPTY_BLOCKS NUM_ROWS TO_CHAR(LAST_ANALYZ ROW_MOVE
------------------------------ ---------- ------------ ---------- ------------------- --------
TEST_SHRINK 8807 0 659108 2010-08-28 14:35:01 ENABLED
SQL>
小結:
小結：
1,alter table shrink space compact cascade;對大表或大索引操作會產生大量日誌
2,alter table shrink space;--加上compact選項僅重新整理segment 空間，並壓縮表的記錄在以後進行release空間.但資料庫並不調整hwm及釋放空間.為了釋放空間.你必須再發布alter table shrink space
--compact用於把一個長操作分割為兩個較短的操作
--須開啟行移動
--cascade會級聯對其依賴物件（比如上面的索引）進行壓緊操作
3,它的一些限制條件：
在叢集表，long列的表不能採用shrink 操作
在基於函式索引或點陣圖聯接索引不支援shrink操作
就是你指定了cascade選項，也不能shrink 索引組織表的對映表
壓縮表不能用shrink操作
構建了on commit物化檢視的主表，在shrink操作後，rowid實化檢視必須重建
4,alter table shrink space;--僅可對automatic segment management tablespace方式的table,index_orginized table or overflow segment,index,partition,lob segment,mv,mv log
--壓縮segment,調整hwm,並馬上釋放空間
5,user_tables某些列（請查對官方手冊，若標有*標記，表示此列須收集統計資訊才會被填充,比如num_rows，所以及時分析表很重要啊
blocks表示使用的塊數 empty_blocks 表示從未使用的塊數 avg_row_len 表示每個行的長度（以byte計）
nested 表示是否為nested table(值為y or n)
iot_type表示是否為index-orginized table(值為iot,iot_overflow,若不是iot,值為null)
temporary表示在當前會話是否僅僅看到物件上面的資料
global_stats表示（對於分割槽表），是收集了全表的統計資訊（值為yes)還是根據分割槽表的基礎表或子分割槽估計統計資訊（值 為no)
duration 表示臨時表中資料的保持時間（值sys$session表記錄僅在會話期間保持），而值sys$transaction在commit後刪除
skip_corrupt表示oracle在檢查表或索引中標記為破壞狀態的塊,是否進行忽略處理呢（值為enabled or disabled),為了 enabled必須用dbms_repair.skip_corrupt_blocks標記壞塊
---測試學習user_segment相關列含義及user_tables相關列含義，且二者字典的關聯
SQL> select table_name,blocks,blocks*8/1024 mb,empty_blocks,num_rows,to_char(last_analyzed,'yyyy-mm-dd hh24:mi:ss'),row_movement from user_tables where table_name='TEST_SHRINK';
TABLE_NAME BLOCKS MB EMPTY_BLOCKS NUM_ROWS TO_CHAR(LAST_ANALYZ ROW_MOVE
------------------------------ ---------- ---------- ------------ ---------- ------------------- -------- ###表有68m左右
TEST_SHRINK 8807 68.8046875 0 659108 2010-08-28 14:35:01 ENABLED
SQL> select segment_name,bytes/1024/1024 mb from user_segments where segment_name='TEST_SHRINK'; ##表有69m 左右(同上面68m 差不多）
SEGMENT_NAME MB
--------------------------------------------------------------------------------- ----------
TEST_SHRINK 69.9375
SQL> select segment_name,segment_type,initial_extent,next_extent,pct_increase,min_extents,max_extents from user_segments where segment_name='TEST_SHRINK';---請注意initial_extent列，為65536bytes，也就是一個8
block的extent,請繼續對應檢視user_extents字典
SQL> select segment_name,segment_type,initial_extent,next_extent,pct_increase,min_extents,max_extents,extents from user_segments where segment_name='TEST_SHRINK';##有80個extent
SEGMENT_NAME SEGMENT_TYPE INITIAL_EXTENT NEXT_EXTENT PCT_INCREASE MIN_EXTENTS MAX_EXTENTS EXTENTS
--------------------------------------------------------------------------------- ------------------ -------------- ----------- ------------ ----------- ----------- ----------
TEST_SHRINK TABLE 65536 1 2147483645 80
SQL>
SQL> select count(*) from user_extents where segment_name='TEST_SHRINK';--這不，對應上了，就是分配了80個extent為測試表segment
COUNT(*)
----------
80
SQL> select segment_name,extent_id,bytes,blocks from user_extents where segment_name='TEST_SHRINK';##oracle在分配extent時會根據不同演算法可能每個extent的大小不一樣，也就是每個extent包含的blocks個數不同
SEGMENT_NAME EXTENT_ID BYTES BLOCKS
--------------------------------------------------------------------------------- ---------- ---------- ----------
TEST_SHRINK 0 65536 8
TEST_SHRINK 1 65536 8
TEST_SHRINK 2 65536 8
TEST_SHRINK 3 65536 8
TEST_SHRINK 4 65536 8
TEST_SHRINK 5 65536 8
TEST_SHRINK 6 65536 8
TEST_SHRINK 7 65536 8
TEST_SHRINK 8 65536 8
TEST_SHRINK 9 65536 8
TEST_SHRINK 10 65536 8
SEGMENT_NAME EXTENT_ID BYTES BLOCKS
--------------------------------------------------------------------------------- ---------- ---------- ----------
TEST_SHRINK 11 65536 8
TEST_SHRINK 12 65536 8
TEST_SHRINK 13 65536 8
TEST_SHRINK 14 65536 8
TEST_SHRINK 15 65536 8
TEST_SHRINK 16 1048576 128
TEST_SHRINK 17 1048576 128
TEST_SHRINK 18 1048576 128
TEST_SHRINK 19 1048576 128
TEST_SHRINK 20 1048576 128
TEST_SHRINK 21 1048576 128
SEGMENT_NAME EXTENT_ID BYTES BLOCKS
--------------------------------------------------------------------------------- ---------- ---------- ----------
TEST_SHRINK 22 1048576 128
TEST_SHRINK 23 1048576 128
TEST_SHRINK 24 1048576 128
TEST_SHRINK 25 1048576 128
TEST_SHRINK 26 1048576 128
TEST_SHRINK 27 1048576 128
TEST_SHRINK 28 1048576 128
TEST_SHRINK 29 1048576 128
TEST_SHRINK 30 1048576 128
TEST_SHRINK 31 1048576 128
TEST_SHRINK 32 1048576 128
SEGMENT_NAME EXTENT_ID BYTES BLOCKS
--------------------------------------------------------------------------------- ---------- ---------- ----------
TEST_SHRINK 33 1048576 128
TEST_SHRINK 34 1048576 128
TEST_SHRINK 35 1048576 128
TEST_SHRINK 36 1048576 128
TEST_SHRINK 37 1048576 128
TEST_SHRINK 38 1048576 128
TEST_SHRINK 39 1048576 128
TEST_SHRINK 40 1048576 128
TEST_SHRINK 41 1048576 128
TEST_SHRINK 42 1048576 128
TEST_SHRINK 43 1048576 128
SEGMENT_NAME EXTENT_ID BYTES BLOCKS
--------------------------------------------------------------------------------- ---------- ---------- ----------
TEST_SHRINK 44 1048576 128
TEST_SHRINK 45 1048576 128
TEST_SHRINK 46 1048576 128
TEST_SHRINK 47 1048576 128
TEST_SHRINK 48 1048576 128
TEST_SHRINK 49 1048576 128
TEST_SHRINK 50 1048576 128
TEST_SHRINK 51 1048576 128
TEST_SHRINK 52 1048576 128
TEST_SHRINK 53 1048576 128
TEST_SHRINK 54 1048576 128
SEGMENT_NAME EXTENT_ID BYTES BLOCKS
--------------------------------------------------------------------------------- ---------- ---------- ----------
TEST_SHRINK 55 1048576 128
TEST_SHRINK 56 1048576 128
TEST_SHRINK 57 1048576 128
TEST_SHRINK 58 1048576 128
TEST_SHRINK 59 1048576 128
TEST_SHRINK 60 1048576 128
TEST_SHRINK 61 1048576 128
TEST_SHRINK 62 1048576 128
TEST_SHRINK 63 1048576 128
TEST_SHRINK 64 1048576 128
TEST_SHRINK 65 1048576 128
SEGMENT_NAME EXTENT_ID BYTES BLOCKS
--------------------------------------------------------------------------------- ---------- ---------- ----------
TEST_SHRINK 66 1048576 128
TEST_SHRINK 67 1048576 128
TEST_SHRINK 68 1048576 128
TEST_SHRINK 69 1048576 128
TEST_SHRINK 70 1048576 128
TEST_SHRINK 71 1048576 128
TEST_SHRINK 72 1048576 128
TEST_SHRINK 73 1048576 128
TEST_SHRINK 74 1048576 128
TEST_SHRINK 75 1048576 128
TEST_SHRINK 76 1048576 128
SEGMENT_NAME EXTENT_ID BYTES BLOCKS
--------------------------------------------------------------------------------- ---------- ---------- ----------
TEST_SHRINK 77 1048576 128
TEST_SHRINK 78 1048576 128
TEST_SHRINK 79 6225920 760
80 rows selected.
SQL> select 8*8*1024*1024 from dual;
8*8*1024*1024
-------------
67108864
SQL> select 8*8*1024 from dual;
8*8*1024
----------
65536
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/9240380/viewspace-672037/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- oracle10g_alter table shrink space_compact_cascade回收空間測試(一)Oracle
- oracle 10g__alter table shrink space compactOracle 10g
- alter table move 和 alter table shrink space的區別
- ALTER TABLE MOVE | SHRINK SPACE區別
- alter table move 與shrink space的區別
- alter table move跟shrink space的區別
- ALTER TABLE MOVE和SHRINK SPACE區別
- alter table move跟shrink space的區別（轉）
- 測試alter table storage及dbms_space_admin包
- Oracle 11g alter table move與shrink spaceOracle
- [Oracle] Shrink space & Table move比較Oracle
- Oracle 10g Shrink Table - Shrink Space 收縮空間Oracle 10g
- Oracle OCP 1Z0 053 Q96(Shrink Space Compact)Oracle
- 主子表drop table constraints cascade的測試AI
- Oracle中shrink space命令詳解[轉]－－還示測試Oracle
- [重慶思莊每日技術分享]-在為表新增了列後執行ALTER TABLE SHRINK SPACE 提示ORA-8102
- oracle10g_alter table_測試3Oracle
- 學習 java 做自動化測試相關Java
- Oracle SCN相關問題學習與測試Oracle
- Oracle IZ0-053 Q277(Table shrink space)Oracle
- 【DG】搭建（二）及相關測試
- How to adjust the high watermark in ORACLE 10g – ALTER TABLE SHRINKOracle 10g
- shrink space的最佳實踐
- oracle shrink tableOracle
- 【LC】locale設定查詢及相關環境變數含義及示例變數
- alter database backup controlfile to trace的含義Database
- Java異常及相關呼叫效能測試Java
- Linux之相關英文縮寫含義Linux
- 自定義View相關學習(一) (SlantedTextView ，canvas)TextViewCanvas
- MySQL-ALTER TABLE命令學習[20180503]MySql
- oracle10g_impdp工具測試學習_之三_remap相關OracleREM
- iOS 應用效能測試的相關方法、工具及技巧iOS
- Explain各列的含義AI
- Oracle中shrink space命令詳解Oracle
- oracle11g同義詞synonym建立及授權且刪除相關測試Oracle
- SRE的含義及與 DevOps 如何關聯dev
- (原)發動機油指標及相關測試指標
- mysql裡alter table 重定義主鍵的步驟：MySql