oracle 如何知道在哪一列上应用了序列？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3064139/
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 to know on which column,the sequence is applied?
提问by Vineet
I have to fetch all sequences with their table name along with the column name on which sequence is applied .Some how i managed to fetch table name corresponding to sequence because in my data base sequence is stored with first name as table name from data dictionary(all_sequences and all_tables) .
我必须获取所有序列及其表名以及应用序列的列名。我如何设法获取与序列相对应的表名，因为在我的数据库中，序列以名字作为表名存储在数据字典中（ all_sequences 和 all_tables) 。
Please let me know how to fetch corresponding column name also if possible!!
如果可能，请让我知道如何获取相应的列名！！
回答by dcp
In Oracle, a sequence is an independent object, it's not associated with a specific table or column. For example, you can run this query to get a list of the sequences:
在 Oracle 中，序列是一个独立的对象，它不与特定的表或列相关联。例如，您可以运行此查询以获取序列列表：
SELECT * FROM all_sequences
And when you create a sequence, you'll notice that there's nothing in the CREATE SEQUENCE syntax to indicate that you want to associate it with a table or column.
当您创建一个序列时，您会注意到 CREATE SEQUENCE 语法中没有任何内容表明您希望将它与表或列相关联。
A sequence is just a unique number generator, it doesn't care what you do with the number generated from it (i.e. whether you insert the sequence value into a table, etc.), it's just there to provide that unique number.
序列只是一个唯一的数字生成器，它不关心您如何处理从中生成的数字（即是否将序列值插入表等），它只是提供该唯一数字。
So it's impossible to tell for a given column what sequence was used (if any) to generate that column's value.
因此，不可能告诉给定列使用了什么序列（如果有）来生成该列的值。
回答by Gary Myers
You can often 'guess' at a correlation by looking at the LAST_NUMBER in all_sequences and the following SQL (which looks at the highest number for numeric columns defined as part of a primary key).
您通常可以通过查看 all_sequences 中的 LAST_NUMBER 和以下 SQL（查看定义为主键一部分的数字列的最大数量）来“猜测”相关性。
select table_name, column_name, utl_raw.cast_to_number(high_value)
from dba_tab_columns
where owner = '...'
and data_type = 'NUMBER'
and (owner, table_name, column_name) in
(select cc.owner, cc.table_name, cc.column_name
from dba_cons_columns cc
join dba_constraints c
on cc.owner = c.owner and cc.constraint_name = c.constraint_name
where c.constraint_type = 'P')
order by 3;
But it is a good idea to adopt a naming standard that indicates the correlation (eg the same as the table_name with a _SEQ on the end).
但是，采用指示相关性的命名标准是一个好主意（例如，与 table_name 相同，末尾带有 _SEQ）。
回答by Gary Myers
See dcp's answer.
请参阅 dcp 的回答。
However, a sequence will normally be used to generate a unique key for the table it corresponds to - try looking for primary keys and/or unique indexes on the matching table.
但是，通常会使用序列为其对应的表生成唯一键 - 尝试在匹配表上查找主键和/或唯一索引。
