[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 12704
  • Last Modified:

Search for a string across all tables in oracle

I have a string abc, i want to find all tables in oracle that contains this string.
Is there any metadata table that i can reference for this information?
0
gram77
Asked:
gram77
  • 7
  • 4
  • 3
  • +1
1 Solution
 
Javier MoralesOracle DBACommented:
you'll have to user dynamic sql to query that:

1.- query all columns that contain text values
2.- build a PL/SQL code to search in all of them for the string requested.

Something like:

declare
    num_rows number;
    sql_text varchar2(250);
    sql_info varchar2(100);
begin
    dbms_output.enable(1000000);
    for x in (select table_name, column_name from dba_tab_columns
               where data_type in ('VARCHAR','VARCHAR2','CHAR')
                 and owner='SYSTEM')
    loop
         sql_text:='select count(*) into :num_rows from SYSTEM.'||x.table_name||' where '||x.column_name||' like ''%A%''';
         -- dbms_output.put_line (sql_text);
         execute immediate sql_text into num_rows;
         if num_rows>0 
         then 
              sql_info:='Table: '||x.table_name||' contains the string';
              dbms_output.put_line (sql_info);
         end if;
    end loop;
end;
/

Open in new window



Changing A for the string requested and SYSTEM for the schema being queried... Here's an example:

SQL> r
  1  declare
  2      num_rows number;
  3      sql_text varchar2(250);
  4      sql_info varchar2(100);
  5  begin
  6      dbms_output.enable(1000000);
  7      for x in (select table_name, column_name from dba_tab_columns
  8                 where data_type in ('VARCHAR','VARCHAR2','CHAR')
  9                   and owner='SYSTEM')
 10      loop
 11           sql_text:='select count(*) into :num_rows from SYSTEM.'||x.table_name||' where '||x.column_name||' like ''%A%''';
 12           -- dbms_output.put_line (sql_text);
 13           execute immediate sql_text into num_rows;
 14           if num_rows>0
 15           then
 16                sql_info:='Table: '||x.table_name||' contains the string';
 17                dbms_output.put_line (sql_info);
 18           end if;
 19      end loop;
 20* end;
Table: AQ$_INTERNET_AGENTS contains the string
Table: AQ$_INTERNET_AGENT_PRIVS contains the string
Table: AQ$_QUEUES contains the string
Table: AQ$_QUEUES contains the string
Table: AQ$_QUEUE_TABLES contains the string
Table: AQ$_QUEUE_TABLES contains the string
Table: AQ$_QUEUE_TABLES contains the string
Table: DEF$_DESTINATION contains the string
Table: HELP contains the string
Table: HELP contains the string
Table: LOGSTDBY$SKIP_SUPPORT contains the string
Table: MVIEW$_ADV_PARAMETERS contains the string
Table: REPCAT$_AUDIT_ATTRIBUTE contains the string
Table: REPCAT$_AUDIT_ATTRIBUTE contains the string
Table: REPCAT$_OBJECT_TYPES contains the string
Table: REPCAT$_RESOLUTION_METHOD contains the string
Table: REPCAT$_TEMPLATE_TYPES contains the string
Table: SYS_EXPORT_FULL_01 contains the string
Table: SYS_EXPORT_FULL_01 contains the string
Table: SYS_EXPORT_FULL_01 contains the string
Table: SYS_EXPORT_FULL_01 contains the string
Table: SYS_EXPORT_FULL_01 contains the string
Table: SYS_EXPORT_FULL_01 contains the string
Table: SYS_EXPORT_FULL_01 contains the string
Table: SYS_EXPORT_FULL_01 contains the string
Table: SYS_EXPORT_FULL_01 contains the string
Table: SYS_EXPORT_FULL_01 contains the string
Table: SYS_EXPORT_FULL_01 contains the string
Table: SYS_EXPORT_FULL_01 contains the string
Table: SYS_EXPORT_FULL_01 contains the string
Table: SYS_EXPORT_FULL_01 contains the string
Table: SYS_EXPORT_FULL_01 contains the string
Table: TEMP_JAVA_PRIVS contains the string
Table: TEMP_JAVA_PRIVS contains the string
Table: TEMP_JAVA_PRIVS contains the string
Table: TEMP_JAVA_PRIVS contains the string
Table: TEMP_JAVA_PRIVS contains the string

Open in new window

0
 
gram77Author Commented:
'select count(*) into :num_rows from PBSDDEV.'||x.table_name||' where '||x.column_name||' like ''A'''

I don't want to check column names that start with A, but column data that start with say an error: ORA-01428

I want to check all columns in all tables that contain ORA-01428
0
 
awking00Commented:
To find columns containing an error message -
... ' where instr('||x.column_name||',''ORA-'') > 0)'
To find columns beginning with an error message -
... ' where instr('||x.column_name||',''ORA-'') = 1)'
To find columns containing a specific error message -
... ' where instr('||x.column_name||',''ORA-01428'') > 0)'
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
Javier MoralesOracle DBACommented:
The code I sent you shows the tables that contain an "A" between the char columns as a value.

Please, replace "A" for "ORA-whatever"
It will find ALL the tables containing char columns and filtering values with the dynamic where clause.

declare
    num_rows number;
    sql_text varchar2(250);
    sql_info varchar2(100);
begin
    dbms_output.enable(1000000);
    for x in (select owner, table_name, column_name from dba_tab_columns
               where data_type in ('VARCHAR','VARCHAR2','CHAR'))
    loop
         sql_text:='select count(*) into :num_rows from '||x.owner||'.'||x.table_name||' where '||x.column_name||' like ''%ORA-01428%''';
         -- dbms_output.put_line (sql_text);
         execute immediate sql_text into num_rows;
         if num_rows>0 
         then 
              sql_info:='Table: '||x.owner||'.'||x.table_name||' contains the string ORA-01428';
              dbms_output.put_line (sql_info);
         end if;
    end loop;
end;
/

Open in new window


Well, just change the code to fit exactly what you need to! It's just a sample.

Cheers,
Javier
0
 
Mark GeerlingsDatabase AdministratorCommented:
"Is there any metadata table that i can reference for this information?"
No.

Using a dynamic query in a PL\SQL procedure something like what techlevel suggested could work to answer this question.  But, be aware that the performance of a procedure like this could be *VERY* slow.  This will depend on the size of your database and on a number of other factors including:
1. the amount of RAM in the server
2. the size of the Oracle SGA or the value for buffer_cache
3. the number of CPUs
4. your disk system
etc.

A procedure like this could easily run for hours or days.  Also, you most likely don't want it limited to just tables in the "SYSTEM" schema, but maybe you do know the schema(s) you want to search.
0
 
awking00Commented:
See attached.
comments.txt
0
 
gram77Author Commented:
so the query is:

select owner, table_name , column_name ...
from all_tab_columns
where column_name like '%ERR%' or column_name like '%MSG%'
0
 
awking00Commented:
Only you know your table definitions, so you may also have a column like
 '%MESSAGE%' or the like that you would also include in the where clause.
0
 
Javier MoralesOracle DBACommented:
Well, I see you accepted the solution that searches for columns with some text in the column name, not the string contained in the text values of the columns.

Your question suggested you requested something absolutely different.
0
 
awking00Commented:
Maybe I didn't make it clear but, what I was trying to get across is to limit the cursor query to select only such columns that might contain an error message and not all of the character type columns in the schema since that could be extremely poor performing. You would still need to loop through the columns in the cursor dynamically searching for the specified text value in those columns.
0
 
gram77Author Commented:
great!
0
 
awking00Commented:
See attached.
procedure.txt
0
 
awking00Commented:
See attached.
procedures.txt
0
 
gram77Author Commented:
This procedure only counts records from character columns
that will likely store error messages

declare
      v_cnt number := 0;
      v_tot number := 0;
      v_sql varchar2(255);
begin
      for rec in
            (select table_name, column_name
            from user_tab_columns
            where instr(data_type ,'CHAR') > 0
            and column_name like '%ERR%') <--I am not looking for those columns that are named : ERR
      loop
            v_sql := 'select count(*) from '||rec.table_name;
            execute immediate v_sql into v_cnt;
            v_tot := v_tot + v_cnt;
      end loop;
            dbms_output.put_line('Total count = '||to_char(v_tot));
end;
0
 
awking00Commented:
>>and column_name like '%ERR%') <--I am not looking for those columns that are named : ERR<<
 [sic] not named but like '%ERR%'
But I think you should be. Otherwise you will be needlessly looping through columns like firstname, lastname, address, city, state, and many others that will never contain an error message.
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 7
  • 4
  • 3
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now