SLTechnology News&Howtos › Database ›

Search for keywords in Oracle DDL

Shulou Source: shulou.com Published: 2022-06-01 05:14:21 11月11日 Update

If you want to export all the definitions under the DD user, the code in SQL*Plus is as follows:

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

You can export all DDL statements under the DD user to the / tmp/schema_dd.sql file. Find keywords in DDL

Fgrep 999 * | grep-v 999999

Fgrep 9999 * | grep-v 999999

Fgrep CN30006 * | grep-v CN300006

Fgrep CN3000006 * | grep-v CN300006 another simpler way to find keywords

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%'