數(shù)據(jù)文件的作用
HWM的基本概念
查看數(shù)據(jù)文件的使用情況
包括內(nèi)容：數(shù)據(jù)文件大小，已經(jīng)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
結(jié)果說明：
File_id ： 文件編號
File_name: 文件名稱
File_size: 數(shù)據(jù)文件占用磁盤空間大小
Freesize：文件中被標(biāo)記為free的空間大小
Usedsize: 使用的空間大小。
Hwmsize： 已經(jīng)分配出去的空間大小，如果希望通過alter database datafile … resize integerM回收空間，將需要這個值作為參考，不能回收到這個值之下，否則會報錯。
Freee_belowhwm_size： 在HWM（高水位標(biāo)記線之下的空閑空間數(shù)），這個是理論上的可以回收的空間大小。
Curr_can_shrink： 這個是實(shí)際大小與HWM標(biāo)記之間的差，就是還沒有分配出去的空間大小。
file_id
file_name
filesize
freesize
usedsize
hwmsize
free_belowhwm_size
curr_can_shrink
11
/oradata/ODSD01.dbf
2048
1908
140
2048
1908
0
12
/oradata/ODSD02.dbf
2048
1897
151
2048
1897
0
20
/oradata/ODSD10.dbf
2048
1897
151
2048
1897
0
16
/oradata/ODSD06.dbf
2048
1889
159
2048
1889
0
15
/oradata/ODSD05.dbf
2048
1888
160
2048
1888
0
19
/oradata/ODSD09.dbf
2048
1885
163
2048
1885
0
13
/oradata/ODSD03.dbf
2048
1884
164
2048
1884
0
17
/oradata/ODSD07.dbf
2048
1884
164
2048
1884
0
14
/oradata/ODSD04.dbf
2048
1813
235
2041
1806
7
34
/oradata/DWD01.dbf
4000
3701
299
2088
1789
1912
51
/oradata/ODSD11.dbf
2048
1963
85
1584
1499
464
21
/oradata/ODSI01.dbf
2048
1913
135
1617
1482
431
25
/oradata/ODSI05.dbf
2048
1910
138
1607
1469
441
22
/oradata/ODSI02.dbf
2048
1903
145
1606
1461
442
24
/oradata/ODSI04.dbf
2048
1909
139
1592
1453
456
23
/oradata/ODSI03.dbf
2048
1892
156
1603
1447
445
48
/oradata/ODSI06.dbf
2048
1925
123
1559
1436
489
30
/oradata/TODSD05.dbf
2048
1804
244
1315
1071
733
18
/oradata/ODSD08.dbf
2048
1881
167
1225
1058
823
27
/oradata/TODSD02.dbf
2048
1818
230
1244
1014
804
31
/oradata/TODSI01.dbf
2048
1977
71
936
865
1112
35
/oradata/DWI01.dbf
2048
1973
75
936
861
1112
32
/oradata/TODSI02.dbf
2048
1969
79
867
788
1181
43
/oradata/DWI03.dbf
2048
1975
73
802
729
1246
42
/oradata/DWI02.dbf
2048
1983
65
755
690
1293
39
/oradata/TODSI04.dbf
2048
1971
77
680
603
1368
26
/oradata/TODSD01.dbf
2048
1819
229
830
601
1218
40
/oradata/TODSI05.dbf
2048
1976
72
609
537
1439
28
/oradata/TODSD03.dbf
2048
1793
255
702
447
1346
37
/oradata/TODSI03.dbf
2048
1946
102
450
348
1598
29
/oradata/TODSD04.dbf
2048
1793
255
485
230
1563
33
/oradata/CTL01.dbf
500
494
6
21
15
479
10
/oradata/xdb01.dbf
47
3
44
46
2
1
1
/oradata/system01.dbf
1040
6
1034
1034
0
6
3
/oradata/cwmlite01.dbf
20
2
18
18
0
2
4
/oradata/drsys01.dbf
20
10
10
10
0
10
36
/oradata/OD01.dbf
500
407
93
93
0
407
5
/oradata/example01.dbf
139
0
139
139
0
0
54
/oradata/TCLKING.dbf
5
0
5
5
0
0
56
/oradata/undotbs03.dbf
1000
996
4
4
0
996
55
/oradata/HWM01.dbf
5000
4963
37
37
0
4963
49
/oradata/DP23.dbf
10
7
3
3
0
7
7
/oradata/odm01.dbf
20
11
9
9
0
11
9
/oradata/users01.dbf
83
0
83
82
-1
1
46
/oradata/RPTI01.dbf
1024
802
222
221
-1
803
45
/oradata/RPTD01.dbf
1024
923
101
100
-1
924
38
/oradata/FBI.dbf
200
79
121
120
-1
80
對想收縮的表空間中的表及索引進(jìn)行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類型的數(shù)據(jù)超難管理，不能通過move來傳輸，也不能通過諸如insert t1 select long_col from t2的方式（或者使用游標(biāo)可以解決這個問題）請注意在設(shè)計中盡量避免使用LONG類型。
檢查當(dāng)前表空間中的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'
tablespace
segmentname
segtype
datatype
colname
CTL
CTL.ETL_LOG
TABLE
LONG
EXECUTE_SQL
CTL
CTL.PLAN_TABLE
TABLE
LONG
OTHER
DWD
DW.PLAN_TABLE
TABLE
LONG
OTHER
CTL
OD.PLAN_TABLE
TABLE
LONG
OTHER
FBI
FBI.PLAN_TABLE
TABLE
LONG
OTHER
對long類型的數(shù)據(jù)處理的一個簡單的方法實(shí)將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，而且會失效。在創(chuàng)建失效的索引之前，使用到索引的查詢語句將會報錯。失效的索引需要使用rebuild重創(chuàng)建。
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表空間下的分區(qū)table及index
和普通表一樣，索引也會失效，區(qū)別的僅僅是語法而已。
分區(qū)表move基本語法
如果是單級分區(qū)，則使用關(guān)鍵字partition，如果是多級分區(qū)，則使用subpartition替代partition。如果分區(qū)或分區(qū)索引比較大，可以使用并行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分區(qū)表
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;
循環(huán)執(zhí)行上述語句，直到選不出結(jié)果。
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標(biāo)志，而且其重建方式跟普通索引一致，所以在執(zhí)行忘回導(dǎo)入的時候需要按照move 普通表；move分區(qū)表；move全局索引；move分區(qū)索引；move lob對象的順序進(jìn)行。
重建分區(qū)索引
視圖dba_part_indexes存儲分區(qū)表的本地索引，查詢發(fā)現(xiàn)當(dāng)前系統(tǒng)中不存在本地索引，可以忽略。
select * from dba_part_indexes t where t.owner not in ('SYSTEM','SH')
move表空間下的LOB類型
在建立含有Lob字典的表時，oracle會自動為Lob字段建立兩個單獨(dú)的segment，一個用來存放數(shù)據(jù)（segment_type=LOBSEGMENT），另一個用來存放索引（segment_type=LOBINDEX）。默認(rèn)他們會存儲在和表一起的表空間。
我們對表move時，LOB類型字段和該字段索引不會跟著move，必須使用單據(jù)的語句來執(zhí)行該字段的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
執(zhí)行完上述操作步驟后，我們檢查tablespace的空間使用情況可以發(fā)現(xiàn)，所有相關(guān)數(shù)據(jù)文件的hwm都已經(jīng)變?yōu)?，也就是說所有的空間都已經(jīng)變?yōu)槲捶峙錉顟B(tài)。但這時如果我們將數(shù)據(jù)文件dump出去，會發(fā)現(xiàn)原來的數(shù)據(jù)還在，只不過在數(shù)據(jù)字典中將其標(biāo)識為未分配。
Move對象的逆順序
普通表對象
將普通表對象和分區(qū)表對象按照其owner的不同從HWM臨時表空間move到其默認(rèn)的表空間中區(qū)。
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
分區(qū)表對象
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;
反復(fù)執(zhí)行上述過程，直到?jīng)]有記錄可以選擇。
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的命令標(biāo)準(zhǔn)為username+’I’，如果類似的表空間不存在，我們就將索引數(shù)據(jù)存儲到用戶的默認(rèn)表空間中。所以我們可以使用下面的語句將index rebuild到對應(yīng)的表空間中。
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類型數(shù)據(jù)隨著table對象存儲在對象owner的默認(rèn)表空間中。
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，則不考慮收縮。
收縮目標(biāo)：當(dāng)前數(shù)據(jù)文件大小 － （沒分配空間－ 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
收縮語句
文件大小
收縮目標(biāo)
alter database datafile '/oradata/HWM02.dbf' resize 2671M;
5000
1989
alter database datafile '/oradata/ODSD01.dbf' resize 598M;
2048
136
alter database datafile '/oradata/ODSD02.dbf' resize 592M;
2048
128
alter database datafile '/oradata/ODSD03.dbf' resize 591M;
2048
127
alter database datafile '/oradata/ODSD04.dbf' resize 742M;
2048
316
alter database datafile '/oradata/ODSD05.dbf' resize 594M;
2048
130
alter database datafile '/oradata/ODSD06.dbf' resize 597M;
2048
134
alter database datafile '/oradata/ODSD07.dbf' resize 598M;
2048
135
alter database datafile '/oradata/ODSD08.dbf' resize 472M;
1470
122
alter database datafile '/oradata/ODSD09.dbf' resize 587M;
2048
122
alter database datafile '/oradata/ODSD10.dbf' resize 595M;
2048
132
alter database datafile '/oradata/ODSI01.dbf' resize 507M;
1783
88
alter database datafile '/oradata/ODSI02.dbf' resize 505M;
1774
88
alter database datafile '/oradata/ODSI03.dbf' resize 529M;
1772
118
alter database datafile '/oradata/ODSI04.dbf' resize 517M;
1763
105
alter database datafile '/oradata/ODSI05.dbf' resize 525M;
1775
113
alter database datafile '/oradata/TODSD01.dbf' resize 497M;
1154
233
alter database datafile '/oradata/TODSD02.dbf' resize 561M;
1485
230
alter database datafile '/oradata/TODSD03.dbf' resize 465M;
1051
218
alter database datafile '/oradata/TODSD04.dbf' resize 431M;
878
219
alter database datafile '/oradata/TODSD05.dbf' resize 598M;
1542
262
alter database datafile '/oradata/TODSI01.dbf' resize 385M;
1238
72
alter database datafile '/oradata/TODSI02.dbf' resize 365M;
1183
60
alter database datafile '/oradata/CTL01.dbf' resize 146M;
197
33
alter database datafile '/oradata/DWD01.dbf' resize 770M;
2550
225
alter database datafile '/oradata/DWI01.dbf' resize 386M;
1238
73
alter database datafile '/oradata/OD01.dbf' resize 152M;
254
27
alter database datafile '/oradata/TODSI03.dbf' resize 288M;
850
48
alter database datafile '/oradata/TODSI04.dbf' resize 324M;
1034
46
alter database datafile '/oradata/TODSI05.dbf' resize 343M;
977
84
alter database datafile '/oradata/DWI02.dbf' resize 356M;
1094
72
alter database datafile '/oradata/DWI03.dbf' resize 366M;
1131
75
alter database datafile '/oradata/RPTD01.dbf' resize 231M;
365
98
alter database datafile '/oradata/RPTI01.dbf' resize 300M;
462
159
alter database datafile '/oradata/ODSI06.dbf' resize 505M;
1737
97
alter database datafile '/oradata/ODSD11.dbf' resize 535M;
1757
129
alter database datafile '/oradata/undotbs03.dbf' resize 176M;
283
49
檢查磁盤當(dāng)前剩余空間
$ bdf
/dev/vg01/lvol1 133120000 33173720 99166120 25% /oradata
小結(jié)
執(zhí)行整個步驟之前，/oradata磁盤下的剩余空間不足6G，執(zhí)行步驟之后我們看到，目前系統(tǒng)中有將近100G的剩余空間^_^。
效果明顯。
