Search all views and Materialized Views by part of text - Oracle Apps Views

Views:

Step1: Create a Temporary Table based on ALL_VIEWS View.

create table search_all_views as
select  av.owner, av.view_name, to_lob(text) as text_clob
from    ALL_VIEWS av;

Step2: Search based on Part of text based on Temporary table created in Step1. 

select * from search_all_views where text_clob like '%Enter Text Here%'

Step3) Drop the Temporary Table created in Step1.

drop table search_all_views;

Materialized Views:

Step1: Create a Temporary Table based on ALL_MVIEWS View.

create table search_all_mviews as
select  av.owner, av.mview_name, to_lob(query) as text_clob
from    ALL_MVIEWS av;

Step2: Search based on Part of text based on Temporary table created in Step1. 
select * from search_all_mviews where text_clob like '%xwii_osp_util_pk.get_osp_name_locator%'

Step3) Drop the Temporary Table created in Step1.
drop table search_all_mviews