Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Searching all fields in an Oracle database.

Posted on 2015-02-01
6
Medium Priority
?
194 Views
Last Modified: 2015-03-02
I am working with a rather large Oracle database with thousands of tables.  This is used for a Windows based application.  Unfortunately the original developer is gone and my client ask for me to pull data out of the database.

As of now, I am trying to find the tables that contain specific pieces of data.  I am attempting to do this based on values that I know exist in the database.  I know these values are not generated by some type of formula or in a blob field.  I am using
Oracle SQL Developer for this.

I have an extensive background in SQL, but not Oracle.  Any suggestions regarding running a query to look for a column with one of the known values would be greatly appreciated.
0
Comment
Question by:rye004
6 Comments
 
LVL 22

Accepted Solution

by:
Helena Marková earned 2000 total points
ID: 40583500
This is one tip:
there is  a system view sys.all_tab_cols in Oracle (
here you can see more:
https://docs.oracle.com/cd/E11882_01/server.112/e40402/toc.htm
https://docs.oracle.com/cd/E11882_01/server.112/e40402/statviews_2102.htm#REFRN20276
the registration is free)
You can use this view for searching through the database.
 But it is no so easy to write a generic procedure.

Here is an inspiration:
declare
  n_exist PLS_INTEGER;
  n_search_number number:=55;
begin
for item in (select a.column_name,a.data_type from sys.all_tab_cols a where a.table_name = 'YOUR_TABLE' and a.owner='YOUR_OWNER') loop
    if item.data_type='NUMBER' then
        execute immediate 'select count(*) from dual where exists (select null from your_owner.your_table where '||item.column_name||'='||to_char(n_search_number)||')' into n_exist;
    end if;
    dbms_output.put_line('Column ' ||item.column_name||' value '||n_exist);
end loop;
end;
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 40583809
SELECT table_name, column_name,
       TO_NUMBER(
           EXTRACTVALUE(
               xmltype(
                   DBMS_XMLGEN.
                    getxml(
                          'select count(*) X from '
                       || table_name
                       || ' where '
                       || column_name
                       || ' = ''your known value'''
                   )
               ),
               '/ROWSET/ROW/X'
           )
       )
           COUNT
FROM all_tab_cols
WHERE data_type = 'VARCHAR2'  -- change this to whatever is appropriate
0
 
LVL 35

Expert Comment

by:Mark Geerlings
ID: 40583984
To do exactly what you asked for in Oracle (search all tables for a particular value in a column) is certainly not easy.  And, depending on the size of your database and on the size and speed of your server and storage system, running a query like this may take some minutes,  hours, days or weeks.

As a couple examples from others demonstrate, yes, it is possible in Oracle to use "dynamic SQL" in Oracle stored procedures.  (That is: use a query to construct a list of table and column names, then construct and process a query for each table and column combination.)  But, Oracle stored procedures are *NOT* optimized for processing "dynamic SQL" like this in stored procedures.  This is one of the significant differences between SQL Server and Oracle: Oracle stored procedures *ARE* optimized for static SQL statements, that is: SQL statements where the table and column names are known at compile time, and only the bind values (in "where" clauses or "insert ... values (...)" statements) are changed each time.

Also, this PL\SQL command in Oracle: "dbms_output.put_line..." is legal, but you usually have to explicitly execute a command like one of these first in order to see that output:
set serveroutput on;
exec dbms_output.enable(1000000);

It may be faster to use Oracle's export (or DataPump export) utility to extract the contents of some of the tables to (mostly) text files, then use an O/S search utility to check for the character string(s) you are interested in.  That may help you identify the table name(s), but that won't help you yet to identify the column(s).  You may then need to use a "dynamic SQL" technique to search the columns, but if you at least have the table (or afew tables) identified, that should dramatically reduce the amount of time required.
0
 

Author Comment

by:rye004
ID: 40584265
Thank you everyone for your input.  I am reviewing your suggestions now.

Markgeer, Oracle SQL Developer has a “Database Export” feature which I am trying now.
0
 
LVL 2

Expert Comment

by:Rob Jurd (eenookami)
ID: 40640749
I've requested that this question be deleted for the following reason:

Author has found a workaround (http://www.experts-exchange.com/R_42492.html)
0

Featured Post

Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.
Suggested Courses

926 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question