Oracle数据库搜索工具升级啦～

这次修改了一个bug，并用到了parallel能力，以提高性能，源代码如下：

CREATE OR REPLACE PROCEDURE P_DB_SEARCH_TOOL(TABLE_OWNER   VARCHAR2 DEFAULT '.*',
                                             COLUMN_TYPE   VARCHAR2,
                                             TARGET_STRING VARCHAR2) IS

  /************************************************************************************
   *                          Oracle search tool ( Version 1.0 )                      *
   *                                                                                  *
   * TABLE_OWNER   : The user that the tool will search.                              *
   *                 By default, search all the users(exclude system users) in the DB *
   * COLUMN_TYPE   : The target column type that will be searched (fuzzy match)       *
   * TARGET_STRING : The target string that will be searched (fuzzy match)            *
   *                                                                                  *
   * Note: Strongly suggest compile in user SYS, to avoid evitable troubles           *
   *                                                           By Jeans 2013-4-25     *
   ************************************************************************************/
  /************************************************************************************
   *                          Oracle search tool ( Version 1.1 )                      *
   *                                                                                  *
   * Add parallel hint, to improve performance                                        *
   * Exclude dropped tables in recyclebin                                             *
   *                                                           By Jeans 2013-5-29     *
   ************************************************************************************/

  /*----------------------------------------------------------------------------------------------------
    Demo:
      SET serveroutput ON;
      exec P_DB_SEARCH_TOOL(TABLE_OWNER => 'JEANS', COLUMN_TYPE => 'NVARCHAR', TARGET_STRING => '康');
  ----------------------------------------------------------------------------------------------------*/

  V_USER_EXIST_FLAG VARCHAR2(200);
  /*-----------------------------------------------------------
    Generate dynamic SQLs those will be used to search the DB
  -----------------------------------------------------------*/
  CURSOR CS IS
    SELECT 'SELECT /*+ PARALLEL*/ COUNT(1), '''  || T.OWNER       || '.'          ||
           T.TABLE_NAME       || ''', '''    || T.COLUMN_NAME || ''' FROM '   ||
           T.OWNER            || '."'        || T.TABLE_NAME  || '" WHERE '   ||
           T.COLUMN_NAME      || ' LIKE ''%' || TARGET_STRING ||  '%''' SQL_TEXT
      FROM DBA_TAB_COLS T, DBA_RECYCLEBIN DR
     WHERE T.TABLE_NAME = DR.OBJECT_NAME(+)
    /*--------------------------------------
      Exclude dropped tables in recyclebin
    --------------------------------------*/
       AND DR.OBJECT_NAME IS NULL
       AND REGEXP_LIKE(T.OWNER,     '^' || UPPER(TABLE_OWNER) || '$')
       AND REGEXP_LIKE(T.DATA_TYPE, UPPER(COLUMN_TYPE))
       AND T.OWNER
    /*----------------------
      Exclude system users
    ----------------------*/
    NOT IN ('OE', 'PM', 'BI',  'SH', 'IX',  'HR',  'SCOTT',   'OWBSYS_AUDIT', 'OWBSYS',
            'APEX_PUBLIC_USER',      'FLOWS_FILES',     'APEX_030200',     'MGMT_VIEW',
            'SYSMAN',    'SPATIAL_CSW_ADMIN_USR',  'SPATIAL_WFS_ADMIN_USR',   'MDDATA',
            'OLAPSYS', 'MDSYS', 'SI_INFORMTN_SCHEMA','ORDDATA', 'ORDSYS', 'ORDPLUGINS',
            'XS$NULL',  'ANONYMOUS',  'XDB',  'CTXSYS', 'EXFSYS', 'WMSYS', 'APPQOSSYS',
            'DBSNMP',    'ORACLE_OCM',     'DIP',     'OUTLN',     'SYS',     'SYSTEM');
  V_SQL         VARCHAR2(2000);
  ROW_CNT       NUMBER;
  TARGET_TABLE  VARCHAR2(2000);
  TARGET_COLUMN VARCHAR2(2000);
BEGIN
  /*--------------------------
    To avoid buffer overflow
  --------------------------*/
  DBMS_OUTPUT.ENABLE(90000000);
  /*----------------------------------------------------------
    SELECT SYS_CONTEXT('USERENV', 'SESSION_USER') FROM DUAL;
    Check current user
  ----------------------------------------------------------*/
  EXECUTE IMMEDIATE 'ALTER SESSION ENABLE PARALLEL DML';
  SELECT COUNT(1) INTO V_USER_EXIST_FLAG
    FROM DBA_USERS T
   WHERE REGEXP_LIKE(T.USERNAME, UPPER(TABLE_OWNER))
     AND T.USERNAME
  NOT IN ('OE', 'PM', 'BI',  'SH', 'IX',  'HR',  'SCOTT',   'OWBSYS_AUDIT', 'OWBSYS',
          'APEX_PUBLIC_USER',      'FLOWS_FILES',     'APEX_030200',     'MGMT_VIEW',
          'SYSMAN',    'SPATIAL_CSW_ADMIN_USR',  'SPATIAL_WFS_ADMIN_USR',   'MDDATA',
          'OLAPSYS', 'MDSYS', 'SI_INFORMTN_SCHEMA','ORDDATA', 'ORDSYS', 'ORDPLUGINS',
          'XS$NULL',  'ANONYMOUS',  'XDB',  'CTXSYS', 'EXFSYS', 'WMSYS', 'APPQOSSYS',
          'DBSNMP',    'ORACLE_OCM',     'DIP',     'OUTLN',     'SYS',     'SYSTEM');
  /*--------------------------------------------------
    To judge whether the given user exists in the DB
  --------------------------------------------------*/
  IF V_USER_EXIST_FLAG = 0 THEN
    DBMS_OUTPUT.PUT_LINE('User:' || TABLE_OWNER || ' does not exist!');
    RETURN;
  END IF;

  FOR V_SQL IN CS LOOP
    /*-------------------------------------------------------------------------------------------------
      Execute dynamic SQLs in a loop block, to search the DB and return all the corresponding results
    -------------------------------------------------------------------------------------------------*/
    BEGIN
      EXECUTE IMMEDIATE V_SQL.SQL_TEXT INTO ROW_CNT, TARGET_TABLE, TARGET_COLUMN;
      IF ROW_CNT > 0 THEN
        DBMS_OUTPUT.PUT_LINE(V_SQL.SQL_TEXT || ';');
        DBMS_OUTPUT.PUT_LINE('');
      END IF;
      /*IF ROW_CNT > 0 THEN
        DBMS_OUTPUT.PUT_LINE('Result: SELECT T.' || TARGET_COLUMN || ', T.*, T.ROWID FROM ' ||
                             TARGET_TABLE        || ' T WHERE T.' || TARGET_COLUMN          ||
                             ' LIKE ''%'         || TARGET_STRING || '%'';   '              ||
                             'Row count:'        || ROW_CNT );
      END IF;*/
    EXCEPTION
      WHEN OTHERS THEN
        NULL;
    END;
  END LOOP;
  RETURN;
END P_DB_SEARCH_TOOL;
/