oracle 如何找出数据库表的填充位置？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2857990/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How can I find out where a database table is being populated from?
提问by Tami
I'm in charge of an Oracle database for which we don't have any documentation. At the moment I need to know howa table is getting populated.
我负责我们没有任何文档的 Oracle 数据库。目前，我需要知道如何表是越来越填充。
How can I find out which procedure, trigger, or other source, this table is getting its data from?
如何找出该表从哪个过程、触发器或其他来源获取数据？
回答by Guru
Or even better, query the DBA_DEPENDENCIEStable (or its equivalent USER_). You should see what objects are dependent on them and who owns them.
或者甚至更好，查询DBA_DEPENDENCIES表（或其等效项USER_）。您应该看到哪些对象依赖于它们以及谁拥有它们。
select owner, name, type, referenced_owner
from dba_dependencies
where referenced_name = 'YOUR_TABLE'
And yeah, you need to see through the objects to see whether there is an INSERT happening in.
是的，您需要查看对象以查看是否有 INSERT 发生。
Also this, from my comment above.
还有这个，来自我上面的评论。
If it is not a production system, I would suggest you to raise an user defined exception in TRIGGER- before INSERT with some custom message or LOCK the table from INSERT and watch over the applications which try inserting into them failing. But yeah, you might also get calls from many angry people.
如果它不是生产系统，我建议您在 INSERT 之前使用一些自定义消息在 TRIGGER- 中引发用户定义的异常，或者从 INSERT 锁定表并监视尝试插入它们失败的应用程序。但是，是的，您也可能会接到许多愤怒的人的电话。
回答by UltraCommit
It is quite simple ;-)
这很简单;-)
SELECT * FROM USER_SOURCE WHERE UPPER(TEXT) LIKE '%NAME_OF_YOUR_TABLE%';
In output you'll have all procedures, functions, and so on, that in ther body invoke your table called NAME_OF_YOUR_TABLE.
在输出中，您将拥有所有过程、函数等，它们在主体中调用名为 NAME_OF_YOUR_TABLE 的表。
NAME_OF_YOUR_TABLE has to be written UPPERCASE because we are using UPPER(TEXT) in order to retrieve results as Name_Of_Your_Table, NAME_of_YOUR_table, NaMe_Of_YoUr_TaBlE, and so on.
NAME_OF_YOUR_TABLE 必须写成大写，因为我们使用 UPPER(TEXT) 来检索 Name_Of_Your_Table、NAME_of_YOUR_table、NaMe_Of_YoUr_TaBlE 等形式的结果。
回答by Gary Myers
Another thought is to try querying v$sql to find a statement that performs the update. You may get something from the module/action (or in 10g progam_id and program_line#).
另一个想法是尝试查询 v$sql 以找到执行更新的语句。您可能会从模块/操作（或在 10g progam_id 和 program_line# 中）获得一些信息。
回答by Stellios
DML changes are recorded in *_TAB_MODIFICATIONS.
DML 更改记录在 *_TAB_MODIFICATIONS 中。
Without creating triggers you can use LOG MINER to find all data changes and from which session.
在不创建触发器的情况下，您可以使用 LOG MINER 来查找所有数据更改以及来自哪个会话。
With a trigger you can record SYS_CONTEXT variables into a table.
使用触发器，您可以将 SYS_CONTEXT 变量记录到表中。
http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/functions165.htm#SQLRF06117
http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/functions165.htm#SQLRF06117
回答by Andrew Wolfe
Sounds like you want to audit.
听起来你想审计。
How about
怎么样
AUDIT ALL ON ::TABLE::;
Alternatively apply DBMS_FGA policy on the table and collect the client, program, user, and maybe the call stack would be available too.
或者，在表上应用 DBMS_FGA 策略并收集客户端、程序、用户，也许调用堆栈也可用。
回答by JustinKaisse
Late to the party!
晚会迟到！
I second Gary's mention of v$sql also. That may yield the quick answer as long as the query hasn't been flushed.
我还提到了 Gary 提到的 v$sql。只要查询尚未刷新，这可能会产生快速答案。
If you know its in your current instance, I like a combination of what has been used above; if there is no dynamic SQL, xxx_Dependencies will work and work well.
如果您在当前实例中知道它，我喜欢上面使用的组合；如果没有动态 SQL，xxx_Dependencies 将工作并且运行良好。
Join that to xxx_Source to get that pesky dynamic SQL.
将其加入 xxx_Source 以获得讨厌的动态 SQL。
We are also bringing data into our dev instance using the SQL*Plus copy command (careful! deprecated!), but data can be introduced by imp or impdp as well. Check xxx_Directories for the directories blessed to bring data in/out.
我们还使用 SQL*Plus 复制命令将数据引入我们的开发实例（小心！不推荐使用！），但数据也可以通过 imp 或 impdp 引入。检查 xxx_Directories 是否有幸将数据输入/输出的目录。
