oracle查找所有列,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--视图下面。