Finding sequences and triggers associated with an Oracle table

Question

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

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.

Answer 1

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_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 demo.

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

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

Answer 2

One way would be to run these queries to check if there are any sequence's Pseudocolumns (NEXTVAL and CURRVAL ) used in your functions , procedures, packages, Triggers or PL/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 Trigger to check which column/table gets populated by a sequence. The query could also be used with '%CURRVAL%'

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

---

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

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

This will internally create a sequence that 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

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