Query all views for specific text in Oracle database views

Question

I was wondering if anyone had a query that would search all views to find specific text. The database version we are on is Oracle Database 12c. This will only be run in our dev/test database.

I'm newer to the company, new to this database structure, and new to using Oracle. I've only used MSSQL in the past. I couldn't find a data dictionary and felt bad always having to ask what something meant or where it was located.

I was trying to investigate some before asking. I'm trying to learn what all the columns mean and where all the data is connected to. I'm open to other suggestions.

For SQL, I have one that searches through the views and columns for data and is rather fast. I don't have an exact time. But, I thought it would be similar to running it in Oracle unless the database is a little different to where maybe running something like that won't return as quick. I found some queries for Oracle that search all tables, but I don't have access to any of the tables. How we have been given access is going through: other users > users > views > then query on that view.

I found this link that I thought might work - Oracle Search all tables all columns for string

When I run the first query in the accepted answer I get this error:

Error report -ORA-00932: inconsistent datatypes: expected - got CHAR
ORA-06512: at line 6
00932. 00000 - "inconsistent datatypes: expected %s got %s"
*Cause:
*Action:"` 

The string that I am searching for contains numbers and letters. Ex. 123ABC

When I run the second query, I let it run for four hours and still nothing returned. Is there anyway to speed that one up?

I'm open to any other queries, suggestions, and help of pointing me in the right direction.

Thank you!

Answer 1

You have to understand that searching all CHAR (and its variations) datatype columns (as "123ABC" is a string) within the whole database is a tedious and a long time running process. It takes no time in a few relatively small tables; but, on a large database, it really takes a long time. You can't use any indexes, so ... be patient.

Also, note that code (behind that link) searches through ALL_TAB_COLUMNS view which contains not only your tables' columns (owned by you), but everything you have access to, and that contains various users. Have a look; that's my 11gXE database on a laptop:

SQL> select owner, count(*) from all_tab_columns group by owner;

OWNER                            COUNT(*)
------------------------------ ----------
MDSYS                                 736
CTXSYS                                320
SYSTEM                                 54
APEX_040000                          3327
SCOTT                                  69
XDB                                    35
SYS                                 15211

7 rows selected.

SQL> select count(*) from user_tab_columns;

  COUNT(*)
----------
        69

SQL>

See the difference? Using ALL_TAB_COLUMNS, you're searching through ~20.000 columns. In my own schema (and USER_TAB_COLUMNS), that's only 70 of them.

Therefore, consider switching to USER_TAB_COLUMNS (if you do, remove all OWNER column references).

---

PL/SQL procedure (in this case (regarding code you took from the question whose link you posted), an anonymous PL/SQL block) won't display anything until it is over.

Alternatively, you could create a "log" table, an autonomous transaction stored procedure (so that you could insert into the log table and commit) so that you'd then "trace" execution from another session. Something like this:

Log table and procedure:

SQL> create table search_log (table_name varchar2(30), column_name varchar2(30));

Table created.

SQL> create or replace procedure p_log (par_table_name in varchar2,
  2                                     par_column_name in varchar2)
  3  is
  4    pragma autonomous_transaction;
  5  begin
  6    insert into search_log (table_name, column_name)
  7      values (par_table_name, par_column_name);
  8    commit;
  9  end;
 10  /

Procedure created.

Code from the link you posted; switched to USER_TAB_COLUMNS, searching for table/column that contains the 'KING' string:

SQL> DECLARE
  2    match_count integer;
  3    v_search_string varchar2(4000) := 'KING';
  4  BEGIN
  5    FOR t IN (SELECT table_name, column_name
  6              FROM user_tab_columns
  7              WHERE data_type like '%CHAR%'
  8             )
  9    LOOP
 10      EXECUTE IMMEDIATE
 11        'SELECT COUNT(*) FROM '|| t.table_name||
 12        ' WHERE '||t.column_name||' = :1'
 13         INTO match_count
 14        USING v_search_string;
 15      IF match_count > 0 THEN
 16        --dbms_output.put_line( t.owner || '.' || t.table_name ||' '||t.column_name||' '||match_count );
 17        p_log(t.table_name, t.column_name);
 18      END IF;
 19    END LOOP;
 20  END;
 21  /

PL/SQL procedure successfully completed.

SQL> select * From search_log;

TABLE_NAME                     COLUMN_NAME
------------------------------ ------------------------------
EMP                            ENAME

SQL>

Only one table found; EMP and its ENAME column.