ViewDefinitionSearch in Oracle[…

http://yong321.freeshell.org/oranotes/ViewDefinitionSearch.txt

See usage of sys.dbms_metadata_util.long2clob in catmeta.sqlQ: How do I find which view's definition contains a given string?A: Other than spooling dba_views.text to a file and read the file, you can build another table   based on dba_views converting the text column to a regular string type and query your table.   In fact, you can use this method to search in trigger code (dba_triggers.trigger_body) or   any long type column data.The following is run in an account with select any dictionary privilege in Oracle 10.2.0.1.SQL> create table myviewtable (  2  owner       varchar2(30),  3  view_name   varchar2(30),  4  text        varchar2(4000),  5  obj#        number);Table created.SQL> insert into myviewtable (text, obj#)  2  select sys.dbms_metadata_util.long2clob(1000000, 'VIEW$', 'TEXT', rowid), obj#  3  from sys.view$;3672 rows created.SQL> update myviewtable mvt  2  set (owner, view_name) =  3  (select u.name, o.name  4   from sys.user$ u, sys.obj$ o, sys.view$ v  5   where u.user# = o.owner# and o.obj# = v.obj# and mvt.obj# = v.obj#);3672 rows updated.--I don't need obj# column.SQL> alter table myviewtable drop column obj#;Table altered.SQL> select * from dba_views where lower(text) like '%x$kgl%';select * from dba_views where lower(text) like '%x$kgl%'                                    *ERROR at line 1:ORA-00932: inconsistent datatypes: expected NUMBER got LONGSQL> select * from myviewtable where lower(text) like '%x$kgl%';OWNER                          VIEW_NAME------------------------------ ------------------------------TEXT------------------------------------------------------------------------------SYS                            DBA_KGLLOCKselect kgllkuse, kgllkhdl, kgllkmod, kgllkreq, 'Lock' kgllktype from x$kgllk union all  select kglpnuse, kglpnhdl, kglpnmod, kglpnreq, 'Pin'  kgllktype from x$kglpnSYS                            DBA_LOCK_INTERNALselect...SQL> select owner, view_name from myviewtable where lower(text) like '�ncer%';OWNER                          VIEW_NAME------------------------------ ------------------------------TCS_OWNER                      VDEATHTCS_OWNER                      VPATHOLOGYLCDR_RESOURCE                  LCDR_DX_VW...SQL> select max(length(text)) from myviewtable;MAX(LENGTH(TEXT))-----------------            17214SQL> select owner, view_name, length(text) from myviewtable where length(text) > 12000 order by 3;OWNER                          VIEW_NAME                      LENGTH(TEXT)------------------------------ ------------------------------ ------------OLAPSYS                        ALL$OLAP2UENTITY_PARAMETERS           12872SYS                            ALL_IND_STATISTICS                    13163SYSMAN                         ORACLE_CURRENTDBINSTANCESTATS         13423SYS                            STRADDLING_TS_OBJECTS                 16214SYS                            STRADDLING_RS_OBJECTS                 17214