SQL 存储过程中的 Oracle 表使用情况

Oracle Table Usage Across Stored Procedures

提问by GrumP

I have a 11G database. I need to examine a number of stored procedures to see if they use a particular table. (Both front end and back end sps) I have full access to the database, and I also have a copy of all the individual sps for the project which are stored on a TFS.

我有一个11G的数据库。我需要检查许多存储过程以查看它们是否使用特定的表。（前端和后端 sps）我拥有对数据库的完全访问权限，并且我还拥有存储在 TFS 上的项目的所有单个 sps 的副本。

I would like a way to generate a list of all the sps that interact with this particular table. I'm unfamiliar with how to go about searching for these. Can anyone advise the most logical way of obtaining this data?

我想要一种方法来生成与此特定表交互的所有 sps 的列表。我不熟悉如何去搜索这些。任何人都可以建议获取这些数据的最合乎逻辑的方法吗？

Thanks.

谢谢。

回答by Incognito

If I understand this correctly, you're trying to search for occurrence of a tablein all stored procs. In that case, you can use this query:

如果我理解正确，您将尝试table在所有存储的过程中搜索 a 的出现。在这种情况下，您可以使用以下查询：

When searching for occurrences of SPin your schema

SP在您的架构中搜索 的出现时

SELECT * FROM user_source WHERE text LIKE '%tab_name%';

When searching for occurrences of SPin all schemas

SP在所有模式中搜索 的出现时

SELECT * FROM all_source WHERE text LIKE '%tab_name%';

回答by Plouf

Two things, in PL/SQL there are some changes which will require the recompilation of pl/sql object, other don't. To see the first one, you have the ALL_DEPENDENCIESview. Or DBA_ if you prefer.

两件事，在 PL/SQL 中有一些更改需要重新编译 pl/sql 对象，其他则不需要。要查看第一个，您就有了ALL_DEPENDENCIES视图。或者 DBA_ 如果您愿意。

If you just want to see where the table name appears in all the pl/sql code, whether a change to the table will require recompilation or not, you can use ALL_SOURCEusing a upper and %, but it might take some time.

如果您只想查看表名在所有 pl/sql 代码中出现的位置，无论对表的更改是否需要重新编译，您可以使用ALL_SOURCE上位和 %，但这可能需要一些时间。

回答by GolezTrol

I use PLSQL Developer, in which you can browse to a table (or other object), and view 'Referenced by', to see all objects that refer to the table. That's about as easy as it gets. I can imagine other tools have similar features.

我使用 PLSQL Developer，您可以在其中浏览到一个表（或其他对象），并查看“引用者”，以查看引用该表的所有对象。这很容易。我可以想象其他工具也有类似的功能。

I don't know if this pre-parsed information is readily available in Oracle, but I can imagine so, since those tools seem to work pretty fast.

我不知道这个预先解析的信息是否在 Oracle 中很容易获得，但我可以想象，因为这些工具似乎工作得非常快。

This information is available in the viewAll_DEPENDENCIES, which these tools probably use.

此信息可在All_DEPENDENCIES这些工具可能使用的视图中找到。

The source of stored procedures can be found in the USER_SOURCE(or ALL_SOURCE) view, in which the structure of the entire database is stored. Nevertheless, fetching and parsing the code from there would be quite cumbersome.

存储过程的来源可以在USER_SOURCE（或ALL_SOURCE）视图中找到，其中存储了整个数据库的结构。然而，从那里获取和解析代码会非常麻烦。

回答by Sunil Soceite Generale

Here is snippet I wrote to perform impact analysis (ONLY MERGE, INSERT and UPDATE) for a given @schema (upper case only) and @table (upper case only). It will return all the procedure name, procedure code, from line number and to line number along with other details. It can be easily used to include functions objects as well instead of package. am working on a utility that can run across all schema or selected schema (that will include SELECT rows as well). Though this will be good enough for you to start working.

这是我编写的代码片段，用于对给定的 @schema（仅限大写）和 @table（仅限大写）执行影响分析（仅合并、插入和更新）。它将返回所有过程名称、过程代码、从行号到行号以及其他详细信息。它也可以很容易地用于包含函数对象而不是包。我正在开发一个可以跨所有模式或选定模式运行的实用程序（也将包括 SELECT 行）。尽管这足以让您开始工作。

I know you can use the dependency and references available in Oracle to perform similarly. But for package level impact this is good addition. We can also use regex for more complex searches. But like operator is simple and efficient for my needs.

我知道您可以使用 Oracle 中可用的依赖项和引用来执行类似的操作。但是对于包级别的影响，这是一个很好的补充。我们还可以使用正则表达式进行更复杂的搜索。但是像操作符对于我的需求来说既简单又高效。

Note, this doesn't work on any dynamic code that may be working in your environment. This is just a appropriate starting point for quick one on impact with static PL/SQL code in your packages.

请注意，这不适用于可能在您的环境中工作的任何动态代码。这只是一个合适的起点，可以快速影响包中的静态 PL/SQL 代码。

WITH TableDep as
-- This table returns references where the table is used within the code for UPDATE OR INSERT
(
SELECT 
owner as schemaname,
name as packagename, 
type as typename,
TEXT as refcodeline,
CASE WHEN upper(text) LIKE '%INSERT%' THEN 'INSERT'
     WHEN upper(text) LIKE '%UPDATE%' THEN 'UPDATE'
   WHEN upper(text) LIKE '%MERGE%' THEN 'MERGE'
END AS opr,
:Tablename AS Tablename,
line refline
FROM dba_source WHERE upper(owner) = upper(:OWNER)  
      AND type = 'PACKAGE BODY' 
      AND (
            upper(text) LIKE ('%INSERT INTO '||:Tablename||'%')
            OR 
            upper(text) LIKE ('%UPDATE%'||:Tablename||' %')
            OR
            upper(text) LIKE ('%MERGE%'||:Tablename||' %')
           )
  ),
ProcedureDetails as
-- This code build all procedures within the package for references that is found in above query
(
SELECT 
  owner as schemaname,
  name as packagename, 
  type as typename,
  TEXT,
  trim(REGEXP_SUBSTR(TEXT, '(PROCEDURE [[:print:]]+)\(',1,1,null,1))   as procedure_name,
  line startline,
  LEAD(line, 1) OVER (partition by name order by line)-1 as endline
FROM dba_source
WHERE owner = upper(:OWNER) 
      AND type = 'PACKAGE BODY' 
      AND upper(text) LIKE '%PROCEDURE%(%'
      and exists (SELECt 1 FROM TableDep WHERE TableDep.packagename=name)
)
,ProcCode as
-- This code builds procedures into one cell per program for a given package. Later to find the  effected procedures
(
SELECT 
       ProcTag.packagename ,
       ProcTag.schemaname,
       ProcTag.typename,
       ProcTag.PROCEDURE_NAME,
       ProcTag.startline,
       ProcTag.endline,
       TO_CLOB(rtrim(xmlagg(xmlelement(e,codeline.text).extract('//text()') order by line).GetClobVal(),',')) as Procedure_Code
FROM
    ProcedureDetails ProcTag
    INNER JOIN dba_source codeline ON ProcTag.packagename=codeline.name
                                      AND ProcTag.schemaname=codeline.owner
                                      and ProcTag.typename=codeline.type
                                      and codeline.line between ProcTag.startline and ProcTag.endline
--WHERE PROCEDURE_NAME='PROCEDURE TRANS_KAT_INSO'
    group by 
       ProcTag.packagename ,
       ProcTag.schemaname,
       ProcTag.typename,
       ProcTag.PROCEDURE_NAME,
       ProcTag.startline,
       ProcTag.endline
      )
-- extract all the reference code for the given table selected with it complete procedure code.
SELECT
ProcHeader.Packagename, ProcHeader.schemaname, ProcHeader.typename, ProcHeader.procedure_name, ProcHeader.Procedure_Code ,ProcHeader.startline,ProcHeader.endline,ProcReference.Tablename, ProcReference.opr
FROM 
  ProcCode ProcHeader
  INNER JOIN 
        (
          SELECT DISTINCT ProcCode.Packagename, ProcCode.schemaname, ProcCode.typename, ProcCode.procedure_name , TableDep.Tablename, TableDep.opr
          FROM               ProcCode 
                  INNER JOIN TableDep ON ProcCode.packagename=TableDep.packagename
                                            AND ProcCode.schemaname=TableDep.schemaname
                                            and ProcCode.typename=TableDep.typename
                                            and TableDep.refline between ProcCode.startline and ProcCode.endline
        ) ProcReference
    ON    ProcHeader.Packagename=ProcReference.Packagename
          AND ProcHeader.schemaname=ProcReference.schemaname
          AND ProcHeader.typename=ProcReference.typename
          AND ProcHeader.procedure_name=ProcReference.procedure_name
;

回答by Arunprasanth K V

This question already have an accepted answer but anyhow the queryused inside the accepted answer will pick all the user sourceswhich uses the particular table.

这个问题已经有一个接受的答案，但无论如何，query在接受的答案user sources中使用的将选择所有使用特定表格的内容。

Because the question is specific about Proceduresyou can go for the below query to get the result

因为问题是具体的，Procedures您可以通过以下查询获得结果

SELECT * FROM user_source WHERE text LIKE '%YourTableName%' and TYPE='PROCEDURE';