–指定某张表对象，通过schema的方式收集统计信息

DECLARE
    V_FILTER_LIST OBJ_FILTER_TAB;
BEGIN
    V_FILTER_LIST := OBJ_FILTER_TAB();
    
    -- 添加需要收集的表名（区分大小写）
    V_FILTER_LIST.EXTEND(3);
    V_FILTER_LIST(1) := OBJ_FILTER_TYPE('SYSDBA', 'EMPLOYEES', NULL);  -- 表1
    V_FILTER_LIST(2) := OBJ_FILTER_TYPE('SYSDBA', 'DEPARTMENTS', NULL); -- 表2
    V_FILTER_LIST(3) := OBJ_FILTER_TYPE('SYSDBA', 'ORDERS', NULL);     -- 表3

    -- 调用存储过程收集统计信息
    DBMS_STATS.GATHER_SCHEMA_STATS(
        OWNNAME          => 'SYSDBA',                -- 模式名
        ESTIMATE_PERCENT => 100,                      -- 采样率100%
        METHOD_OPT       => 'FOR ALL COLUMNS SIZE AUTO', -- 自动列分析
        OBJ_FILTER_LIST  => V_FILTER_LIST             -- 指定表过滤
    );
END;

–如果需动态收集数据量变化超过 10% 的表：

ALTER SYSTEM SET 'AUTO_STAT_OBJ' = 1 BOTH;

SELECT 
    ID AS 表ID,
    INSERT_ROWS AS 新增行数,
    DELETE_ROWS AS 删除行数,
    UPDATE_ROWS AS 更新行数
FROM V$AUTO_STAT_TABLE_IDU
WHERE ID = (SELECT ID FROM SYSOBJECTS WHERE NAME = 'EMPLOYEES');

或
– 启用表级监控（动态生效）

ALTER SYSTEM SET 'MONITOR_MODIFICATIONS' = 2 BOTH;  -- 2=指定表监控

– 设置监控表

DBMS_STATS.SET_TABLE_PREFS('SYSDBA', 'TEST_DEL', 'MONITOR_MODIFICATIONS_FLAG', 'TRUE');

– 设置刷新间隔（默认3600秒）

ALTER SYSTEM SET 'MON_CHECK_INTERVAL' = 5 BOTH;  -- 每5秒刷新一次

–查询统计信息变化

SELECT 
    ID AS 表ID,
    INSERTS AS 新增行数,
    DELETES AS 删除行数,
    UPDATES AS 更新行数,
    LAST_MOD_TIME AS 最后变更时间,
    *
FROM SYSMODIFICATIONS
WHERE ID = (SELECT ID FROM SYSOBJECTS WHERE NAME = 'TEST_DEL');

–结合语法，动态收集数据量变化超过 10% 的表

DECLARE
    V_FILTER_LIST DBMS_STATS.OBJECTTAB;
CURSOR c_data IS
SELECT 
                OWNER OWNNAME, 
                'TABLE' OBJTYPE,
                TABLE_NAME OBJNAME
            FROM (
                SELECT 
                    SCH.NAME AS OWNER,
                    TAB.NAME AS TABLE_NAME,
                    sm.inserts-sm.deletes AS TOTAL_ROWS,
                    ST.T_TOTAL AS STATS_TOTAL_ROWS
                FROM 
                    SYSOBJECTS TAB
                LEFT JOIN SYSSTATS ST ON ST.ID = TAB.ID
                left join SYSOBJECTS SCH on sch.id = tab.schid
                left join SYSMODIFICATIONS SM on sm.id=tab.id
                WHERE 
                    SCH.NAME = 'SYSDBA'
                    AND T_FLAG='T'
            )
            WHERE ABS((TOTAL_ROWS - NVL(STATS_TOTAL_ROWS, 0)) / TOTAL_ROWS) > 0.1;
BEGIN    
  OPEN c_data;
  LOOP
    FETCH c_data INTO 
      V_FILTER_LIST(0).OWNNAME,
      V_FILTER_LIST(0).OBJTYPE,
      V_FILTER_LIST(0).OBJNAME;
    
    EXIT WHEN c_data%NOTFOUND;
    -- 处理逻辑
    DBMS_STATS.GATHER_SCHEMA_STATS(
        OWNNAME=>'SYSDBA', 
        ESTIMATE_PERCENT=>100, 
        METHOD_OPT=>'FOR ALL COLUMNS SIZE AUTO',
        OBJ_FILTER_LIST => V_FILTER_LIST
    );
  END LOOP;
  CLOSE c_data;
END;

–查看统计信息变化

SELECT 
    SCH.NAME AS SCHEMA_NAME,
    TAB.NAME AS TABLE_NAME,
    ST.LAST_GATHERED 
FROM 
    SYSSTATS ST,
    SYSOBJECTS TAB,
    SYSOBJECTS SCH 
WHERE 
    SCH.ID = TAB.SCHID 
    AND ST.ID = TAB.ID 
    AND SCH.NAME = 'SYSDBA' 
    AND T_FLAG='T'
    AND TAB.NAME IN ('TEST_DEL', 'TEST2_DEL');