oracle中，得到所有的表的列，以及全库空间查找

oracle还没研究明白。这两天，需要分析Winchill的数据库的表间的关联关系。

所以，一直用了一个查询，类似这样：

--create or replace
--PROCEDURE PROCEDURE1HYJ AS 
--SET SERVEROUTPUT ON SIZE 100000

DECLARE
  match_count INTEGER;
  --match_count := 0;
-- Type the owner of the tables you are looking at
  v_owner VARCHAR2(255) :='PDM8';

-- Type the data type you are look at (in CAPITAL)
-- VARCHAR2, NUMBER, etc.
  v_data_type VARCHAR2(255) :='VARCHAR2';

-- Type the string you are looking at
 v_search_string VARCHAR2(4000) :='wt.workflow.engine.WfProcess';

BEGIN

  FOR t IN (SELECT table_name, column_name FROM all_tab_cols where owner=v_owner and data_type = v_data_type AND table_name like 'WT%') LOOP


    EXECUTE IMMEDIATE 
    'SELECT COUNT(*) FROM '||t.table_name||' WHERE '||t.column_name||' like :1'
    INTO match_count
    USING v_search_string;

--dbms_output.put_line( t.table_name ||' '||t.column_name||' '||match_count );

    IF match_count > 0 THEN
      dbms_output.put_line( '[ee]' || t.table_name ||' '||t.column_name||' '||match_count );
    END IF;

  END LOOP;
end;

但是，时常会出错。

经过本人就坚苦的分区停电法，终于找到，原来all_tab_cols包括视图。

而视图在这个查询就会出错。

所以，自己建了一个视图，存这些columns，并且把不属于表的，去除掉。

然后再指向这个视图，见下：

--create or replace
--PROCEDURE PROCEDURE1HYJ AS 
--SET SERVEROUTPUT ON SIZE 100000

DECLARE
  match_count INTEGER;
  --match_count := 0;
-- Type the owner of the tables you are looking at
  v_owner VARCHAR2(255) :='PDM8';

-- Type the data type you are look at (in CAPITAL)
-- VARCHAR2, NUMBER, etc.
  --v_data_type VARCHAR2(255) :='VARCHAR2';
  v_data_type VARCHAR2(255) :='NUMBER';

	v_search_string VARCHAR2(4000) :='37137037';
BEGIN
--AND table_name like 'F%'owner=v_owner and AND (table_name like 'V%' )
  FOR t IN (SELECT table_name, column_name FROM PDM8."HaoAllColumnNoView" where  data_type = v_data_type ) LOOP



    EXECUTE IMMEDIATE 
    'SELECT COUNT(*) FROM '||t.table_name||' WHERE '||t.column_name||' = :1'
    INTO match_count
    USING v_search_string;

--dbms_output.put_line( t.table_name ||' '||t.column_name||' '||match_count );

    IF match_count > 0 THEN
      dbms_output.put_line( '[ee]' || t.table_name ||' '||t.column_name||' '||match_count );
    END IF;

  END LOOP;
end;

这个视图就不贴了，

就是all_tab_cols与 all_tables的TABLE_NAME，进行inner join，用owner进行过滤。只选当前表空间的。

这样这个查询就OK了。

顺便指出，all_tables所在的位置：

【模式】--sys--视图下面。