需求：举个例子，oracle测试库的scott用户下面的有张emp表，emp表的ename列中有一行数据为'CLARK'。红色标记部分。
SQL> select * from scott.emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 800 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7566 JONES MANAGER 7839 02-APR-81 2975 20
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7788 SCOTT ANALYST 7566 19-APR-87 3000 20
7839 KING PRESIDENT 17-NOV-81 5000 10
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7876 ADAMS CLERK 7788 23-MAY-87 1100 20
7900 JAMES CLERK 7698 03-DEC-81 950 30
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7934 MILLER CLERK 7782 23-JAN-82 1300 10
可是你忘记了'CLARK'这个字符在哪张表里了，如何找出来？
下面是plsql代码
set serveroutput on
declare
v_sql varchar2(2000);
v_count number;
begin
for cur in(select t.owner, t.table_name, t.column_name from dba_tab_columns t where t.owner = 'SCOTT')
loop
begin v_sql := 'select count(1) from ' || cur.owner || '.' || cur.table_name || ' where ' || cur.column_name || ' like ''%CLARK%''';
execute immediate v_sql into v_count;
if(v_count >= 1) then
dbms_output.put_line(cur.owner || ':' || cur.table_name || ':' || cur.column_name);
end if;
exception
when others then
null;
end;
end loop;
end;
/
说明：
# 如果你不确定表是哪个用户的，就把where t.owner = 'SCOTT'去掉进行全库搜索，但是效率会很低
# 在plsqldeveloper command window中执行上面代码
结果就是CLARK出现在scott用户下的emp表的ename列中
