我想获得Oracle视图中使用的源。
如-
SELECT a.name, b.name, c.age FROM customer_tab a, employee_tab b, employee_detail_tab c
假设我在Oracle DB中部署了上述视图，是否有一种方法来提取FROM子句中的对象?
您可以查询DBA_DEPENDENCIES
描述了数据库中对象之间的所有依赖关系。这一观点不显示SCHEMAID列。
或ALL_DEPENDENCIES
描述过程、包、函数、包主体，以及当前用户可访问的触发器，包括对没有任何数据库链接的视图的依赖。这一观点不显示SCHEMAID列。
您可以使用解释计划。只做:
EXPLAIN PLAN FOR <your query>;
然后你可以写:
SELECT DISTINCT object_name
FROM plan_table
WHERE object_name IS NOT NULL;
你可以尝试使用下面的代码来获得高达6级的依赖关系…
-- -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-- Shows objects that given object depends on
-- --------------------------------------------------------------------------------
-- MOTE :
-- 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
-- --------------------------------------------------------------------------------
-- 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 fotmat: ---> [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
运行它，你会被要求输入三个参数(你可以输入你自己的)…
Enter Value For OwnerName: 'user_1'
Enter Value For ObjectName: 'some_view_to_check'
Enter Value For ObjectType: 'view'
R e s u l t :
OBJECT DEPENDENCY_1 DEPENDENCY_2 DEPENDENCY_3 DEPENDENCY_4 DEPENDENCY_5 DEPENDENCY_6
------------------------------------------ --------------------------------------- ------------------------------- ------------------- ------------------- ------------------- ---------------------
[view] "some_view_to_check" (owner.user_1) [VIEW] "SOME_VIEW_1" (owner.USER_1) [TABLE] "TABLE2" (owner.USER_1) None None None None
[view] "some_view_to_check" (owner.user_1) [VIEW] "SOME_VIEW_1" (owner.USER_1) [TABLE] "TABLE3" (owner.USER_1) None None None None
[view] "some_view_to_check" (owner.user_1) [TABLE] "SOME_TABLE1" (owner.USER_1) None None None None None
结果显示视图"some_view_to_check"使用另一个视图"SOME_VIEW_1"和一个表some_table1;(这是Dependency_1)…此外，视图"SOME_VIEW_1"使用两个表:TABLE2和TABLE3 (Dependency_2)