ORACLE分析表生成索引信息
-- 分析 表 (ORACLE 11G及上版本) DECLARE CURSOR C1 IS select OBJ.OWNER, OBJ.OBJECT_NAME , --- 'SELECT * FROM '|| OBJ.OWNER ||'.'||OBJ.OBJECT_NAME AS SQL_SCRIPT, OBJ.OBJECT_TYPE from sys.dba_objects obj where obj.owner = 'INFODBA' AND OBJ.OBJECT_TYPE = 'TABLE' ORDER BY OBJ.OBJECT_NAME ; V_COUNT NUMBER := 0 ; begin FOR R1 IN C1 LOOP DBMS_STATS.gather_table_stats( ownname => 'INFODBA', TABNAME => R1.OBJECT_NAME , -- 'PPOM_USER', CASCADE => TRUE, ESTIMATE_PERCENT => DBMS_STATS.AUTO_SAMPLE_SIZE, METHOD_OPT => 'FOR ALL COLUMNS SIZE AUTO' -- 为所有 列自动选择大小 ); V_COUNT := V_COUNT +1 ; END LOOP; DBMS_OUTPUT.PUT_LINE('共分析' || V_COUNT ||'个表。' ); END ; / ``` -- 分析 表 (ORACLE 11G及上版本) DECLARE CURSOR C1 IS select OBJ.OWNER, OBJ.OBJECT_NAME , --- 'SELECT * FROM '|| OBJ.OWNER ||'.'||OBJ.OBJECT_NAME AS SQL_SCRIPT, OBJ.OBJECT_TYPE from sys.dba_objects obj where obj.owner = 'INFODBA' AND OBJ.OBJECT_TYPE = 'TABLE' ORDER BY OBJ.OBJECT_NAME ; V_COUNT NUMBER := 0 ; begin FOR R1 IN C1 LOOP DBMS_STATS.gather_table_stats( ownname => 'INFODBA', TABNAME => R1.OBJECT_NAME , -- 'PPOM_USER', CASCADE => TRUE, ESTIMATE_PERCENT => DBMS_STATS.AUTO_SAMPLE_SIZE, METHOD_OPT => 'FOR ALL COLUMNS SIZE AUTO' -- 为所有 列自动选择大小 ); V_COUNT := V_COUNT +1 ; END LOOP; DBMS_OUTPUT.PUT_LINE('共分析' || V_COUNT ||'个表。' ); END ; /
> 原文链接: https://www.cnblogs.com/samrv/p/18826033
