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

问题描述

我已使用此查询来获取属于Oracle数据库用户的序列列表:

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

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

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

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_table using query so that I can get the auto increment id in my application.

推荐答案

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

You can get the triggers associated with your tables from the user_triggers view. 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_sequences view will only show you the ones you are interested in.

像这样的事情，假设您正在查看自己的模式，并且仅在引用序列的触发器中感到有趣(不一定执行自动递增"，但可能是):

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演示.

如果您实际上正在查看其他架构，则需要使用all_tables等，并过滤并加入要查找的用户的所有者列.而且，如果要包括没有触发器的表或不引用序列的触发器，则可以使用外部联接.

If you're actually looking at a different schema then you'll need to use all_tables etc. 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.

版本正在寻找其他架构，尽管这假设您具有访问数据字典信息所必需的特权-表等对您可见，但可能不可见:

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>';

如果看不到它们，那么您可能需要查看DBA视图，如果您有足够的特权，则需要再次查看:

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

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>';

这篇关于查找与Oracle表关联的序列和触发器的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！