搜索Oracle DDL中的关键字

如果要导出DD用户下的所有定义，那么在SQL*Plus中代码如下所示：

SET PAGESIZE 0

SET TRIMSPOOL ON

SET LINESIZE 10000

SET LONG 90000

SET FEEDBACK OFF

SET FEED OFF

SET ECHO OFF

SPOOL /tmp/schema_dd.sql

SELECT CASE

         WHEN U.OBJECT_TYPE IN

              ('PROCEDURE', 'FUNCTION','PACKAGE', 'TRIGGER') THEN

          DBMS_METADATA.GET_DDL(U.OBJECT_TYPE, U.OBJECT_NAME, 'DD') ||

          CHR(10) || '/'

         ELSE

          DBMS_METADATA.GET_DDL(U.OBJECT_TYPE, U.OBJECT_NAME, 'DD')||

          CHR(10) || ';'

       END AS SCOTT_DDL

  FROM USER_OBJECTS U

 WHERE U.OBJECT_TYPE IN

       ('PROCEDURE','FUNCTION','PACKAGE','TRIGGER')

       AND U.OBJECT_NAME not like 'BIN%';

SPOOL OFF;

则可以导出DD用户下所有的DDL语句到/tmp/schema_dd.sql文件中。

找DDL中的关键字

fgrep 999 * | grep -v 999999

fgrep 9999 * | grep -v 999999

fgrep CN30006 * | grep -v CN300006

fgrep CN3000006 * | grep -v CN300006

另外一个更简单的找关键字的方法

select * from dba_source t where OWNER='DD' and upper(text) like '%999%' and upper(text) not like '%999999%';

select * from dba_source t where OWNER='DD' and upper(text) like '%CN30006%' and upper(text) not like '%CN300006%';

select * from dba_source t where OWNER='DD' and upper(text) like '%CN3000006%' and upper(text) not like '%CN300006%';