Oracle DB: SQL to search in all Users Views

If you need to find some text, that is used in some View of your Oracle Database. You can use the code below.

CREATE TABLE TEMP_VIEWS_SOURCE (
  view_name VARCHAR2(30),
  text CLOB
);
 
BEGIN
  FOR v IN (SELECT view_name, text FROM user_views) LOOP
    INSERT INTO TEMP_VIEWS_SOURCE VALUES (v.view_name, v.text);
  END LOOP;
  COMMIT;
END;
/
 
SELECT * FROM TEMP_VIEWS_SOURCE WHERE instr(upper(text), 'SOME TEXT') > 0;

Don't forget to delete temp table (Purge will release space, that was used by table).

DROP TABLE TEMP_VIEWS_SOURCE PURGE;