如何查找在Oracle中不被Package/Procedure/Function/Type/Trigger所引用的表

From them we can easily find what are the depending  tables and views of EVM Owned Package/Procedure/Function/Type/Trigger.

At the same time, can find these tables/views not directly used by EVM PLSQL Code.

 

 

Note:

Tables/Views are showedin evm_tables_not_used_in_plsqldo not mean they are useless.

For example,

They may be used only inDynamic SQL, in Java File.

 

 该视图用来查找在指定用户下被其它Package/Procedure/Function/Type/Trigger所引用的表：

CREATE OR REPLACE VIEW  evm_plsql_depending_tables AS 
SELECT *
  FROM dba_dependencies
WHERE owner IN ('OPS$APP', 'OPS$UTIL', 'OPS$DATA', 'OPS$CONFIG')
   AND TYPE IN ('PACKAGE', 'PACKAGE BODY', 'TRIGGER', 'PROCEDURE', 'FUNCTION',
        'TYPE', 'TYPE BODY')
   AND referenced_owner IN ('OPS$APP', 'OPS$UTIL', 'OPS$DATA', 'OPS$CONFIG')
   AND referenced_name IN
       (SELECT table_name
          FROM all_tables
         WHERE owner IN ('OPS$APP', 'OPS$UTIL', 'OPS$DATA', 'OPS$CONFIG'))
   AND referenced_type <> 'NON-EXISTENT';

该视图用来查找在指定用户下，没有被任何PACKAGE, TYPE, TRIGGER, PROCEDUREAND FUNCTION 所使用的表

 --all these tables do not be used within any PACKAGE, TYPE, TRIGGER, PROCEDUREAND FUNCTION

CREATE OR REPLACE VIEW  evm_tables_not_used_in_plsql AS 
 SELECT *
   FROM all_tables
  WHERE owner IN ('OPS$APP', 'OPS$UTIL', 'OPS$DATA', 'OPS$CONFIG')
    AND table_name NOT IN
        (SELECT referenced_name FROM evm_plsql_depending_tables);

Notes： 需要注意的是，在Java或者非Oracle自有程序或类型所使用的表在这两个视图中无法统计出来。也就是说，从这两个视图中查出来的无用表可能在程序中被用到，并不一定是无用的

因此，我们还是可以用查找工具再来确认一次：

find .-name '*.pkb' | xargs grep -is 'enumlist'
find .-name '*.pkg' | xargs grep -is 'enumlist'
 
Under JAVAdirectory:
find .-name '*.java' | xargs grep -is 'enumlist'