如何查找oracle cursor中正在使用的表？

bf1o4zei  于 2023-10-16  发布在  Oracle
关注(0)|答案(2)|浏览(100)

我有一个包的列表，它们对应的过程和它们对应的游标和在这些游标中使用的属性。
有人能帮助我如何识别这些属性的对应表名吗？
我使用SQL Developer 19c作为Oracle版本。
谢谢

oracle

2条答案

按热度按时间
eanckbw9

eanckbw91#

在Oracle术语中，“游标”是一条准备好的SQL语句，在共享池中由sql_id标识。要查看游标所依赖的对象，假设您具有SELECT ANY DICTIONARY权限，您可以像这样查询其执行计划详细信息：

SELECT DISTINCT
       object_owner,
       object_name,
       object_node
  FROM v$sql_plan
 WHERE sql_id = 'c7d4p6r0xs00f' -- replace with your sql_id

现在要注意的是，如果它使用索引并从索引中获取所需的所有内容，并且实际上并没有访问下面的表，那么您将看到索引而不是表。因此，您可能希望在这种情况下增加查询以涵盖您自己：

SELECT DISTINCT
       object_owner,
       object_name,
       object_node,
       CASE WHEN (operation LIKE '%INDEX%') THEN (SELECT MAX(i.table_name)
                                                    FROM all_indexes i
                                                   WHERE i.index_name = sp.object_name
                                                     AND i.owner = sp.object_owner) 
       END index_table_name
  FROM v$sql_plan sp
 WHERE sql_id = 'c7d4p6r0xs00f'

你的问题也提到了包和过程。如果您需要知道这些对象依赖于什么对象，因为它们是编译代码，Oracle会记录它们的依赖关系，以便在其中一个依赖关系出错时使它们失效。您可以这样查询：

SELECT *
  FROM all_dependencies
 WHERE owner = 'MYSCHEMA'
   AND name = 'MYPACKAGE_OR_PROCEDURE_OR_FUNCTION_OR_WHATEVER'

这将省略的唯一一件事是你在动态SQL中使用的任何对象（例如在EXECUTE IMMEDIATE或OPEN FOR中，因为它们不会创建Oracle跟踪的依赖关系）。对于那些你必须运行代码，然后检查v$sql_plan。
也有可能“cursor”指的是PL/SQL创建的命名变量CURSOR或REF CURSOR（它们本身不是游标，而是引用游标的句柄）。如果是这样，并且它是静态定义的，而不是动态定义的，那么它的对象将在定义过程的依赖列表中（使用all_dependencies）。但是，如果它是动态定义的，那么最好的办法是添加一个唯一标识每个游标的提示，然后运行代码，然后可以查询v$sql，并使用SQL语句文本中的提示来查找游标，并使用v$sql_plan获取它们的对象，如上所示。
最后，您提到了“属性”，我猜您指的是结果集中的列。如果不手动从SQL或PL/SQL文本本身提取它，就不可能干净地获得这个列表。对于一个特别勇敢的人来说，一个值得研究的先进技术是查看计划的最外投影（应该是最低的id），但输出需要一些额外的工作来清理它，我还没有证明这一点，除了一些简单的测试：

SELECT *
  FROM (SELECT projection,
               ROW_NUMBER() OVER (PARTITION BY sql_id,child_number ORDER BY id) seq
          FROM v$sql_plan
         WHERE sql_id = 'd747aacbc0hhv'
           AND projection IS NOT NULL)
WHERE seq = 1
赞(0）分享  回复(0）举报  2023-10-16
jdzmm42g

jdzmm42g2#

你可以尝试使用下面的代码。到目前为止，它对我来说是相当有用的，可以与不同类型的对象（表，视图，包体...）一起工作。基本指令在代码中。如果我没说错的话，你需要找到一个包对象的依赖关系（不知道提到的属性），如果是这样的话，那么对于ObjectType参数put 'PACKAGE BODY'，你也需要提供对象所有者和对象名称。
下面是返回对象依赖关系的代码，最高可达6级：

--  -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--  Shows objects that given object depends on
--      
--          --------------------------------------------------------------------------------
--          NOTE :
--              The code uses recursions - so it is highly advisable to use it just for one object at a time to prevent infinite recursions 
--
--              Source is system table DBA_DEPENDENCIES to which you'd need to have access to
--              If not use ALL_DEPENDENCIES instead
--          --------------------------------------------------------------------------------
--
--  Code could be used in SQL> or Oracle Developer 
--  
--  Parameters are of type VarChar2 (They Are Not Case Sensitive) and should be provided within single quates:
--      like    -   OwnerName -> 'scott'        ObjectName -> 'my_view'     ObjectType -> 'VIEW'
--
--  Resulting format:   --->    [Object_Type] "ObjectName" (owner.OwnerName)    <---
--  -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
WITH
    obj AS
        (   Select &OwnerName "OWNED_BY", &ObjectName "OBJ_NAME", &ObjectType "OBJ_TYPE" From Dual  ), 
--  ---------------------------------------
    obj_deps_1 AS
        (   Select
                o.OBJ_NAME "OBJ_NAME",
                o.OBJ_TYPE "OBJ_TYPE",
                o.OWNED_BY "OBJ_OWNED_BY",
                --
                CASE WHEN d.REFERENCED_OWNER IN('SYS', 'PUBLIC') THEN Null ELSE d.REFERENCED_NAME END "REF_OBJ_NAME_1",
                CASE WHEN d.REFERENCED_OWNER IN('SYS', 'PUBLIC') THEN Null ELSE d.REFERENCED_TYPE END "REF_OBJ_TYPE_1",
                CASE WHEN d.REFERENCED_OWNER IN('SYS', 'PUBLIC') THEN Null ELSE d.REFERENCED_OWNER END "REF_OBJ_OWNED_BY_1"
                --
            From 
                obj o 
            Inner Join
                ALL_DEPENDENCIES d ON(d.OWNER = Upper(o.OWNED_BY) And d.NAME = Upper(o.OBJ_NAME) And d.TYPE = Upper(o.OBJ_TYPE))
        ),
    obj_deps_6 AS
        (
            SELECT DISTINCT
                o.OBJ_NAME "OBJ_NAME",
                o.OBJ_TYPE "OBJ_TYPE",
                o.OBJ_OWNED_BY "OBJ_OWNED_BY",
                --
                o.REF_OBJ_NAME_1 "REF_OBJ_NAME_1",
                o.REF_OBJ_TYPE_1 "REF_OBJ_TYPE_1",
                o.REF_OBJ_OWNED_BY_1 "REF_OBJ_OWNED_BY_1",
                --
                CASE WHEN d2.REFERENCED_OWNER IN('SYS', 'PUBLIC') THEN Null ELSE d2.REFERENCED_NAME END "REF_OBJ_NAME_2",
                CASE WHEN d2.REFERENCED_OWNER IN('SYS', 'PUBLIC') THEN Null ELSE d2.REFERENCED_TYPE END "REF_OBJ_TYPE_2",
                CASE WHEN d2.REFERENCED_OWNER IN('SYS', 'PUBLIC') THEN Null ELSE d2.REFERENCED_OWNER END "REF_OBJ_OWNED_BY_2",
                --
                CASE WHEN d3.REFERENCED_OWNER IN('SYS', 'PUBLIC') THEN Null ELSE d3.REFERENCED_NAME END "REF_OBJ_NAME_3",
                CASE WHEN d3.REFERENCED_OWNER IN('SYS', 'PUBLIC') THEN Null ELSE d3.REFERENCED_TYPE END "REF_OBJ_TYPE_3",
                CASE WHEN d3.REFERENCED_OWNER IN('SYS', 'PUBLIC') THEN Null ELSE d3.REFERENCED_OWNER END "REF_OBJ_OWNED_BY_3",
                --
                CASE WHEN d4.REFERENCED_OWNER IN('SYS', 'PUBLIC') THEN Null ELSE d4.REFERENCED_NAME END "REF_OBJ_NAME_4",
                CASE WHEN d4.REFERENCED_OWNER IN('SYS', 'PUBLIC') THEN Null ELSE d4.REFERENCED_TYPE END "REF_OBJ_TYPE_4",
                CASE WHEN d4.REFERENCED_OWNER IN('SYS', 'PUBLIC') THEN Null ELSE d4.REFERENCED_OWNER END "REF_OBJ_OWNED_BY_4",
                --
                CASE WHEN d5.REFERENCED_OWNER IN('SYS', 'PUBLIC') THEN Null ELSE d5.REFERENCED_NAME END "REF_OBJ_NAME_5",
                CASE WHEN d5.REFERENCED_OWNER IN('SYS', 'PUBLIC') THEN Null ELSE d5.REFERENCED_TYPE END "REF_OBJ_TYPE_5",
                CASE WHEN d5.REFERENCED_OWNER IN('SYS', 'PUBLIC') THEN Null ELSE d5.REFERENCED_OWNER END "REF_OBJ_OWNED_BY_5",
                --
                CASE WHEN d6.REFERENCED_OWNER IN('SYS', 'PUBLIC') THEN Null ELSE d6.REFERENCED_NAME END "REF_OBJ_NAME_6",
                CASE WHEN d6.REFERENCED_OWNER IN('SYS', 'PUBLIC') THEN Null ELSE d6.REFERENCED_TYPE END "REF_OBJ_TYPE_6",
                CASE WHEN d6.REFERENCED_OWNER IN('SYS', 'PUBLIC') THEN Null ELSE d6.REFERENCED_OWNER END "REF_OBJ_OWNED_BY_6"
            FROM
                obj_deps_1 o
            LEFT JOIN
                ALL_DEPENDENCIES d2 ON(d2.NAME = Nvl(o.REF_OBJ_NAME_1, 'no_object') And d2.TYPE = Nvl(o.REF_OBJ_TYPE_1, 'no_type'))
            LEFT JOIN
                ALL_DEPENDENCIES d3 ON(d3.NAME = Nvl(d2.REFERENCED_NAME, 'no_object') And d3.TYPE = Nvl(d2.REFERENCED_TYPE, 'no_type'))
            LEFT JOIN
                ALL_DEPENDENCIES d4 ON(d4.NAME = Nvl(d3.REFERENCED_NAME, 'no_object') And d4.TYPE = Nvl(d3.REFERENCED_TYPE, 'no_type'))
            LEFT JOIN
                ALL_DEPENDENCIES d5 ON(d5.NAME = Nvl(d4.REFERENCED_NAME, 'no_object') And d5.TYPE = Nvl(d4.REFERENCED_TYPE, 'no_type'))
            LEFT JOIN
                ALL_DEPENDENCIES d6 ON(d6.NAME = Nvl(d5.REFERENCED_NAME, 'no_object') And d6.TYPE = Nvl(d5.REFERENCED_TYPE, 'no_type'))
        )
SELECT 
    REPLACE('[' || OBJ_TYPE || '] "' || OBJ_NAME || '" (owner.' || OBJ_OWNED_BY || ')', '[] "" (owner.)', 'None') "OBJECT",
    REPLACE('[' || REF_OBJ_TYPE_1 || '] "' || REF_OBJ_NAME_1 || '" (owner.' || REF_OBJ_OWNED_BY_1 || ')', '[] "" (owner.)', 'None') "DEPENDENCY_1",
    REPLACE('[' || REF_OBJ_TYPE_2 || '] "' || REF_OBJ_NAME_2 || '" (owner.' || REF_OBJ_OWNED_BY_2 || ')', '[] "" (owner.)', 'None') "DEPENDENCY_2",
    REPLACE('[' || REF_OBJ_TYPE_3 || '] "' || REF_OBJ_NAME_3 || '" (owner.' || REF_OBJ_OWNED_BY_3 || ')', '[] "" (owner.)', 'None') "DEPENDENCY_3",
    REPLACE('[' || REF_OBJ_TYPE_4 || '] "' || REF_OBJ_NAME_4 || '" (owner.' || REF_OBJ_OWNED_BY_4 || ')', '[] "" (owner.)', 'None') "DEPENDENCY_4",
    REPLACE('[' || REF_OBJ_TYPE_5 || '] "' || REF_OBJ_NAME_5 || '" (owner.' || REF_OBJ_OWNED_BY_5 || ')', '[] "" (owner.)', 'None') "DEPENDENCY_5",
    REPLACE('[' || REF_OBJ_TYPE_6 || '] "' || REF_OBJ_NAME_6 || '" (owner.' || REF_OBJ_OWNED_BY_6 || ')', '[] "" (owner.)', 'None') "DEPENDENCY_6"
FROM
    obj_deps_6
ORDER BY
    OBJ_NAME, OBJ_TYPE,
    REF_OBJ_NAME_1, REF_OBJ_TYPE_1,
    REF_OBJ_NAME_2, REF_OBJ_TYPE_2,
    REF_OBJ_NAME_3, REF_OBJ_TYPE_3,
    REF_OBJ_NAME_4, REF_OBJ_TYPE_4,
    REF_OBJ_NAME_5, REF_OBJ_TYPE_5
赞(0）分享  回复(0）举报  2023-10-16
我来回答

相关问题

    查看更多

    热门标签

    更多
    JavaquerypythonNode开发语言requestUtil数据库Table后端算法LoggerMessageElementParser

    最新问答

    更多
    • 蜀ICP备13028337号-1 大数据知识库 https://www.saoniuhuo.com © All rights reserved
    • 本站内容来源互联网,如果侵犯您的权益请联系我们删除, 联系方式：448109455@qq.com