查找与 Oracle 表关联的序列和触发器

Finding sequences and triggers associated with an Oracle table

提问by user2492525

I have used this query to fetch the list of sequences belonging to an Oracle database user:

我使用这个查询来获取属于 Oracle 数据库用户的序列列表：

SELECT * FROM all_sequences x,all_tables B
WHERE x.sequence_owner=B.owner AND B.TABLE_NAME='my_table';

But that database user is having many more sequence also, so the query returns me all the sequence of the database user. Can anybody help me to find the particular sequence of my_tableusing query so that I can get the auto increment id in my application.

但是那个数据库用户也有更多的序列，所以查询返回数据库用户的所有序列。任何人都可以帮助我找到my_table使用查询的特定序列，以便我可以在我的应用程序中获取自动增量 ID。

回答by Alex Poole

i want the query which fetch list of table of my database user with the sequence and triggers used in the table

我想要查询，它使用表中使用的序列和触发器获取我的数据库用户的表列表

You can get the triggers associated with your tables from the user_triggersview. You can then look for any dependencies recorded for those triggers in user_dependencies, which may include objects other than sequences (packages etc.), so joining those dependencies to the user_sequencesview will only show you the ones you are interested in.

您可以从user_triggers视图中获取与您的表关联的触发器。然后，您可以在 中查找为这些触发器记录的任何依赖项user_dependencies，其中可能包括序列以外的对象（包等），因此将这些依赖项加入user_sequences视图只会显示您感兴趣的那些。

Something like this, assuming you are looking at your own schema, and you're only interesting in triggers that references sequences (which aren't necessarily doing 'auto increment', but are likely to be):

像这样的事情，假设您正在查看自己的架构，并且您只对引用序列的触发器感兴趣（不一定执行“自动增量”，但可能会）：

select tabs.table_name,
  trigs.trigger_name,
  seqs.sequence_name
from user_tables tabs
join user_triggers trigs
  on trigs.table_name = tabs.table_name
join user_dependencies deps
  on deps.name = trigs.trigger_name
join user_sequences seqs
  on seqs.sequence_name = deps.referenced_name;

SQL Fiddle demo.

SQL 小提琴演示。

If you're actually looking at a different schema then you'll need to use all_tablesetc. and filter and join on the owner column for the user you're looking for. And if you want to include tables which don't have triggers, or triggers which don't refer to sequences, you can use outer joins.

如果您实际上正在查看不同的架构，那么您将需要使用all_tablesetc. 并过滤并加入您正在查找的用户的所有者列。如果您想包含没有触发器的表或不引用序列的触发器，您可以使用外连接。

---

---

Version looking for a different schema, though this assumes you have the privs necessary to access the data dictionary information - that the tables etc. are visible to you, which they may not be:

寻找不同模式的版本，尽管这假设您具有访问数据字典信息所需的权限 - 表等对您可见，但它们可能不是：

select tabs.table_name,
  trigs.trigger_name,
  seqs.sequence_name
from all_tables tabs
join all_triggers trigs
  on trigs.table_owner = tabs.owner
  and trigs.table_name = tabs.table_name
join all_dependencies deps
  on deps.owner = trigs.owner
  and deps.name = trigs.trigger_name
join all_sequences seqs
  on seqs.sequence_owner = deps.referenced_owner
  and seqs.sequence_name = deps.referenced_name
where tabs.owner = '<owner>';

If that can't see them then you might need to look at the DBA views, again if you have sufficient privs:

如果看不到它们，那么您可能需要查看 DBA 视图，如果您有足够的权限：

select tabs.table_name,
  trigs.trigger_name,
  seqs.sequence_name
from dba_tables tabs
join dba_triggers trigs
  on trigs.table_owner = tabs.owner
  and trigs.table_name = tabs.table_name
join dba_dependencies deps
  on deps.owner = trigs.owner
  and deps.name = trigs.trigger_name
join dba_sequences seqs
  on seqs.sequence_owner = deps.referenced_owner
  and seqs.sequence_name = deps.referenced_name
where tabs.owner = '<owner>';

回答by imsome1

I found a solution to this problem to guess the sequence of a particular sequence

我找到了这个问题的解决方案来猜测特定序列的序列

select * from SYS.ALL_SEQUENCES where SEQUENCE_OWNER='OWNER_NAME' and LAST_NUMBER between (select max(FIELD_NAME) from TABLE_NAME) and (select max(FIELD_NAME)+40 from TABLE_NAME);

This query will guess by search the LAST_NUMBER of the sequence value between MAX value of the field using sequence and Max value + 40 (in my case cache value is 20, so I put 40)

此查询将通过使用序列和最大值 + 40 搜索字段的 MAX 值之间的序列值的 LAST_NUMBER 来猜测（在我的情况下缓存值为 20，所以我放了 40）

回答by Kaushik Nayak

One way would be to run these queries to check if there are any sequence's Pseudocolumns (NEXTVALand CURRVAL) used in your functions, procedures, packages, Triggersor PL/SQL JAVA SOURCE.

一种方法是运行这些查询以检查您的, , ,或 中是否使用了任何sequence伪列（NEXTVAL和CURRVAL）。functionsprocedurespackagesTriggersPL/SQL JAVA SOURCE

select * from user_source where 
         UPPER(TEXT) LIKE '%NEXTVAL%';   

select * from all_source where 
         UPPER(TEXT) LIKE '%NEXTVAL%';  

Then go to the specific Procedure, Function or Triggerto check which column/table gets populated by a sequence. The query could also be used with '%CURRVAL%'

然后转到特定Procedure, Function or Trigger以检查哪个列/表被序列填充。该查询还可以与'%CURRVAL%'

This might not help if you are running inserts from JDBCor other external applications using a sequence.

如果您JDBC使用sequence.

---

---

Oracle 12c introduced the IDENTITYcolumns, using which you could create a table with an identity column, which is generated by default.

Oracle 12c 引入了IDENTITY列，您可以使用这些列创建一个带有标识列的表，该列是默认生成的。

CREATE TABLE t1 (c1 NUMBER GENERATED BY DEFAULT ON NULL AS IDENTITY, 
                   c2 VARCHAR2(10));

This will internally create a sequencethat auto-generates the value for the table's column.So, If you would like to know which sequence generates the value for which table, you may query the all_tab_columns

这将在内部创建一个sequence自动生成表列的值。因此，如果您想知道哪个序列为哪个表生成值，您可以查询all_tab_columns

SELECT data_default AS sequence_val
    ,table_name
    ,column_name
FROM all_tab_columns
WHERE OWNER = 'HR'
    AND identity_column = 'YES';

SEQUENCE_VAL                             |TABLE_NAME               |COLUMN_NAME
-----------------------------------------|-------------------------------------
"HR"."ISEQ$$_78160".nextval              |T1                       |C1