in a query to a table or to a view can we identify if a column is being used ? and based on that change the where clause ?
You can find references to a table in all the recently run SQL with a query like:
select * from v$sql
where upper ( sql_text ) like '%TABLE_NAME%'
and upper ( sql_text ) like '%COLUMN_NAME%';
And you can find where it's used in PL/SQL code with a query like:
select * from dba_statements
where text like '%TABLE_NAME%'
and text like '%COLUMN_NAME%';
Note - you need to be on 12.2 or higher and have compiled the code with these settings first:
ALTER SESSION SET plscope_settings='identifiers:all, statements:all'
These will give false positives - statements that include the table and column name but reference a different table/column or just have it in comments. It also won't show you references if the column is accessed via a view. You can find views that use it with:
select * from dba_views
where upper ( text_vc ) like '%TABLE_NAME%'
and upper ( text_vc ) like '%COLUMN_NAME%';
These methods will also overlook rarely run instances in application code, scripts, etc.
and based on that change the where clause ? Change how and why?