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',
'PORTALWWW.T_CONTENT_MATCH_DEVICE','PORTALPC.T_CONTENT','PORTALWWW.T_TEMP_JIMIN1','PORTALWWW.T_CONTENT_CARVE',
'PORTALPC.T_CONTENT_MATCH_DEVICE','PORTALWWW.T_CSSP_ORDER_RECORD','PORTALWWW.T_SMS_INFO','PORTALWWW.T_CONTENT_CARVE_BAK',
'PORTALWWW.V_ONDEMAND_ORDER_20101215','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 大对象
压缩表

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

转载于:http://blog.itpub.net/24996904/viewspace-1169930/