|
数据文件的作用
HWM的基本概念
查看数据文件的使用情况
包括内容：数据文件大小，已经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_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 对想收缩的表空间中的表及索引进行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' 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类型的数据处理的一个简单的方法实将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; 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 检查磁盘当前剩余空间
$ bdf
/dev/vg01/lvol1 133120000 33173720 99166120 25% /oradata
小结
执行整个步骤之前，/oradata磁盘下的剩余空间不足6G，执行步骤之后我们看到，目前系统中有将近100G的剩余空间^_^。 效果明显。
本文来自CSDN博客，转载请标明出处：http://blog.csdn.net/47522341/archive/2009/02/06/3865946.aspx
|
|
|
