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 ;
/