oracle 收高水位,oracle回收高水位

oracle回收高水位

查看数据库表的高水位情况(针对具体的某些表)：

select a.owner,

a.table_name,

round(a.blocks * to_number(p.value) / 1024 / 1024, 2) size_mb,

round(a.avg_row_len * a.num_rows / 1024 / 1024, 2) used_mb,

round(a.avg_row_len * a.num_rows / (a.blocks * to_number(p.value)) * 100,

2) pct,

to_char(a.last_analyzed, 'yyyy-mm-dd hh24:mi:ss') last_analyzed

from dba_tables a, v$parameter p

where p.name = 'db_block_size' and blocks>0 and owner||'.'||table_name in ('MM_DLS.DR$FULL_DEVICEID21131053$I','PORTALMO.V_RECOMMEND',

'SSMS.T_SYN_RESULT','SSMS.T_SYNCTIME_TMP_CY','MM_MOSVR.VS_UA_VERSION',

'SSMS.T_RB_STATISTICS_NEW','MM_DLS.T_ADVANCECONTENT_ALL','MM_DLS.T_ADVANCECONTENT',

'MM_DLS.T_CONTESTCONTENT','PORTALMO.USER_INFO_BAK0720','PORTALMO.TEST_4_2VID','SSMS.T_GAMESTOP',

'SSMS.T_GAME_CONTENT','SSMS.T_MB_SINGER_NEW','PUBUSER.T_R_LOCAL','SSMS.T_R_GCONTENT','SSMS.T_R_BASE',

'PORTAL','PORTALPC.T_CONTENT','PORTAL','PORTAL',

'PORTALPC.T_CONTENT_MATCH_DEVICE','PORTAL','PORTAL','PORTAL_BAK',

'PORTAL','MMOPEN.T_SMS_INFO','MMOPEN.T_TEMP_JIMIN1','MMOPEN.T_CONTENT_MATCH_DEVICE',

'SSMS.T_MQ_PROCESSING_PARA','SSMS.T_A_REPORT','SSMS.T_MQ_PROCESSING','SSMS.T_MQ_INFO','RCAT_MMP1.ROUT',

'SSMS.CM_CT_DEVICE_UPGRADE_DEL3BAK','MMPORT.MM_CONTENT_ANDROID','MMPORT.MM_CONTENT_DATA_EXT_930DEL',

'SSMS.T_A_ANDROID_LIST','SSMS.A_TEMP1','SSMS.T_FREE_DL_ORDER','SSMS.T_FREE_DL_REPORT','SSMS.T_VO_VIDEO_MID','SSMS.T_VO_REFERENCE');

针对结果中pct低于60的做高水位回收：

1、打开表的行移动功能

alter table SSMS.T_FREE_DL_REPORT                         enable row movement;

alter table SSMS.T_VO_VIDEO_MID                           enable row movement;

alter table SSMS.T_VO_REFERENCE                           enable row movement;

2、回收操作

alter table SSMS.T_FREE_DL_REPORT            shrink space;

alter table SSMS.T_VO_VIDEO_MID               shrink space;

alter table SSMS.T_VO_REFERENCE               shrink space;

为了最小限度影响业务，采用了shrink操作，该操作有一些限制，以下情况不能用shrink：

IOT索引组织表

用rowid创建的物化视图的基表(我也不知道要说什么了)

带有函数索引的表

SECUREFILE 大对象

压缩表