Oracle資料檔案收縮例項

　　檢視資料檔案的使用情況

　　包括內容：資料檔案大小，已經used空間，free空間，hwm資訊

　　select /*+ ordered use_hash(a,b,c) */

　　a.file_id,a.file_name,a.filesize, b.freesize,

　　(a.filesize-b.freesize) usedsize,

　　c.hwmsize,

　　c.hwmsize - (a.filesize-b.freesize) unsedsize_belowhwm,

　　a.filesize - c.hwmsize canshrinksize

　　from

　　(

　　select file_id,file_name,round(bytes/1024/1024) filesize from dba_data_files

　　) a,

　　(

　　select file_id,round(sum(dfs.bytes)/1024/1024) freesize from dba_free_space dfs

　　group by file_id

　　) b,

　　(

　　select file_id,round(max(block_id)*8/1024) HWMsize from dba_extents

　　group by file_id) c

　　where a.file_id = b.file_id

　　and a.file_id = c.file_id

　　order by unsedsize_belowhwm desc

　　結果說明：

　　File_id ： 檔案編號

　　File_name: 檔名稱

　　File_size: 資料檔案佔用磁碟空間大小

　　Freesize：檔案中被標記為free的空間大小

　　Usedsize: 使用的空間大小。

　　Hwmsize： 已經分配出去的空間大小，如果希望通過alter database datafile … resize integerM回收空間，將需要這個值作為參考，不能回收到這個值之下，否則會報錯。

　　Freee_belowhwm_size： 在HWM(高水位標記線之下的空閒空間數)，這個是理論上的可以回收的空間大小。

　　Curr_can_shrink： 這個是實際大小與HWM標記之間的差，就是還沒有分配出去的空間大小。

　　file_idfile_namefilesizefreesizeusedsizehwmsizefree_belowhwm_sizecurr_can_shrink

　　11/oradata/ODSD01.dbf20481908140204819080

　　12/oradata/ODSD02.dbf20481897151204818970

　　20/oradata/ODSD10.dbf20481897151204818970

　　16/oradata/ODSD06.dbf20481889159204818890

　　15/oradata/ODSD05.dbf20481888160204818880

　　19/oradata/ODSD09.dbf20481885163204818850

　　13/oradata/ODSD03.dbf20481884164204818840

　　17/oradata/ODSD07.dbf20481884164204818840

　　14/oradata/ODSD04.dbf20481813235204118067

　　34/oradata/DWD01.dbf40003701299208817891912

　　51/oradata/ODSD11.dbf204819638515841499464

　　21/oradata/ODSI01.dbf2048191313516171482431

　　25/oradata/ODSI05.dbf2048191013816071469441

　　22/oradata/ODSI02.dbf2048190314516061461442

　　24/oradata/ODSI04.dbf2048190913915921453456

　　23/oradata/ODSI03.dbf2048189215616031447445

　　48/oradata/ODSI06.dbf2048192512315591436489

　　30/oradata/TODSD05.dbf2048180424413151071733

　　18/oradata/ODSD08.dbf2048188116712251058823

　　27/oradata/TODSD02.dbf2048181823012441014804

　　31/oradata/TODSI01.dbf20481977719368651112

　　35/oradata/DWI01.dbf20481973759368611112

　　32/oradata/TODSI02.dbf20481969798677881181

　　43/oradata/DWI03.dbf20481975738027291246

　　42/oradata/DWI02.dbf20481983657556901293

　　39/oradata/TODSI04.dbf20481971776806031368

　　26/oradata/TODSD01.dbf204818192298306011218

　　40/oradata/TODSI05.dbf20481976726095371439

　　28/oradata/TODSD03.dbf204817932557024471346

　　37/oradata/TODSI03.dbf204819461024503481598

　　29/oradata/TODSD04.dbf204817932554852301563

　　33/oradata/CTL01.dbf50049462115479

　　10/oradata/xdb01.dbf473444621

　　1/oradata/system01.dbf104061034103406

　　3/oradata/cwmlite01.dbf202181802

　　4/oradata/drsys01.dbf20101010010

　　36/oradata/OD01.dbf50040793930407

　　5/oradata/example01.dbf139013913900

　　54/oradata/TCLKING.dbf505500

　　56/oradata/undotbs03.dbf1000996440996

　　55/oradata/HWM01.dbf50004963373704963

　　49/oradata/DP23.dbf1073307

　　7/oradata/odm01.dbf201199011

　　9/oradata/users01.dbf8308382-11

　　46/oradata/RPTI01.dbf1024802222221-1803

　　45/oradata/RPTD01.dbf1024923101100-1924

　　38/oradata/FBI.dbf20079121120-180

　　對想收縮的表空間中的表及索引進行rebuild

　　建立測試表空間

　　SQL> create tablespace HWM datafile ‘/oradata/HWM01.dbf’ size 5000M;

　　Tablespace created;

　　SQL> alter tablespace HWM add datafile '/oradata/HWM02.dbf' size 5000M;

　　Tablespace altered

　　move表空間的long型別

　　LONG型別的資料超難管理，不能通過move來傳輸，也不能通過諸如insert t1 select long_col from t2的方式(或者使用遊標可以解決這個問題)請注意在設計中儘量避免使用LONG型別。

　　檢查當前表空間中的LONG型別欄位。

　　select /*+use_hash(ds,dtc)*/

　　ds.tablespace_name,ds.owner||'.'||ds.segment_name,ds.segment_type,

　　dtc.DATA_TYPE,dtc.COLUMN_NAME

　　from dba_tab_columns dtc , dba_segments ds

　　where dtc.TABLE_NAME = ds.segment_name

　　and dtc.OWNER = ds.owner

　　and ds.tablespace_name not in ('SYSTEM','CWMLITE','EXAMPLE','UNDOTBS2','HWM')

　　and data_type = 'LONG'

　　tablespacesegmentnamesegtypedatatypecolname

　　CTLCTL.ETL_LOGTABLELONGEXECUTE_SQL

　　CTLCTL.PLAN_TABLETABLELONGOTHER

　　DWDDW.PLAN_TABLETABLELONGOTHER

　　CTLOD.PLAN_TABLETABLELONGOTHER

　　FBIFBI.PLAN_TABLETABLELONGOTHER

　　對long型別的資料處理的一個簡單的方法實將LONG型別欄位直接修改為LOB型別。

　　select /*+use_hash(ds,dtc)*/

　　'alter table '||ds.owner||'.'||ds.segment_name||' modify '||dtc.COLUMN_NAME||' clob;'

　　from dba_tab_columns dtc , dba_segments ds

　　where dtc.TABLE_NAME = ds.segment_name

　　and dtc.OWNER = ds.owner

　　and ds.tablespace_name not in ('SYSTEM','CWMLITE','EXAMPLE','UNDOTBS2','HWM')

　　and data_type = 'LONG'

　　修改型別語句

　　alter table CTL.ETL_LOG modify EXECUTE_SQL clob;

　　alter table CTL.PLAN_TABLE modify OTHER clob;

　　alter table DW.PLAN_TABLE modify OTHER clob;

　　alter table OD.PLAN_TABLE modify OTHER clob;

　　alter table FBI.PLAN_TABLE modify OTHER clob;

　　SQL> alter table CTL.ETL_LOG modify EXECUTE_SQL clob;

　　Table altered

　　SQL> alter table CTL.PLAN_TABLE modify OTHER clob;

　　Table altered

　　SQL> alter table DW.PLAN_TABLE modify OTHER clob;

　　Table altered

　　SQL> alter table OD.PLAN_TABLE modify OTHER clob;

　　Table altered

　　SQL> alter table FBI.PLAN_TABLE modify OTHER clob;

　　Table altered

　　move表空間下的普通table及index

　　SQL> alter table tbname move tablespace newtbname;

　　Move一個表到另外一個表空間時，索引不會跟著一塊move，而且會失效。在建立失效的索引之前，使用到索引的查詢語句將會報錯。失效的索引需要使用rebuild重建立。

　　Alter index index_name rebuild;

　　Alter index pk_name rebuild;

　　如果我們需要move索引到另外一個表空間，則需要使用rebuild

　　Alter index index_name rebuild tablespace tbs_name;

　　Alter index pk_name rebuild tablespace tbs_name;

　　select ds.tablespace_name,'alter table '||ds.owner||'.'||ds.segment_name||' move tablespace HWM;'

　　from dba_segments ds

　　where ds.tablespace_name not in('SYSTEM','CWMLITE','EXAMPLE','UNDOTBS2',

　　'HWM','XDB','WKSYS','CTXSYS','ODM_MTR','USERS','DRSYS','HTEC','HAPPYTREE')

　　and ds.segment_type = 'TABLE';

　　SQL> alter table ODS.SM_PRODUCT_SPEC_SHOW move tablespace HWM;

　　Table altered

　　SQL> alter table DW.D_PRODUCT_INFO move tablespace HWM;

　　Table altered

　　select ds.tablespace_name,'alter INDEX '||ds.owner||'.'||ds.segment_name||' rebuild tablespace HWM;'

　　from dba_segments ds

　　where ds.tablespace_name not in('SYSTEM','CWMLITE','EXAMPLE','UNDOTBS2',

　　'HWM','XDB','WKSYS','CTXSYS','ODM_MTR','USERS','DRSYS','HTEC','HAPPYTREE')

　　and ds.segment_type = 'INDEX'

　　…

　　SQL> alter INDEX CTL.IDX_TL_ADJUSTMENT_CONFIRMDATE rebuild tablespace HWM;

　　Index altered

　　SQL> alter INDEX CTL.IDX_TL_ADJUSTMENT_ORDER rebuild tablespace HWM;

　　Index altered

　　…

　　move表空間下的分割槽table及index

　　和普通表一樣，索引也會失效，區別的僅僅是語法而已。

　　分割槽表move基本語法

　　如果是單級分割槽，則使用關鍵字partition，如果是多級分割槽，則使用subpartition替代partition。如果分割槽或分割槽索引比較大，可以使用並行move或rebuild，parallel(degree 2)。

　　重建全域性索引

　　Alter index global_index rebuild;

　　或

　　Alter index global_index rebuild tablespace tbs_name;

　　重建區域性索引

　　Alter table tab_name modify partition partition_name rebuild unusable local indexes;

　　或

　　Alter index local_index_name rebuild partition partition_name tablespace tbs_name;

　　Move分割槽表

　　select cname

　　from (

　　select rownum rm,'alter table '||ds.owner||'.'||ds.segment_name||' move partition '||ds.partition_name||' tablespace HWM;' cname

　　from dba_segments ds

　　where ds.tablespace_name not in('SYSTEM','CWMLITE','EXAMPLE','UNDOTBS2',

　　'HWM','XDB','WKSYS','CTXSYS','ODM_MTR','USERS','DRSYS','HTEC','HAPPYTREE')

　　and ds.segment_type = 'TABLE PARTITION'

　　) c

　　where rm between 1 and 100;

　　迴圈執行上述語句，直到選不出結果。

　　SQL> alter table ODS.CR_PS_INVENTORY_ITEM move partition CR_PS_INVENTORY_ITEM_P070603 tablespace HWM;

　　Table altered

　　SQL> alter table ODS.CR_PS_INVENTORY_ITEM move partition CR_PS_INVENTORY_ITEM_P070604 tablespace HWM;

　　Table altered

　　重建全域性索引

　　Oracle的全域性索引也儲存在dba_segments中，並以index標誌，而且其重建方式跟普通索引一致，所以在執行忘回匯入的時候需要按照move 普通表;move分割槽表;move全域性索引;move分割槽索引;move lob物件的順序進行。

　　重建分割槽索引

　　檢視dba_part_indexes儲存分割槽表的本地索引，查詢發現當前系統中不存在本地索引，可以忽略。

　　select * from dba_part_indexes t where t.owner not in ('SYSTEM','SH')

　　move表空間下的LOB型別

　　在建立含有Lob字典的表時，oracle會自動為Lob欄位建立兩個單獨的segment，一個用來存放資料(segment_type=LOBSEGMENT)，另一個用來存放索引(segment_type=LOBINDEX)。預設他們會儲存在和表一起的表空間。

　　我們對錶move時，LOB型別欄位和該欄位索引不會跟著move，必須使用單據的語句來執行該欄位的move，語法如下：

　　Alter table t321 move tablespace HWM;

　　Later table t321 move lob(en) store as (tablespace HWM);

　　select 'alter table '||dtc.owner||'.'||dtc.TABLE_NAME||' move lob('||dtc.COLUMN_NAME||') store as(tablespace HWM);'

　　from dba_tab_columns dtc

　　where dtc.OWNER in('CTL','DW','RPT','OD','ODS','TODS','FBI','DP22','DP23','TCLKING')

　　and dtc.DATA_TYPE like '%LOB'

　　SQL> alter table DP22.D_KPI move lob(KPIFORM) store as(tablespace HWM);

　　Table altered

　　SQL> alter table DP22.D_KPI move lob(KPIFORMDSPN) store as(tablespace HWM);

　　Table altered

　　執行完上述操作步驟後，我們檢查tablespace的空間使用情況可以發現，所有相關資料檔案的hwm都已經變為0，也就是說所有的空間都已經變為未分配狀態。但這時如果我們將資料檔案dump出去，會發現原來的資料還在，只不過在資料字典中將其標識為未分配。

　　Move物件的逆順序

　　普通表物件

　　將普通表物件和分割槽表物件按照其owner的不同從HWM臨時表空間move到其預設的表空間中區。

　　select ds.tablespace_name,'alter table '||ds.owner||'.'||ds.segment_name||' move tablespace '||du.default_tablespace||';'

　　from dba_segments ds , dba_users du

　　where ds.owner = du.username

　　and ds.owner in ('CTL','DW','RPT','OD','ODS','TODS','FBI','DP22','DP23','TCLKING')

　　and ds.tablespace_name = 'HWM'

　　and ds.segment_type = 'TABLE';

　　SQL> alter table TODS.CR_PARTY_RELATIONSHIP move tablespace TODSD;

　　Table altered

　　SQL> alter table TODS.CR_PARTY_RELATIONSHIP_TYPE move tablespace TODSD;

　　Table altered

　　分割槽表物件

　　select cname

　　from (

　　select rownum rm,'alter table '||ds.owner||'.'||ds.segment_name||' move partition '||ds.partition_name||' tablespace '||du.default_tablespace||';' cname

　　from dba_segments ds , dba_users du

　　where ds.owner = du.username

　　and ds.owner in ('CTL','DW','RPT','OD','ODS','TODS','FBI','DP22','DP23','TCLKING')

　　and ds.tablespace_name = 'HWM'

　　and ds.segment_type = 'TABLE PARTITION'

　　) c

　　where rm between 1 and 500;

　　反覆執行上述過程，直到沒有記錄可以選擇。

　　SQL> alter table ODS.CR_PS_INVENTORY_ITEM move partition CR_PS_INVENTORY_ITEM_P080513 tablespace ODSD;

　　Table altered

　　SQL> alter table ODS.CR_PS_INVENTORY_ITEM move partition CR_PS_INVENTORY_ITEM_P080514 tablespace ODSD;

　　Table altered

　　索引物件

　　索引物件儲存的tablespace的命令標準為username+’I’，如果類似的表空間不存在，我們就將索引資料儲存到使用者的預設表空間中。所以我們可以使用下面的語句將index rebuild到對應的表空間中。

　　select 'alter INDEX '||ds.owner||'.'||ds.segment_name||' rebuild tablespace '||nvl(dt.tablespace_name,du.default_tablespace)||';'

　　from dba_segments ds , dba_users du, dba_tablespaces dt

　　where ds.owner = du.username

　　and dt.tablespace_name(+) = du.username||'I'

　　and ds.owner in ('CTL','DW','RPT','OD','ODS','TODS','FBI','DP22','DP23','TCLKING')

　　and ds.tablespace_name = 'HWM'

　　and ds.segment_type = 'INDEX'

　　LOB型別

　　Lob型別資料隨著table物件儲存在物件owner的預設表空間中。

　　select 'alter table '||dtc.owner||'.'||dtc.TABLE_NAME||' move lob('||dtc.COLUMN_NAME||') store as(tablespace '||du.default_tablespace||');'

　　from dba_tab_columns dtc,dba_users du

　　where dtc.OWNER = du.username

　　and dtc.OWNER in('CTL','DW','RPT','OD','ODS','TODS','FBI','DP22','DP23','TCLKING')

　　and dtc.DATA_TYPE like '%LOB'

　　SQL> alter table FBI.TIME_FORMAT move lob(FORMAT) store as(tablespace FBI);

　　Table altered

　　SQL> alter table FBI.URLTABLE move lob(DETAIL) store as(tablespace FBI);

　　Table altered

　　SQL> alter table OD.PLAN_TABLE move lob(OTHER) store as(tablespace OD);

　　Table altered

　　收縮空閒表空間

　　首先，如果沒有分配的空間不足100M，則不考慮收縮。

　　收縮目標：當前資料檔案大小 - (沒分配空間- 100M)×0.8

　　select /*+ ordered use_hash(a,c) */

　　'alter database datafile '''||a.file_name||''' resize '

　　||round(a.filesize - (a.filesize - c.hwmsize-100) *0.8)||'M;',

　　a.filesize,

　　c.hwmsize

　　from

　　(

　　select file_id,file_name,round(bytes/1024/1024) filesize from dba_data_files

　　) a,

　　(

　　select file_id,round(max(block_id)*8/1024) HWMsize from dba_extents

　　group by file_id) c

　　where a.file_id = c.file_id

　　and a.filesize - c.hwmsize > 100

　　收縮語句檔案大小收縮目標

　　alter database datafile '/oradata/HWM02.dbf' resize 2671M;50001989

　　alter database datafile '/oradata/ODSD01.dbf' resize 598M;2048136

　　alter database datafile '/oradata/ODSD02.dbf' resize 592M;2048128

　　alter database datafile '/oradata/ODSD03.dbf' resize 591M;2048127

　　alter database datafile '/oradata/ODSD04.dbf' resize 742M;2048316

　　alter database datafile '/oradata/ODSD05.dbf' resize 594M;2048130

　　alter database datafile '/oradata/ODSD06.dbf' resize 597M;2048134

　　alter database datafile '/oradata/ODSD07.dbf' resize 598M;2048135

　　alter database datafile '/oradata/ODSD08.dbf' resize 472M;1470122

　　alter database datafile '/oradata/ODSD09.dbf' resize 587M;2048122

　　alter database datafile '/oradata/ODSD10.dbf' resize 595M;2048132

　　alter database datafile '/oradata/ODSI01.dbf' resize 507M;178388

　　alter database datafile '/oradata/ODSI02.dbf' resize 505M;177488

　　alter database datafile '/oradata/ODSI03.dbf' resize 529M;1772118

　　alter database datafile '/oradata/ODSI04.dbf' resize 517M;1763105

　　alter database datafile '/oradata/ODSI05.dbf' resize 525M;1775113

　　alter database datafile '/oradata/TODSD01.dbf' resize 497M;1154233

　　alter database datafile '/oradata/TODSD02.dbf' resize 561M;1485230

　　alter database datafile '/oradata/TODSD03.dbf' resize 465M;1051218

　　alter database datafile '/oradata/TODSD04.dbf' resize 431M;878219

　　alter database datafile '/oradata/TODSD05.dbf' resize 598M;1542262

　　alter database datafile '/oradata/TODSI01.dbf' resize 385M;123872

　　alter database datafile '/oradata/TODSI02.dbf' resize 365M;118360

　　alter database datafile '/oradata/CTL01.dbf' resize 146M;19733

　　alter database datafile '/oradata/DWD01.dbf' resize 770M;2550225

　　alter database datafile '/oradata/DWI01.dbf' resize 386M;123873

　　alter database datafile '/oradata/OD01.dbf' resize 152M;25427

　　alter database datafile '/oradata/TODSI03.dbf' resize 288M;85048

　　alter database datafile '/oradata/TODSI04.dbf' resize 324M;103446

　　alter database datafile '/oradata/TODSI05.dbf' resize 343M;97784

　　alter database datafile '/oradata/DWI02.dbf' resize 356M;109472

　　alter database datafile '/oradata/DWI03.dbf' resize 366M;113175

　　alter database datafile '/oradata/RPTD01.dbf' resize 231M;36598

　　alter database datafile '/oradata/RPTI01.dbf' resize 300M;462159

　　alter database datafile '/oradata/ODSI06.dbf' resize 505M;173797

　　alter database datafile '/oradata/ODSD11.dbf' resize 535M;1757129

　　alter database datafile '/oradata/undotbs03.dbf' resize 176M;28349

　　檢查磁碟當前剩餘空間

　　$ bdf

　　/dev/vg01/lvol1 133120000 33173720 99166120 25% /oradata

　　小結

　　執行整個步驟之前，/oradata磁碟下的剩餘空間不足6G，執行步驟之後我們看到，目前系統中有將近100G的剩餘空間^_^。

　　效果明顯。