Search DBA_SOURCE for a given string and (optional) owner. In the example below a serach has been run for all occurrences of "select *" in schema OMEGA_ONE1.
SQL> @in_source
Purpose : Searches DBA_SOURCE for a text string
Enter # for current user
Use % to activate like clause
Owner : OMEGA_ONE1
Search for : select *
OWNER NAME TYPE LINE TEXT
------------------------------ ------------------------------ ------------ ---------- ----------------------------------------------------------------------------------------------------
OMEGA_ONE1 PR_PURGE_ARCH_DMP_AUDIT PROCEDURE 46 select * from COMPET_ALT_FLT_DETAIL_LOG where PNR_ITEM_KEY = XI_DMP_UKI_DTL_LOG(I).PNR_ITEM_KEY;
OMEGA_ONE1 PR_PURGE_ARCH_DMP_AUDIT PROCEDURE 58 select * from COMPET_CUST_EMAIL_ADDR_LOG where CUST_ITEM_KEY = XI_CUST_DTL_LOG(J);
OMEGA_ONE1 PR_PURGE_ARCH_DMP_AUDIT PROCEDURE 64 select * from COMPET_CUST_MOB_NO_LOG where CUST_ITEM_KEY = XI_CUST_DTL_LOG(J);
OMEGA_ONE1 PR_PURGE_ARCH_DMP_AUDIT PROCEDURE 72 select * from COMPET_CUST_DETAIL_LOG where PNR_ITEM_KEY = XI_DMP_UKI_DTL_LOG(I).PNR_ITEM_KEY;
OMEGA_ONE1 PR_PURGE_ARCH_DMP_AUDIT PROCEDURE 80 select * from COMPET_UKI_DETAIL_LOG where TRUNC(CREATE_DT) < TRUNC(ADD_MONTHS(SYSDATE,-V_HOLDING_M
ONTH));
OMEGA_ONE1 PR_PURGE_TEST PROCEDURE 46 select * from test_B where emp_no = XI_test_A_DEL(I).emp_no;
OMEGA_ONE1 PR_PURGE_TEST PROCEDURE 58 select * from test_E where trainee_id = XI_TEST_C_LOG(J);
OMEGA_ONE1 PR_PURGE_TEST PROCEDURE 64 select * from test_D where trainee_id = XI_TEST_C_LOG(J);
OMEGA_ONE1 PR_PURGE_TEST PROCEDURE 72 select * from test_C where emp_no = XI_test_A_DEL(I).emp_no;
OMEGA_ONE1 PR_PURGE_TEST PROCEDURE 80 select * from test_A where TRUNC(date_of_joining) < TRUNC(ADD_MONTHS(SYSDATE,-V_HOLDING_MNTH));
10 rows selected.
-- Name : in_source.sql
prompt
prompt Purpose : Searches DBA_SOURCE for a text string
prompt
-- Date last modified : 2015/09/29
-- Oracle Version: 11g
-- Other Oracle versions: should work on 9i and later
----------------------------------------------------------------------------------------
col text for a100
set verify off;
prompt
prompt Enter # for current user
prompt
prompt Use % to activate like clause
prompt
accept x_owner prompt 'Owner : '
prompt
accept S_STRING char prompt 'Search for : '
select unique owner, name, type, line, text
from dba_source
where ( '&x_owner' = '#' and owner = user
or '&x_owner' is null
or owner like upper( '&x_owner' )
)
and upper( text ) like upper( '%&&s_string%' )
order by owner, name, type
/