1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
| SET SERVEROUTPUT ON
BEGIN DBMS_STATS.GATHER_TABLE_STATS(OWNNAME => 'EGISNBUDATA',
TABNAME => 'NBU_QUOTATE_CC_ATTR_VALUE',
METHOD_OPT => 'FOR ALL COLUMNS SIZE REPEAT',
DEGREE => 1,
CASCADE => TRUE,
NO_INVALIDATE => FALSE);
END;
/
declare
v_last_analyzed date;
BEGIN
SELECT LAST_ANALYZED INTO V_LAST_ANALYZED
FROM DBA_TABLES WHERE OWNER = 'EGISNBUDATA'
AND TABLE_NAME = 'NBU_QUOTATE_CC_ATTR_VALUE';
IF V_LAST_ANALYZED >= TRUNC(SYSDATE) THEN
DBMS_OUTPUT.PUT_LINE('NBU_QUOTATE_CC_ATTR_VALUE表统计信息收集成功！');
ELSE
DBMS_OUTPUT.PUT_LINE('NBU_QUOTATE_CC_ATTR_VALUE表统计信息收集失败，请排查原因！');
END IF;
END;
/
------------delete_statistics_if_num_rows_is_zero ---------------
DECLARE
V_COUNT NUMBER;
BEGIN
SELECT num_rows
INTO V_COUNT FROM DBA_TABLES
WHERE owner = 'EGISNBUDATA' AND table_name = 'NBU_QUOTATE_CC_ATTR_VALUE';
if v_count=0 then
BEGIN DBMS_STATS.DELETE_TABLE_STATS(OWNNAME => 'EGISNBUDATA',
TABNAME => 'NBU_QUOTATE_CC_ATTR_VALUE',
CASCADE_COLUMNS => TRUE ,
CASCADE_INDEXES => TRUE ,
CASCADE_PARTS => TRUE );
END;
END IF;
END;
/
------------delete_statistics_if_num_rows_is_zero ---------------
|