求条查询语句,该怎么处理_Oracle管理

求条查询语句
select table_name from user_tab_cols where column_name='test';
这样查出某用户所有tabel里有test列的tabel！
但我很多个tabel里都有test这个列，我要查出所有tabel里test列的某一条数据，和在那一个tabel，具体怎么写。。。???

------解决方案--------------------

SQL code

declare sqlText varchar2(100);countNum number;type tableNameType is table of varchar2(100) index by binary_integer;tableName tableNameType;begin select distinct table_name bulk collect into tableName from user_tab_cols where column_name='USA';dbms_output.put_line(tableName.count());for v_index in 1..tableName.count() loopsqlText:= 'select count(*) from ' ||tableName(v_index)|| ' where usa=to_char(2)';execute immediate sqlText into countNum  ;if(countNum>0) thendbms_output.put_line(tableName(v_index));end if ;end loop;end;
------解决方案--------------------
-- Created on 2012/2/15 by ADMINISTRATOR  
Declare
 colnm Constant Varchar2(10) := 'VEHICLE_ID'; --你要找的字段名
 colv     Varchar2(20) := '82'; --你要找的值
 isExists Integer;
Begin
 For r_up In (Select table_name From user_tab_cols Where column_name = colnm) Loop
   Execute Immediate 'select case when exists (select ' || colnm || ' from ' ||
                     r_up.table_name ||
                     ') then 1 else 0 end isExists from dual'
     Into isExists;
   If isExists = 1 Then
     --输出所有符合条件的表
     dbms_output.put_line(r_up.table_name);
   End If;
 End Loop;
End;