ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 收缩ORACLE的数据文件

收缩ORACLE的数据文件

原创 Linux操作系统 作者:lsm_3036 时间:2011-04-07 11:26:17 0 删除 编辑

查看数据文件的使用情况

包括内容:数据文件大小,已经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的剩余空间^_^。

效果明显。

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/16978544/viewspace-691852/,如需转载,请注明出处,否则将追究法律责任。

下一篇: VSFTP的简单配置
请登录后发表评论 登录
全部评论

注册时间:2008-12-08

  • 博文量
    64
  • 访问量
    138402