--====================
-- 收縮表段(shrink space)
--====================

一、表的增長(zhǎng)方式
?? ?當(dāng)表被創(chuàng)建后，隨著記錄的不斷插入，組成表的區(qū)間會(huì)被填滿，如果啟用了自動(dòng)擴(kuò)展，則當(dāng)區(qū)間填滿后，會(huì)分配新的區(qū)間。假定高水
?? ?位線隨著記錄的增加從最左端往右端來(lái)移動(dòng)，當(dāng)?shù)降撞繀^(qū)間的尾端時(shí)，則新的區(qū)間將會(huì)被分配。
?? ?
二、表可收縮的原理
?? ?隨著記錄的增加高水位線不斷的右移，記錄的刪除不會(huì)導(dǎo)致高水位線往回(左)移動(dòng)
?? ?刪除記錄后的空閑空間(高水位線左側(cè))盡管可以使用，但其稀疏性導(dǎo)致空間空閑
?? ?注：完整的表掃描所耗費(fèi)的時(shí)間不會(huì)因?yàn)橛涗浀臏p少(刪除)而減少

三、使用 alter table tbname shrink space 來(lái)收縮表段
?? ? 1. 實(shí)現(xiàn)原理
?? ??? ?實(shí)質(zhì)上構(gòu)造一個(gè)新表(在內(nèi)部表現(xiàn)為一系列的DML操作,即將副本插入新位置，刪除原來(lái)位置的記錄)
?? ??? ?靠近末尾處(右端)數(shù)據(jù)塊中的記錄往開(kāi)始處(左端)的空閑空間處移動(dòng)(DML操作)，不會(huì)引起DML觸發(fā)器
?? ??? ?當(dāng)所有可能的移動(dòng)被完成，高水位線將會(huì)往左端移動(dòng)(DDL操作)
?? ??? ?新的高水位線右邊的空閑空間被釋放(DDL操作)
?? ??? ?
?? ? 2. 實(shí)現(xiàn)前提條件
?? ??? ?必須啟用行記錄轉(zhuǎn)移(enable row movement)
?? ??? ?僅僅適用于堆表,且位于自動(dòng)段空間管理的表空間(堆表包括:標(biāo)準(zhǔn)表,分區(qū)表,物化視圖容器,物化視圖日志表)
?? ?
?? ? 3. 不能實(shí)現(xiàn)收縮的表
?? ??? ?群集表
?? ??? ?具有LONG類型列的表
?? ??? ?LOB段(盡管表本身可以被縮小)，注，10gR2以后版本支持對(duì)LOB段的收縮
?? ??? ?具有基于提交的物化視圖的表(因?yàn)榻昧擞|發(fā)器)
?? ??? ?具有rowid物化視圖的表(因?yàn)閞owid發(fā)生了變化)
?? ??? ?IOT映射表IOT溢出段
?? ??? ?索引基于函數(shù)的表
?? ??? ?未啟用行記錄轉(zhuǎn)移的堆表
?? ??? ?
?? ? 4. 段收縮的優(yōu)點(diǎn)
?? ??? ?提高緩存利用率，提高OLTP的性能
??? ??? ?減少磁盤I/O，提高訪問(wèn)速度，節(jié)省磁盤空間
??? ??? ?段收縮是在線的，索引在段收縮期間維護(hù)，不要求額外的磁盤空間
??? ??? ?
?? ? 5. 兩個(gè)選項(xiàng)
?? ??? ?cascade:縮小表及其索引，并移動(dòng)高水位線，釋放空間
?? ??? ?compact:僅僅是縮小表和索引，并不移動(dòng)高水位線，不釋放空間
?? ??? ?alter table tbname shrink space 相當(dāng)于帶cascade參數(shù)

四、實(shí)戰(zhàn)演習(xí)

1. 查看需要收縮的表段的基本情況，此處為表big_table ???

SQL> col SEGMENT_NAME format a20 SQL> select OWNER,SEGMENT_NAME,BYTES/1024/1024,BLOCKS from dba_segments 2 where owner='SCOTT' and SEGMENT_NAME='BIG_TABLE'; OWNER SEGMENT_NAME BYTES/1024/1024 BLOCKS ------------------------------ -------------------- --------------- ---------- SCOTT BIG_TABLE 120 15360 SQL> select table_name,blocks,empty_blocks from dba_tables 2 where table_name='big_table' and owner='scott'; -->未更新統(tǒng)計(jì)信息前BLOCKS，EMPTY_BLOCKS列為空 TABLE_NAME BLOCKS EMPTY_BLOCKS ------------------------------ ---------- ------------ BIG_TABLE SQL> exec dbms_stats.gather_table_stats(ownname=>'SCOTT',tabname=>'BIG_TABLE',estimate_percent=>30); PL/SQL procedure successfully completed. SQL> select TABLE_NAME,BLOCKS,EMPTY_BLOCKS from dba_tables 2 where TABLE_NAME='BIG_TABLE' and owner='SCOTT'; TABLE_NAME BLOCKS EMPTY_BLOCKS ------------------------------ ---------- ------------ BIG_TABLE 14590 0 -->使用gather_table_stats時(shí)不會(huì)統(tǒng)計(jì)EMPTY_BLOCKS塊 SQL> analyze table big_table compute statistics; -->使用analyze更新統(tǒng)計(jì)信息后EMPTY_BLOCKS得到數(shù)據(jù) Table analyzed. SQL> select TABLE_NAME,BLOCKS,EMPTY_BLOCKS from dba_tables 2 where TABLE_NAME='BIG_TABLE' and owner='SCOTT'; TABLE_NAME BLOCKS EMPTY_BLOCKS ------------------------------ ---------- ------------ BIG_TABLE 14590 770 SQL> set serveroutput on; SQL> exec show_space('BIG_TABLE','SCOTT'); -->使用show_space過(guò)程或的BIG_TABLE上的空間分配信息 Unformatted Blocks ..................... 0 FS1 Blocks (0-25) ...................... 0 -->空閑度為0-25%的塊數(shù)。FS1,FS2,FS3,FS4為空閑度所占的百分比 FS2 Blocks (25-50) ..................... 0 FS3 Blocks (50-75) ..................... 0 FS4 Blocks (75-100)..................... 0 Full Blocks ............................ 14,427 Total Blocks............................ 15,360 Total Bytes............................. 125,829,120 Total MBytes............................ 120 Unused Blocks........................... 770 Unused Bytes............................ 6,307,840 Last Used Ext FileId.................... 4 Last Used Ext BlockId................... 16,521 Last Used Block......................... 254 PL/SQL procedure successfully completed.

2. 刪除記錄之后，進(jìn)行收縮表段 ???

SQL> delete from big_table where owner in ('SCOTT','SYSTEM'); -->刪除記錄 8715 rows deleted. SQL> commit; Commit complete. SQL> alter table big_table shrink space; -->實(shí)施shrink,提示沒(méi)有啟用ROW MOVEMENT alter table big_table shrink space * ERROR at line 1: ORA-10636: ROW MOVEMENT is not enabled SQL> alter table big_table enable row movement; -->開(kāi)啟row movement Table altered. SQL> alter table big_table shrink space; -->shrink成功 Table altered. SQL> exec show_space('BIG_TABLE','SCOTT'); -->從下面的結(jié)果中可以看到塊數(shù)以及總大小已經(jīng)變小 Unformatted Blocks ..................... 0 FS1 Blocks (0-25) ...................... 1 FS2 Blocks (25-50) ..................... 1 FS3 Blocks (50-75) ..................... 0 FS4 Blocks (75-100)..................... 0 Full Blocks ............................ 14,318 Total Blocks............................ 14,488 Total Bytes............................. 118,685,696 Total MBytes............................ 113 Unused Blocks........................... 5 Unused Bytes............................ 40,960 Last Used Ext FileId.................... 4 Last Used Ext BlockId................... 16,521 Last Used Block......................... 147 PL/SQL procedure successfully completed.

3. 驗(yàn)證cascade與compact的差異 ???

SQL> delete from big_table where rownum<8000; -->再次刪除一些記錄 7999 rows deleted. SQL> alter table big_table shrink space compact; -->使用compact方式收縮表段 Table altered. SQL> exec show_space('BIG_TABLE','SCOTT'); Unformatted Blocks ..................... 0 FS1 Blocks (0-25) ...................... 1 FS2 Blocks (25-50) ..................... 2 FS3 Blocks (50-75) ..................... 0 FS4 Blocks (75-100)..................... 103 Full Blocks ............................ 14,214 --僅有的變化為14318-14214=104塊，即完全填滿的數(shù)據(jù)塊減少了104塊 Total Blocks............................ 14,488 --數(shù)據(jù)的總塊數(shù)及總大小并沒(méi)有減少，即未移動(dòng)高水位線 Total Bytes............................. 118,685,696 Total MBytes............................ 113 Unused Blocks........................... 5 Unused Bytes............................ 40,960 Last Used Ext FileId.................... 4 Last Used Ext BlockId................... 16,521 Last Used Block......................... 147 PL/SQL procedure successfully completed. SQL> alter table big_table shrink space cascade; -->使用cascade方式收縮， Table altered. SQL> exec show_space('BIG_TABLE','SCOTT'); Unformatted Blocks ..................... 0 FS1 Blocks (0-25) ...................... 1 FS2 Blocks (25-50) ..................... 2 FS3 Blocks (50-75) ..................... 0 FS4 Blocks (75-100)..................... 0 Full Blocks ............................ 14,214 Total Blocks............................ 14,384 -->總塊數(shù)及總大小均已減少 Total Bytes............................. 117,833,728 Total MBytes............................ 112 Unused Blocks........................... 4 Unused Bytes............................ 32,768 Last Used Ext FileId.................... 4 Last Used Ext BlockId................... 16,521 Last Used Block......................... 44 PL/SQL procedure successfully completed. -->收縮之后索引依然有效 SQL> select OWNER,INDEX_NAME,STATUS from dba_indexes where TABLE_NAME='BIG_TABLE'; OWNER INDEX_NAME STATUS ------------------------------ ------------------------------ -------- SCOTT BIG_TABLE_PK VALID

五、語(yǔ)法總結(jié)： ?? ???

ALTER TABLE <table_name> ENABLE ROW MOVEMENT -->前提條件 ALTER TABLE <table_name> SHRINK SPACE [ <NULL> | COMPACT | CASCADE ]; ALTER TABLE <table_name> SHRINK SPACE COMPCAT; -->縮小表和索引，不移動(dòng)高水位線，不釋放空間 ALTER TABLE <table_name> SHRINK SPACE; -->收縮表，降低高水位線; ALTER TABLE <table_name> SHRINK SPACE CASCADE; -->收縮表，降低高水位線，并且相關(guān)索引也要收縮一下 ALTER TABLE <table_name> MODIFY LOB (lob_column) (SHRINK SPACE); -->收縮LOB段 ALTER INDEX IDXNAME SHRINK SPACE; -->索引段的收縮，同表段

六、批量收縮腳本
1. 普通表(根據(jù)相應(yīng)需求修改下面的語(yǔ)句生產(chǎn)相應(yīng)腳本)?? ?

select'alter table '||table_name||' enable row movement;' ||chr(10)||'alter table '||table_name||' shrink space;'||chr(10)from user_tables; select'alter index '||index_name||' shrink space;'||chr(10)from user_indexes;

2. 分區(qū)表的處理

?? ?分區(qū)表進(jìn)行shrink space時(shí)發(fā)生ORA-10631錯(cuò)誤.shrink space有一些限制.

?? ?在表上建有函數(shù)索引（包括全文索引）會(huì)失敗。???

--根據(jù)相應(yīng)需求修改下面的語(yǔ)句生產(chǎn)相應(yīng)腳本 select 'alter table '||owner||'.'||table_name||' enable row movement;' ||chr(10)||'alter table '||owner||'.'||table_name||' shrink space;'||chr(10) from dba_tables where owner=upper('&input_owner'); select 'alter index '||owner||'.'||index_name||' shrink space;' ||chr(10) from dba_indexes where uniqueness='NONUNIQUE' ; select 'alter table '||owner||'.'||segment_name||' modify partition '||partition_name||' shrink space;' ||chr(10) from dba_segments where segment_type='TABLE PARTITION';

? 3. 附show_space腳本(來(lái)自Tom大師) ???

CREATE OR REPLACE PROCEDURE show_space ( p_segname IN VARCHAR2, p_owner IN VARCHAR2 DEFAULT USER, p_type IN VARCHAR2 DEFAULT 'TABLE', p_partition IN VARCHAR2 DEFAULT NULL ) -- this procedure uses authid current user so it can query DBA_* -- views using privileges from a ROLE, and so it can be installed -- once per database, instead of once per user who wanted to use it AUTHID CURRENT_USER AS l_free_blks NUMBER; l_total_blocks NUMBER; l_total_bytes NUMBER; l_unused_blocks NUMBER; l_unused_bytes NUMBER; l_LastUsedExtFileId NUMBER; l_LastUsedExtBlockId NUMBER; l_LAST_USED_BLOCK NUMBER; l_segment_space_mgmt VARCHAR2(255); l_unformatted_blocks NUMBER; l_unformatted_bytes NUMBER; l_fs1_blocks NUMBER; l_fs1_bytes NUMBER; l_fs2_blocks NUMBER; l_fs2_bytes NUMBER; l_fs3_blocks NUMBER; l_fs3_bytes NUMBER; l_fs4_blocks NUMBER; l_fs4_bytes NUMBER; l_full_blocks NUMBER; l_full_bytes NUMBER; -- inline procedure to print out numbers nicely formatted -- with a simple label PROCEDURE p ( p_label IN VARCHAR2, p_num IN NUMBER ) IS BEGIN dbms_output.put_line(rpad(p_label, 40, '.') || to_char(p_num, '999,999,999,999')); END; BEGIN -- this query is executed dynamically in order to allow this procedure -- to be created by a user who has access to DBA_SEGMENTS/TABLESPACES -- via a role as is customary. -- NOTE: at runtime, the invoker MUST have access to these two -- views! -- this query determines if the object is an ASSM object or not BEGIN EXECUTE IMMEDIATE 'select ts.segment_space_management from dba_segments seg, dba_tablespaces ts where seg.segment_name = :p_segname and (:p_partition is null or seg.partition_name = :p_partition) and seg.owner = :p_owner and seg.tablespace_name = ts.tablespace_name' INTO l_segment_space_mgmt USING p_segname, p_partition, p_partition, p_owner; EXCEPTION WHEN too_many_rows THEN dbms_output.put_line('This must be a partitioned table, use p_partition => '); RETURN; END; -- if the object is in an ASSM tablespace, we must use this API -- call to get space information, otherwise we use the FREE_BLOCKS -- API for the user-managed segments IF l_segment_space_mgmt = 'AUTO' THEN dbms_space.space_usage(p_owner, p_segname, p_type, l_unformatted_blocks, l_unformatted_bytes, l_fs1_blocks, l_fs1_bytes, l_fs2_blocks, l_fs2_bytes, l_fs3_blocks, l_fs3_bytes, l_fs4_blocks, l_fs4_bytes, l_full_blocks, l_full_bytes, p_partition); p('Unformatted Blocks ', l_unformatted_blocks); p('FS1 Blocks (0-25) ', l_fs1_blocks); p('FS2 Blocks (25-50) ', l_fs2_blocks); p('FS3 Blocks (50-75) ', l_fs3_blocks); p('FS4 Blocks (75-100)', l_fs4_blocks); p('Full Blocks ', l_full_blocks); ELSE dbms_space.free_blocks(segment_owner => p_owner, segment_name => p_segname, segment_type => p_type, freelist_group_id => 0, free_blks => l_free_blks); p('Free Blocks', l_free_blks); END IF; -- and then the unused space API call to get the rest of the -- information dbms_space.unused_space(segment_owner => p_owner, segment_name => p_segname, segment_type => p_type, partition_name => p_partition, total_blocks => l_total_blocks, total_bytes => l_total_bytes, unused_blocks => l_unused_blocks, unused_bytes => l_unused_bytes, LAST_USED_EXTENT_FILE_ID => l_LastUsedExtFileId, LAST_USED_EXTENT_BLOCK_ID => l_LastUsedExtBlockId, LAST_USED_BLOCK => l_LAST_USED_BLOCK); p('Total Blocks', l_total_blocks); p('Total Bytes', l_total_bytes); p('Total MBytes', trunc(l_total_bytes / 1024 / 1024)); p('Unused Blocks', l_unused_blocks); p('Unused Bytes', l_unused_bytes); p('Last Used Ext FileId', l_LastUsedExtFileId); p('Last Used Ext BlockId', l_LastUsedExtBlockId); p('Last Used Block', l_LAST_USED_BLOCK); END;

七、快捷參考

有關(guān)性能優(yōu)化請(qǐng)參考

??? Oracle硬解析與軟解析

??? 共享池的調(diào)整與優(yōu)化(Sharedpool Tuning)

??? Buffercache 的調(diào)整與優(yōu)化(一)

??? Oracle表緩存(cachingtable)的使用

?

有關(guān)ORACLE體系結(jié)構(gòu)請(qǐng)參考

??? Oracle表空間與數(shù)據(jù)文件

??? Oracle密碼文件

??? Oracle參數(shù)文件

??? Oracle聯(lián)機(jī)重做日志文件(ONLINE LOG FILE)

??? Oracle控制文件(CONTROLFILE)

? ?? Oracle歸檔日志

??? Oracle回滾(ROLLBACK)和撤銷(UNDO)

??? Oracle數(shù)據(jù)庫(kù)實(shí)例啟動(dòng)關(guān)閉過(guò)程

??? Oracle10g SGA 的自動(dòng)化管理

??? Oracle實(shí)例和Oracle數(shù)據(jù)庫(kù)(Oracle體系結(jié)構(gòu))

?

有關(guān)閃回特性請(qǐng)參考

??? Oracle閃回特性(FLASHBACK DATABASE)

??? Oracle閃回特性(FLASHBACK DROP & RECYCLEBIN)

??? Oracle閃回特性(Flashback Query、FlashbackTable)

??? Oracle閃回特性(Flashback Version、Flashback Transaction)

?

有關(guān)基于用戶管理的備份和備份恢復(fù)的概念請(qǐng)參考

??? Oracle冷備份

??? Oracle熱備份

??? Oracle備份恢復(fù)概念

??? Oracle實(shí)例恢復(fù)

??? Oracle基于用戶管理恢復(fù)的處理 (詳細(xì)描述了介質(zhì)恢復(fù)及其處理)

??? SYSTEM表空間管理及備份恢復(fù)

??? SYSAUX表空間管理及恢復(fù)

?

有關(guān)RMAN的備份恢復(fù)與管理請(qǐng)參考

??? RMAN 概述及其體系結(jié)構(gòu)

??? RMAN 配置、監(jiān)控與管理

??? RMAN 備份詳解

??? RMAN 還原與恢復(fù)

??? RMANcatalog 的創(chuàng)建和使用

??? 基于catalog 創(chuàng)建RMAN存儲(chǔ)腳本

基于catalog 的RMAN 備份與恢復(fù)

使用RMAN遷移文件系統(tǒng)數(shù)據(jù)庫(kù)到ASM

??? RMAN 備份路徑困惑(使用plus archivelog時(shí))

?

有關(guān)ORACLE故障請(qǐng)參考

??? ORA-32004的錯(cuò)誤處理

??? ORA-01658錯(cuò)誤

??? CRS-0215錯(cuò)誤處理

??? ORA-00119，ORA-00132 錯(cuò)誤處理

??? 又一例SPFILE設(shè)置錯(cuò)誤導(dǎo)致數(shù)據(jù)庫(kù)無(wú)法啟動(dòng)

??? 對(duì)參數(shù)FAST_START_MTTR_TARGET= 0 的誤解及設(shè)定

??? SPFILE錯(cuò)誤導(dǎo)致數(shù)據(jù)庫(kù)無(wú)法啟動(dòng)(ORA-01565)

?

有關(guān)ASM請(qǐng)參考

??? 創(chuàng)建ASM實(shí)例及ASM數(shù)據(jù)庫(kù)

??? ASM 磁盤、目錄的管理

??? 使用 ASMCMD 工具管理ASM目錄及文件

?

有關(guān)SQL/PLSQL請(qǐng)參考

??? SQLPlus常用命令

??? 替代變量與SQL*Plus環(huán)境設(shè)置

??? 使用Uniread實(shí)現(xiàn)SQLplus翻頁(yè)功能

??? SQL 基礎(chǔ) -->SELECT 查詢

??? SQL 基礎(chǔ) --> NEW_VALUE的使用

??? SQL 基礎(chǔ) --> 集合運(yùn)算(UNION與UNION ALL)

??? SQL 基礎(chǔ) --> 常用函數(shù)

??? SQL 基礎(chǔ) --> 視圖(CREATEVIEW)

??? SQL 基礎(chǔ) --> 創(chuàng)建和管理表

??? SQL 基礎(chǔ) --> 多表查詢

??? SQL 基礎(chǔ) --> 過(guò)濾和排序

??? SQL 基礎(chǔ) --> 子查詢

??? SQL 基礎(chǔ) --> 分組與分組函數(shù)

??? SQL 基礎(chǔ) --> 層次化查詢(STARTBY ... CONNECT BY PRIOR)

??? SQL 基礎(chǔ) --> ROLLUP與CUBE運(yùn)算符實(shí)現(xiàn)數(shù)據(jù)匯總

??? PL/SQL --> 游標(biāo)

??? PL/SQL --> 異常處理(Exception)

??? PL/SQL --> 語(yǔ)言基礎(chǔ)

??? PL/SQL --> 流程控制

??? PL/SQL --> PL/SQL記錄

??? PL/SQL --> 包的創(chuàng)建與管理

??? PL/SQL --> 隱式游標(biāo)(SQL%FOUND)

??? PL/SQL --> 包重載、初始化

??? PL/SQL --> DBMS_DDL包的使用

??? PL/SQL --> DML 觸發(fā)器

??? PL/SQL --> INSTEAD OF 觸發(fā)器

??? PL/SQL --> 存儲(chǔ)過(guò)程

??? PL/SQL --> 函數(shù)

??? PL/SQL --> 動(dòng)態(tài)SQL

??? PL/SQL --> 動(dòng)態(tài)SQL的常見(jiàn)錯(cuò)誤

?

有關(guān)ORACLE其它特性

??? Oracle常用目錄結(jié)構(gòu)(10g)

??? 使用OEM,SQL*Plus,iSQL*Plus 管理Oracle實(shí)例

??? 日志記錄模式(LOGGING、FORCE LOGGING 、NOLOGGING)

??? 表段、索引段上的LOGGING與NOLOGGING

??? OralceOMF 功能詳解

??? Oracle用戶、對(duì)象權(quán)限、系統(tǒng)權(quán)限 ?

??? Oracle角色、配置文件

??? Oracle分區(qū)表

??? Oracle外部表

??? 使用外部表管理Oracle 告警日志(ALAERT_$SID.LOG)

??? 簇表及簇表管理(Index clustered tables)

??? 數(shù)據(jù)泵 EXPDP 導(dǎo)出工具的使用

??? 數(shù)據(jù)泵 IMPDP 導(dǎo)入工具的使用

??? 導(dǎo)入導(dǎo)出 Oracle 分區(qū)表數(shù)據(jù)

??? SQL*Loader使用方法

??? 啟用用戶進(jìn)程跟蹤

??? 配置非默認(rèn)端口的動(dòng)態(tài)服務(wù)注冊(cè)

??? 配置ORACLE 客戶端連接到數(shù)據(jù)庫(kù)

??? systemsys，sysoper sysdba 的區(qū)別

??? ORACLE_SID、DB_NAME、INSTANCE_NAME、DB_DOMIAN、GLOBAL_NAME

??? Oracle補(bǔ)丁全集 (Oracle 9i 10g 11g Path)

??? Oracle10.2.0.1 升級(jí)到10.2.0.4

??? Oracle徹底 kill session

?? ??? ? ?

收縮表段(shrink space)