技术分享 | Oracle数据库表降高水位及索引重建

本文为墨天轮数据库管理服务团队第77期技术分享，内容原创，作者为技术顾问达世德，如需转载请联系小墨（VX：modb666）并注明来源。如需查看更多文章可关注【墨天轮】公众号。

当前数据库索引表空间使用率超过88%，需要降低表和索引的高水位。

一、确认基础信息

1、查看当前表空间使用率

13:50:44 SQL> set serveroutput on
13:50:44 SQL> set lin 300 pagesize 999
13:50:46 SQL> col TABLESPACE_NAME for a30
13:50:46 SQL> Select B.tablespace_name,sumb total_gb,Nvl(suma,0) free_gb,(sumb-Nvl(suma,0)) used_gb,        to_char(Nvl(round((sumb-suma)/sumb*100,2),100),'990.99')  "used%" from (Select tablespace_name,round(sum(bytes)/(1024*1024*1024),2) suma from dba_free_space group by tablespace_name) A,      (Select tablespace_name,round(sum(bytes)/(1024*1024*1024),3) sumb from dba_data_files group by tablespace_name) B  where A.tablespace_name(+)=B.tablespace_name  order by 5;
TABLESPACE_NAME                  TOTAL_GB    FREE_GB    USED_GB used%
------------------------------ ---------- ---------- ---------- -------
TBS_NEA1_DATA                    1930.074      617.9   1312.174   67.99
TBS_NEA1_HDATA                       1205      665.7      539.3   44.76
TBS_NEA1_INDEX                        305      37.62     267.38   87.67
TBS_NEA1_HINDEX                       785      91.55     693.45   88.34
... ...

2、查看数据表空间下占用的大对象

SET LINESIZE 1000 
SET PAGESIZE 400
COL OWNER FOR A16
COL SEGMENT_TYPE FOR A20; 
COL SEGMENT_NAME FOR A38; 
COL TABLESPACE_NAME FOR A20; 
SELECT *  
FROM   (SELECT OWNER, 
               SEGMENT_TYPE, 
               SEGMENT_NAME, 
               ROUND(SUM(BYTES) / 1024 / 1024/1024,2) TOTAL_SIZE_GB, 
                TABLESPACE_NAME 
        FROM   DBA_SEGMENTS 
        WHERE  TABLESPACE_NAME =UPPER('&TABLESPACE_NAME') 
        GROUP  BY OWNER, 
                  SEGMENT_TYPE, 
                  SEGMENT_NAME, 
                  TABLESPACE_NAME 
        ORDER  BY TOTAL_SIZE_GB DESC) 
 WHERE  ROWNUM <= 200;  
Enter value for tablespace_name: TBS_SFCINS1_DATA

3、查看表的具体信息

根据查出来的表，查看对应对象的物理大小及实际数据大小，判断是否需要降低高水位

col table_name for a40
col partition_name for a40
SELECT table_name,partition_name,
    ROUND (blocks * 8 / 1024 , 2) "物理大小(MB)",
    ROUND (num_rows * avg_row_len / 1024 / 1024, 2) "实际数据大小(MB)"
  FROM dba_tab_partitions WHERE UPPER(table_name) in ('TF_B_IBPLAT_SYN_H','TF_B_NOTIFYTRADELOG_H','TO_B_IBPLAT_SYN_RSLT_SUB_H')
  ORDER BY partition_name DESC;
TABLE_NAME                    PARTITION_NAME                 物理大小(MB)  实际数据大小(MB)
----------------------------- -----------------------------  ------------ ----------------
TF_B_NOTIFYTRADELOG_H         PAR_TF_B_NOTIFYTRADELOG_H_9        10834.48          7622.68
TF_B_NOTIFYTRADELOG_H         PAR_TF_B_NOTIFYTRADELOG_H_8        23407.98         16589.71
TF_B_NOTIFYTRADELOG_H         PAR_TF_B_NOTIFYTRADELOG_H_7         23995.4         16690.11
TF_B_NOTIFYTRADELOG_H         PAR_TF_B_NOTIFYTRADELOG_H_6        52074.48          34763.4
TF_B_NOTIFYTRADELOG_H         PAR_TF_B_NOTIFYTRADELOG_H_5        33817.63          23537.7
TF_B_NOTIFYTRADELOG_H         PAR_TF_B_NOTIFYTRADELOG_H_4        32688.76         22691.46
TF_B_NOTIFYTRADELOG_H         PAR_TF_B_NOTIFYTRADELOG_H_3        30719.71         21319.58
TF_B_NOTIFYTRADELOG_H         PAR_TF_B_NOTIFYTRADELOG_H_2        25049.35         17344.24
TF_B_NOTIFYTRADELOG_H         PAR_TF_B_NOTIFYTRADELOG_H_12       10462.84          7316.88
TF_B_NOTIFYTRADELOG_H         PAR_TF_B_NOTIFYTRADELOG_H_11        9896.41          6980.69
TF_B_NOTIFYTRADELOG_H         PAR_TF_B_NOTIFYTRADELOG_H_10       11941.38          8441.26
TF_B_NOTIFYTRADELOG_H         PAR_TF_B_NOTIFYTRADELOG_H_1        26877.53         18566.64
TF_B_IBPLAT_SYN_H             M12                                36586.88         30469.04
TF_B_IBPLAT_SYN_H             M11                                35581.88         29610.91
TF_B_IBPLAT_SYN_H             M10                                48131.41         40339.29
TF_B_IBPLAT_SYN_H             M09                                48410.36         40377.97
TF_B_IBPLAT_SYN_H             M08                                51075.28          42780.4
TF_B_IBPLAT_SYN_H             M07                                43559.93         36282.29
TF_B_IBPLAT_SYN_H             M06                                40551.79         33780.71
TF_B_IBPLAT_SYN_H             M05                                30183.24         25085.63
TF_B_IBPLAT_SYN_H             M04                                28159.27         23408.79
TF_B_IBPLAT_SYN_H             M03                                32905.54         27360.67
TF_B_IBPLAT_SYN_H             M02                                33316.13         27740.33
TF_B_IBPLAT_SYN_H             M01                                36398.07         30286.62
TO_B_IBPLAT_SYN_RSLT_SUB_H    D12                                  371.77           303.71
TO_B_IBPLAT_SYN_RSLT_SUB_H    D11                                  275.16           227.81
TO_B_IBPLAT_SYN_RSLT_SUB_H    D10                                  300.06           245.26
TO_B_IBPLAT_SYN_RSLT_SUB_H    D09                                  308.02           255.07
TO_B_IBPLAT_SYN_RSLT_SUB_H    D08                                  313.01           256.12
TO_B_IBPLAT_SYN_RSLT_SUB_H    D07                                  307.03           250.92
TO_B_IBPLAT_SYN_RSLT_SUB_H    D06                                  277.16           228.03
TO_B_IBPLAT_SYN_RSLT_SUB_H    D05                                   238.3           195.73
TO_B_IBPLAT_SYN_RSLT_SUB_H    D04                                  249.27           205.58
TO_B_IBPLAT_SYN_RSLT_SUB_H    D03                                  269.19           222.42
TO_B_IBPLAT_SYN_RSLT_SUB_H    D02                                  278.15           229.89
TO_B_IBPLAT_SYN_RSLT_SUB_H    D01

由此可见，各个表的对象占用的物理大小和实际使用大小相差较大，需要手动降低高水位。

二、降低表的高水位

本次采用 shrink space的方式，优点是能够自动维护对象的索引

1、开启表行移动

ALTER TABLE UCR_NEA1.TF_B_NOTIFYTRADELOG_H ENABLE ROW MOVEMENT;
ALTER TABLE UCR_NEA1.TF_B_IBPLAT_SYN_H ENABLE ROW MOVEMENT;
ALTER TABLE UCR_NEA1.TO_B_IBPLAT_SYN_RSLT_SUB_H ENABLE ROW MOVEMENT;

2、收缩分区

ALTER TABLE UCR_NEA1.TF_B_NOTIFYTRADELOG_H MODIFY PARTITION PAR_TF_B_NOTIFYTRADELOG_H_9   SHRINK SPACE cascade;  
ALTER TABLE UCR_NEA1.TF_B_NOTIFYTRADELOG_H MODIFY PARTITION PAR_TF_B_NOTIFYTRADELOG_H_8   SHRINK SPACE cascade;
ALTER TABLE UCR_NEA1.TF_B_NOTIFYTRADELOG_H MODIFY PARTITION PAR_TF_B_NOTIFYTRADELOG_H_7   SHRINK SPACE cascade;
ALTER TABLE UCR_NEA1.TF_B_NOTIFYTRADELOG_H MODIFY PARTITION PAR_TF_B_NOTIFYTRADELOG_H_6   SHRINK SPACE cascade;
ALTER TABLE UCR_NEA1.TF_B_NOTIFYTRADELOG_H MODIFY PARTITION PAR_TF_B_NOTIFYTRADELOG_H_5   SHRINK SPACE cascade;
ALTER TABLE UCR_NEA1.TF_B_NOTIFYTRADELOG_H MODIFY PARTITION PAR_TF_B_NOTIFYTRADELOG_H_4   SHRINK SPACE cascade;
ALTER TABLE UCR_NEA1.TF_B_NOTIFYTRADELOG_H MODIFY PARTITION PAR_TF_B_NOTIFYTRADELOG_H_3   SHRINK SPACE cascade;
ALTER TABLE UCR_NEA1.TF_B_NOTIFYTRADELOG_H MODIFY PARTITION PAR_TF_B_NOTIFYTRADELOG_H_2   SHRINK SPACE cascade;
ALTER TABLE UCR_NEA1.TF_B_NOTIFYTRADELOG_H MODIFY PARTITION PAR_TF_B_NOTIFYTRADELOG_H_12  SHRINK SPACE cascade;
ALTER TABLE UCR_NEA1.TF_B_NOTIFYTRADELOG_H MODIFY PARTITION PAR_TF_B_NOTIFYTRADELOG_H_11  SHRINK SPACE cascade;
ALTER TABLE UCR_NEA1.TF_B_NOTIFYTRADELOG_H MODIFY PARTITION PAR_TF_B_NOTIFYTRADELOG_H_10  SHRINK SPACE cascade;
ALTER TABLE UCR_NEA1.TF_B_NOTIFYTRADELOG_H MODIFY PARTITION PAR_TF_B_NOTIFYTRADELOG_H_1   SHRINK SPACE cascade;
ALTER TABLE UCR_NEA1.TF_B_IBPLAT_SYN_H MODIFY PARTITION M12  SHRINK SPACE cascade;    
ALTER TABLE UCR_NEA1.TF_B_IBPLAT_SYN_H MODIFY PARTITION M11  SHRINK SPACE cascade;
ALTER TABLE UCR_NEA1.TF_B_IBPLAT_SYN_H MODIFY PARTITION M10  SHRINK SPACE cascade;
ALTER TABLE UCR_NEA1.TF_B_IBPLAT_SYN_H MODIFY PARTITION M09  SHRINK SPACE cascade;
ALTER TABLE UCR_NEA1.TF_B_IBPLAT_SYN_H MODIFY PARTITION M08  SHRINK SPACE cascade;
ALTER TABLE UCR_NEA1.TF_B_IBPLAT_SYN_H MODIFY PARTITION M07  SHRINK SPACE cascade;
ALTER TABLE UCR_NEA1.TF_B_IBPLAT_SYN_H MODIFY PARTITION M06  SHRINK SPACE cascade;
ALTER TABLE UCR_NEA1.TF_B_IBPLAT_SYN_H MODIFY PARTITION M05  SHRINK SPACE cascade;
ALTER TABLE UCR_NEA1.TF_B_IBPLAT_SYN_H MODIFY PARTITION M04  SHRINK SPACE cascade;
ALTER TABLE UCR_NEA1.TF_B_IBPLAT_SYN_H MODIFY PARTITION M03  SHRINK SPACE cascade;
ALTER TABLE UCR_NEA1.TF_B_IBPLAT_SYN_H MODIFY PARTITION M02  SHRINK SPACE cascade;
ALTER TABLE UCR_NEA1.TF_B_IBPLAT_SYN_H MODIFY PARTITION M01  SHRINK SPACE cascade;
ALTER TABLE UCR_NEA1.TO_B_IBPLAT_SYN_RSLT_SUB_H MODIFY PARTITION D12 SHRINK SPACE cascade;   DONE
ALTER TABLE UCR_NEA1.TO_B_IBPLAT_SYN_RSLT_SUB_H MODIFY PARTITION D11 SHRINK SPACE cascade;
ALTER TABLE UCR_NEA1.TO_B_IBPLAT_SYN_RSLT_SUB_H MODIFY PARTITION D10 SHRINK SPACE cascade;
ALTER TABLE UCR_NEA1.TO_B_IBPLAT_SYN_RSLT_SUB_H MODIFY PARTITION D09 SHRINK SPACE cascade;
ALTER TABLE UCR_NEA1.TO_B_IBPLAT_SYN_RSLT_SUB_H MODIFY PARTITION D08 SHRINK SPACE cascade;
ALTER TABLE UCR_NEA1.TO_B_IBPLAT_SYN_RSLT_SUB_H MODIFY PARTITION D07 SHRINK SPACE cascade;
ALTER TABLE UCR_NEA1.TO_B_IBPLAT_SYN_RSLT_SUB_H MODIFY PARTITION D06 SHRINK SPACE cascade;
ALTER TABLE UCR_NEA1.TO_B_IBPLAT_SYN_RSLT_SUB_H MODIFY PARTITION D05 SHRINK SPACE cascade;
ALTER TABLE UCR_NEA1.TO_B_IBPLAT_SYN_RSLT_SUB_H MODIFY PARTITION D04 SHRINK SPACE cascade;
ALTER TABLE UCR_NEA1.TO_B_IBPLAT_SYN_RSLT_SUB_H MODIFY PARTITION D03 SHRINK SPACE cascade;
ALTER TABLE UCR_NEA1.TO_B_IBPLAT_SYN_RSLT_SUB_H MODIFY PARTITION D02 SHRINK SPACE cascade;
ALTER TABLE UCR_NEA1.TO_B_IBPLAT_SYN_RSLT_SUB_H MODIFY PARTITION D01 SHRINK SPACE cascade;

3、确认表状态

SELECT status FROM dba_objects WHERE object_name = 'TF_B_NOTIFYTRADELOG_H';
SELECT status FROM dba_objects WHERE object_name = 'TF_B_IBPLAT_SYN_H';
SELECT status FROM dba_objects WHERE object_name = 'TO_B_IBPLAT_SYN_RSLT_SUB_H';

4、关闭表行移动

ALTER TABLE UCR_NEA1.TF_B_NOTIFYTRADELOG_H DISABLE ROW MOVEMENT;
ALTER TABLE UCR_NEA1.TF_B_IBPLAT_SYN_H DISABLE ROW MOVEMENT;
ALTER TABLE UCR_NEA1.TO_B_IBPLAT_SYN_RSLT_SUB_H DISABLE ROW MOVEMENT;
ALTER TABLE UCR_NEA1.TF_B_IBPLAT_SYN_SUB_H DISABLE ROW MOVEMENT;

三、降低索引的高水位

如果需要降低索引表空间的高水位，就必须针对索引进行重建，因为是生产环境，因此选择online创建，防止重建期间发生锁表

1、查看对应索引表空间下的索引大小

col SEGMENT_NAME for a60
SELECT 
    SEGMENT_NAME, 
    SEGMENT_TYPE, 
    TABLESPACE_NAME, 
    BYTES / 1024 / 1024 AS SIZE_MB
FROM 
    DBA_SEGMENTS
WHERE 
    SEGMENT_TYPE = 'INDEX' 
    AND SEGMENT_NAME in('PK_TF_B_IBPLAT_SYN_SUB_H','IDX_IBPLAT_SYN_H_07','PK_TF_B_IBPLAT_SYN_H','IDX_IBPLAT_SYN_H_01','IDX_IBPLAT_SYN_H_02','IDX_IBPLAT_SYN_H_04','IDX_IBPLAT_SYN_H_10','IDX_IBPLAT_SYN_H_08','IDX_IBPLAT_SYN_H_03','IDX_IBPLAT_SYN_H_06') order by 4 desc;
SEGMENT_NAME                SEGMENT_TYPE         TABLESPACE_NAME                   SIZE_MB
------------------------- - -------------------- ------------------------------ ----------
PK_TF_B_IBPLAT_SYN_SUB_H    INDEX                TBS_NEA1_HINDEX                    263807
IDX_IBPLAT_SYN_H_07         INDEX                TBS_NEA1_HINDEX                     61594   
PK_TF_B_IBPLAT_SYN_H        INDEX                TBS_NEA1_HINDEX                     57249   
IDX_IBPLAT_SYN_H_01         INDEX                TBS_NEA1_HINDEX                     55875   
IDX_IBPLAT_SYN_H_04         INDEX                TBS_NEA1_HINDEX                     49842  
IDX_IBPLAT_SYN_H_02         INDEX                TBS_NEA1_HINDEX                     49292   
IDX_IBPLAT_SYN_H_10         INDEX                TBS_NEA1_HINDEX                     42827   
IDX_IBPLAT_SYN_H_08         INDEX                TBS_NEA1_HINDEX                     42255   
IDX_IBPLAT_SYN_H_03         INDEX                TBS_NEA1_HINDEX                     37037   
IDX_IBPLAT_SYN_H_06         INDEX                TBS_NEA1_HINDEX                      7048 
col SEGMENT_NAME for a60
SELECT 
    SEGMENT_NAME, 
    SEGMENT_TYPE, 
    TABLESPACE_NAME, 
    BYTES / 1024 / 1024 AS SIZE_MB
FROM 
    DBA_SEGMENTS
WHERE 
    SEGMENT_TYPE = 'INDEX' 
    AND SEGMENT_NAME in('PK_TF_B_IBTRADE','IDX_TD_M_SAVE_REQ_LOG_3','IDX_IBPLAT_SYN_LOG_02','PK_PID','IDX_TD_M_SAVE_REQ_LOG_4','PK_TF_B_IBPLAT_SYN_LOG','IDX_IBPLAT_SYN_LOG_01','IDX_TF_B_NOTIFYTRADELOG_2','PK_TF_B_NOTIFYTRADELOG','IDX_TD_M_SAVE_REQ_LOG_1','IDX_TD_M_SAVE_REQ_LOG_2','IDX_TF_B_NOTIFYTRADELOG_3','IDX_TF_B_NOTIFYTRADELOG_1') 
order by 4 desc;
SEGMENT_NAME               SEGMENT_TYPE         TABLESPACE_NAME                   SIZE_MB
-------------------------- -------------------- ------------------------------ ----------
PK_TF_B_IBTRADE            INDEX                TBS_NEA1_INDEX                      43330
IDX_TD_M_SAVE_REQ_LOG_3    INDEX                TBS_NEA1_INDEX                      31756
IDX_IBPLAT_SYN_LOG_02      INDEX                TBS_NEA1_INDEX                      30401
PK_PID                     INDEX                TBS_NEA1_INDEX                      24452    
IDX_TD_M_SAVE_REQ_LOG_4    INDEX                TBS_NEA1_INDEX                      23885    
PK_TF_B_IBPLAT_SYN_LOG     INDEX                TBS_NEA1_INDEX                      20959    
IDX_IBPLAT_SYN_LOG_01      INDEX                TBS_NEA1_INDEX                      14468    
IDX_TF_B_NOTIFYTRADELOG_2  INDEX                TBS_NEA1_INDEX                      11555    
PK_TF_B_NOTIFYTRADELOG     INDEX                TBS_NEA1_INDEX                      11339   
IDX_TD_M_SAVE_REQ_LOG_1    INDEX                TBS_NEA1_INDEX                      10801    
IDX_TD_M_SAVE_REQ_LOG_2    INDEX                TBS_NEA1_INDEX                      10603    
IDX_TF_B_NOTIFYTRADELOG_3  INDEX                TBS_NEA1_INDEX 

2、开始索引重建

TBS_NEA1_HINDEX：
alter index UCR_NEA1.PK_TF_B_IBPLAT_SYN_SUB_H  rebuild online parallel 12;  
alter index UCR_NEA1.IDX_IBPLAT_SYN_H_07       rebuild online parallel 12;  
alter index UCR_NEA1.PK_TF_B_IBPLAT_SYN_H      rebuild online parallel 12;  
alter index UCR_NEA1.IDX_IBPLAT_SYN_H_01       rebuild online parallel 12;  
alter index UCR_NEA1.IDX_IBPLAT_SYN_H_04       rebuild online parallel 12;  
alter index UCR_NEA1.IDX_IBPLAT_SYN_H_02       rebuild online parallel 12;  
alter index UCR_NEA1.IDX_IBPLAT_SYN_H_10       rebuild online parallel 12;  
alter index UCR_NEA1.IDX_IBPLAT_SYN_H_08       rebuild online parallel 12;  
alter index UCR_NEA1.IDX_IBPLAT_SYN_H_03       rebuild online parallel 12;  
alter index UCR_NEA1.IDX_IBPLAT_SYN_H_06       rebuild online parallel 12;  
TBS_NEA1_INDEX：
alter index UCR_NEA1.PK_TF_B_IBTRADE              rebuild online parallel 12; 
alter index UCR_NEA1.IDX_TD_M_SAVE_REQ_LOG_3      rebuild online parallel 12; 
alter index UCR_NEA1.IDX_IBPLAT_SYN_LOG_02        rebuild online parallel 12; 
alter index UCR_NEA1.PK_PID                       rebuild online parallel 12; 
alter index UCR_NEA1.IDX_TD_M_SAVE_REQ_LOG_4      rebuild online parallel 12; 
alter index UCR_NEA1.PK_TF_B_IBPLAT_SYN_LOG       rebuild online parallel 12; 
alter index UCR_NEA1.IDX_IBPLAT_SYN_LOG_01        rebuild online parallel 12; 
alter index UCR_NEA1.IDX_TF_B_NOTIFYTRADELOG_2    rebuild online parallel 12; 
alter index UCR_NEA1.PK_TF_B_NOTIFYTRADELOG       rebuild online parallel 12; 
alter index UCR_NEA1.IDX_TD_M_SAVE_REQ_LOG_1      rebuild online parallel 12; 
alter index UCR_NEA1.IDX_TD_M_SAVE_REQ_LOG_2      rebuild online parallel 12; 
alter index UCR_NEA1.IDX_TF_B_NOTIFYTRADELOG_3    rebuild online parallel 12; 
alter index UCR_NEA1.IDX_TF_B_NOTIFYTRADELOG_1    rebuild online parallel 12;

3、关闭索引的并行

alter index UCR_NEA1.PK_TF_B_IBPLAT_SYN_SUB_H  parallel 1;  
alter index UCR_NEA1.IDX_IBPLAT_SYN_H_07       parallel 1;  
alter index UCR_NEA1.PK_TF_B_IBPLAT_SYN_H      parallel 1;  
alter index UCR_NEA1.IDX_IBPLAT_SYN_H_01       parallel 1;   
alter index UCR_NEA1.IDX_IBPLAT_SYN_H_04       parallel 1;  
alter index UCR_NEA1.IDX_IBPLAT_SYN_H_02       parallel 1;  
alter index UCR_NEA1.IDX_IBPLAT_SYN_H_10       parallel 1;  
alter index UCR_NEA1.IDX_IBPLAT_SYN_H_08       parallel 1;  
alter index UCR_NEA1.IDX_IBPLAT_SYN_H_03       parallel 1;  
alter index UCR_NEA1.IDX_IBPLAT_SYN_H_06       parallel 1;  
alter index UCR_NEA1.PK_TF_B_IBTRADE            parallel 1; 
alter index UCR_NEA1.IDX_TD_M_SAVE_REQ_LOG_3    parallel 1; 
alter index UCR_NEA1.IDX_IBPLAT_SYN_LOG_02      parallel 1; 
alter index UCR_NEA1.PK_PID                     parallel 1; 
alter index UCR_NEA1.IDX_TD_M_SAVE_REQ_LOG_4    parallel 1; 
alter index UCR_NEA1.PK_TF_B_IBPLAT_SYN_LOG     parallel 1; 
alter index UCR_NEA1.IDX_IBPLAT_SYN_LOG_01      parallel 1; 
alter index UCR_NEA1.IDX_TF_B_NOTIFYTRADELOG_2  parallel 1; 
alter index UCR_NEA1.PK_TF_B_NOTIFYTRADELOG     parallel 1;  
alter index UCR_NEA1.IDX_TD_M_SAVE_REQ_LOG_1    parallel 1; 
alter index UCR_NEA1.IDX_TD_M_SAVE_REQ_LOG_2    parallel 1; 
alter index UCR_NEA1.IDX_TF_B_NOTIFYTRADELOG_3  parallel 1; 
alter index UCR_NEA1.IDX_TF_B_NOTIFYTRADELOG_1  parallel 1;

4、更新表统计信息

begin 
dbms_stats.GATHER_TABLE_STATS( OWNNAME=>'UCR_NEA1', TABNAME=>'TF_B_NOTIFYTRADELOG_H',ESTIMATE_PERCENT=>10, METHOD_OPT=>'for all columns size auto', degree=>8, granularity=>'ALL', cascade=>TRUE, no_invalidate=>false); 
END; 
/
begin 
dbms_stats.GATHER_TABLE_STATS( OWNNAME=>'UCR_NEA1', TABNAME=>'TF_B_IBPLAT_SYN_H',ESTIMATE_PERCENT=>10, METHOD_OPT=>'for all columns size auto', degree=>8, granularity=>'ALL', cascade=>TRUE, no_invalidate=>false); 
END; 
/
begin 
dbms_stats.GATHER_TABLE_STATS( OWNNAME=>'UCR_NEA1', TABNAME=>'TO_B_IBPLAT_SYN_RSLT_SUB_H',ESTIMATE_PERCENT=>10, METHOD_OPT=>'for all columns size auto', degree=>8, granularity=>'ALL', cascade=>TRUE, no_invalidate=>false); 
END; 
/
begin 
dbms_stats.GATHER_TABLE_STATS( OWNNAME=>'UCR_NEA1', TABNAME=>'TF_B_IBPLAT_SYN_SUB_H',ESTIMATE_PERCENT=>10, METHOD_OPT=>'for all columns size auto', degree=>8, granularity=>'ALL', cascade=>TRUE, no_invalidate=>false); 
END; 
/
begin 
dbms_stats.GATHER_TABLE_STATS( OWNNAME=>'UCR_NEA1', TABNAME=>'TF_B_IBTRADE',ESTIMATE_PERCENT=>10, METHOD_OPT=>'for all columns size auto', degree=>8, granularity=>'ALL', cascade=>TRUE, no_invalidate=>false); 
END; 
/
begin 
dbms_stats.GATHER_TABLE_STATS( OWNNAME=>'UCR_NEA1', TABNAME=>'NEA_SAVE_REQ_LOG',ESTIMATE_PERCENT=>10, METHOD_OPT=>'for all columns size auto', degree=>8, granularity=>'ALL', cascade=>TRUE, no_invalidate=>false); 
END; 
/

5、更新索引统计信息

EXEC DBMS_STATS.GATHER_INDEX_STATS('UCR_NEA1', 'PK_TF_B_IBPLAT_SYN_SUB_H');
EXEC DBMS_STATS.GATHER_INDEX_STATS('UCR_NEA1', 'IDX_IBPLAT_SYN_H_07');
EXEC DBMS_STATS.GATHER_INDEX_STATS('UCR_NEA1', 'PK_TF_B_IBPLAT_SYN_H');
EXEC DBMS_STATS.GATHER_INDEX_STATS('UCR_NEA1', 'IDX_IBPLAT_SYN_H_01');
EXEC DBMS_STATS.GATHER_INDEX_STATS('UCR_NEA1', 'IDX_IBPLAT_SYN_H_04');
EXEC DBMS_STATS.GATHER_INDEX_STATS('UCR_NEA1', 'IDX_IBPLAT_SYN_H_02');
EXEC DBMS_STATS.GATHER_INDEX_STATS('UCR_NEA1', 'IDX_IBPLAT_SYN_H_10');
EXEC DBMS_STATS.GATHER_INDEX_STATS('UCR_NEA1', 'IDX_IBPLAT_SYN_H_08');
EXEC DBMS_STATS.GATHER_INDEX_STATS('UCR_NEA1', 'IDX_IBPLAT_SYN_H_03');
EXEC DBMS_STATS.GATHER_INDEX_STATS('UCR_NEA1', 'IDX_IBPLAT_SYN_H_06');
EXEC DBMS_STATS.GATHER_INDEX_STATS('UCR_NEA1', 'PK_TF_B_IBTRADE');
EXEC DBMS_STATS.GATHER_INDEX_STATS('UCR_NEA1', 'IDX_TD_M_SAVE_REQ_LOG_3');
EXEC DBMS_STATS.GATHER_INDEX_STATS('UCR_NEA1', 'IDX_IBPLAT_SYN_LOG_02');
EXEC DBMS_STATS.GATHER_INDEX_STATS('UCR_NEA1', 'PK_PID');
EXEC DBMS_STATS.GATHER_INDEX_STATS('UCR_NEA1', 'IDX_TD_M_SAVE_REQ_LOG_4');
EXEC DBMS_STATS.GATHER_INDEX_STATS('UCR_NEA1', 'PK_TF_B_IBPLAT_SYN_LOG');
EXEC DBMS_STATS.GATHER_INDEX_STATS('UCR_NEA1', 'IDX_IBPLAT_SYN_LOG_01');
EXEC DBMS_STATS.GATHER_INDEX_STATS('UCR_NEA1', 'IDX_TF_B_NOTIFYTRADELOG_2');
EXEC DBMS_STATS.GATHER_INDEX_STATS('UCR_NEA1', 'PK_TF_B_NOTIFYTRADELOG');
EXEC DBMS_STATS.GATHER_INDEX_STATS('UCR_NEA1', 'IDX_TD_M_SAVE_REQ_LOG_1');
EXEC DBMS_STATS.GATHER_INDEX_STATS('UCR_NEA1', 'IDX_TD_M_SAVE_REQ_LOG_2');
EXEC DBMS_STATS.GATHER_INDEX_STATS('UCR_NEA1', 'IDX_TF_B_NOTIFYTRADELOG_3');
EXEC DBMS_STATS.GATHER_INDEX_STATS('UCR_NEA1', 'IDX_TF_B_NOTIFYTRADELOG_1');

6、查看是否有失效索引

select 'alter index '||OWNER||'.'||INDEX_NAME||' rebuild  parallel 8;' from dba_indexes where status='UNUSABLE' and table_name ='TF_B_IBPLAT_SYN_H';

四、注意事项

表空间的重建原理是：先复制式的创建后再删除旧的，这就意味着对于空间的要求是双倍的，如一个20GB的索引，重建时需要40GB的存储空间才能完成。

如果存储空间已经不足，可以将表空间重建至其他表空间：

# 因为第一个索引太大，rebuld时，当前表空间不足，需要重建到其他表空间。
ALTER INDEX UCR_NEA1.PK_TF_B_IBPLAT_SYN_SUB_H REBUILD online TABLESPACE TBS_SFCINS1_INDEX parallel 10;

五、执行结果

查看表空间使用率

TABLESPACE_NAME                  TOTAL_GB    FREE_GB    USED_GB used%
------------------------------ ---------- ---------- ---------- -------
TBS_NEA1_HINDEX                   824.453     657.25    167.203   20.28
TBS_NEA1_HDATA                       1205     665.69     539.31   44.76
TBS_NEA1_INDEX                        305     100.76     204.24   66.96
TBS_NEA1_DATA                    1930.074     617.84   1312.234   67.99

---

墨天轮从乐知乐享的数据库技术社区蓄势出发，全面升级，提供多类型数据库管理服务。墨天轮数据库管理服务旨在为用户构建信赖可托付的数据库环境，并为数据库厂商提供中立的生态支持。

服务官网：https://www.modb.pro/service