PLM:查找表不存在索引的表，生成创建索引脚本

环境：WINDOW 2008+ ORACLE 11G, 查找表未创建用户索引的表清单，并生成创建索引 脚本：

declare cursor  c_tab is 
-- 检查没有创建索引的表
select OBJ.OWNER, OBJ.OBJECT_NAME AS TABLE_NAME,
 'SELECT * FROM '|| OBJ.OWNER ||'.'||OBJ.OBJECT_NAME  ||' ;' AS SQL_SCRIPT
 from sys.dba_objects obj,
 SYS.DBA_TABLES  TAB
 where  obj.object_type = 'TABLE'
  AND OBJ.OWNER='INFODBA'
  and obj.object_name = tab.table_name 
  and tab.TEMPORARY = 'N' -- 非临时表
  AND NOT EXISTS ( SELECT 'X' FROM SYS.DBA_INDEXES IDX  WHERE IDX.OWNER = 'INFODBA'
   AND IDX.INDEX_NAME NOT LIKE 'SYS%'  AND IDX.TABLE_NAME = OBJ.OBJECT_NAME 
  )
order by OBJ.OWNER, OBJ.OBJECT_NAME ;

cursor c_col(p_table_name varchar2) is 
SELECT COL.COLUMN_NAME ,col.COLUMN_ID
 FROM SYS.DBA_TAB_COLS COL
 WHERE OWNER = 'INFODBA'
  AND COL.TABLE_NAME = p_table_name  --'IMAN_CURVEDATA'
  AND COL.DATA_TYPE IN ('NUMBER', 'VARCHAR2','DATE','DATETIME')
  and col.nullable ='N'
UNION 
SELECT COL.COLUMN_NAME ,col.COLUMN_ID
 FROM SYS.DBA_TAB_COLS COL
 WHERE OWNER = 'INFODBA'
  AND COL.TABLE_NAME = p_table_name  --'IMAN_CURVEDATA'
  AND COL.DATA_TYPE IN ('NUMBER', 'VARCHAR2','DATE','DATETIME')
  and col.nullable ='Y'  
 ;

V_COLUMN_NAME VARCHAR2(240);
V_COLUMN_ID NUMBER;
begin
  for r_tab in c_tab loop
     open c_col(r_tab.table_name) ;
       fetch c_col into V_COLUMN_NAME, V_COLUMN_ID; 
     CLOSE C_COL;   
      dbms_output.put_line('CREATE INDEX INFODBA.'||R_TAB.TABLE_NAME||'_N1  ON INFODBA.'||R_TAB.TABLE_NAME||'(' ||V_COLUMN_NAME ||') tablespace IDATA;' );
     
  end loop;  
end;