SQL 哪个 Oracle 表使用序列？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2363210/
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
Which Oracle table uses a sequence?
提问by mcrisc
Having a sequence, I need to find out which table.column gets its values. As far as I know, Oracle doesn't keep track of this relationship. So, looking up for the sequence in source code would be the only way. Is that right?
有了序列，我需要找出哪个 table.column 获取其值。据我所知，Oracle 不会跟踪这种关系。因此，在源代码中查找序列将是唯一的方法。那正确吗？
Anyone knows of some way to find out this sequence-table relationship?
有人知道找出这种序列表关系的方法吗？
采纳答案by Tony Andrews
In the database you can search all stored code in your schema like this:
在数据库中，您可以像这样搜索架构中的所有存储代码：
select type, name, line, text
from all_source
where owner = 'MYSCHEMA'
and upper(text) like '%MYSEQ.NEXTVAL%';
In SQL Developer, there is a report to do this.
在 SQL Developer 中，有一份报告可以做到这一点。
回答by APC
The problem is that Oracle allows us to use one sequence to populate columns in several tables. Scenarios where this might be desirable include super-type/sub-type implementations.
问题是 Oracle 允许我们使用一个序列来填充多个表中的列。可能需要这样做的场景包括超类型/子类型实现。
You can use the dependencies in the data dictionary to identify relationships. For instance, if you use triggers to assign the values then this query will help you:
您可以使用数据字典中的依赖关系来识别关系。例如，如果您使用触发器来分配值，那么此查询将帮助您：
select ut.table_name
, ud.referenced_name as sequence_name
from user_dependencies ud
join user_triggers ut on (ut.trigger_name = ud.name)
where ud.type='TRIGGER'
and ud.referenced_type='SEQUENCE'
/
If you use PL/SQL then you can write something similar for TYPE in ('PACKAGE BODY', 'PROCEDURE', 'FUNCTION'), although you will still require some trawling through the source code to assign tables and sequences when you have multiple hits.
如果您使用 PL/SQL，那么您可以为 编写类似的东西TYPE in ('PACKAGE BODY', 'PROCEDURE', 'FUNCTION')，尽管当您有多个点击时，您仍然需要对源代码进行一些搜索以分配表和序列。
回答by Quassnoi
If your sequence is used in a trigger, the trigger will be listed in the sequence's "referenced by" list.
如果您的序列在触发器中使用，则触发器将列在序列的“引用者”列表中。
If your sequence is only used in the source-code queries, than yes, browsing the code is the only way.
如果您的序列仅用于源代码查询，那么浏览代码是唯一的方法。
回答by Martin Milan
Use GREP to scan your entire source for "myseq.NextVal" - myseq being the one you're looking for....
使用 GREP 扫描整个源以查找“myseq.NextVal” - myseq 就是您要查找的....
回答by Rob van Laarhoven
I'd like to add backgroundinformation on sequences.
我想添加有关序列的背景信息。
