Any way to debug an Oracle Sequence?

Question

Somewhere in a large project a sequence is being incremented one too many times. Is there some way to add a trigger or watch to the sequence when .NEXTVAL is invoked so we can get a call stack and see where it's happening?

I tried looking for all the code that accesses the sequence directly, as well as code that constructs queries with .NEXTVAL and none are relevant.

Pretty sure it's on the database side because we've trapped the execution as much as we can, and the sequence is being incremented after an INSERT so maybe some post-insert trigger in a package we can't see.

Answer 1

If it's embedded in PL/SQL either directly or within a non-dynamic SQL executed by PL/SQL and that's a stored program (not anonymous), you can find it as Wernfried mentioned in ALL_DEPENDENCIES.

If it's an anonymous (non-stored) PL/SQL block, you'd have to search the code in sql_fulltext in gv$sqlarea for the name and hope it's still in the pool.

If however it's called from a SQL statement (whether standalone or from PL/SQL), it should be visible in the SQL plan data.

For recent SQL still in the pool:

 SELECT *
   FROM gv$sql_plan
  WHERE object_name = 'MYSEQUENCE_NAME'
    AND operation = 'SEQUENCE'

For aged-out SQL:

SELECT *
  FROM dba_hist_sql_plan
 WHERE object_name = 'MYSEQUENCE_NAME'
   AND operation = 'SEQUENCE'

Once you get the sql_id you can use that to query gv$sqlarea for the parsing user or gv$active_session_history for various other session details (executing user, program, module, machine, PL/SQL program (if any), etc..). About the only thing that lacks is the client process and osuser. If you found it in dba_hist... then you'll need to use dba_hist_sqlstat and dba_hist_active_sess_history for these details (which unfortunately, as it only stored 1 out of every 10 records from the in-memory sampling, is less likely to find the activity if it was relatively short-lived). Even the in-memory data might miss it if it was sub-second and not repeated. But this is what I'd do before resorting to anything more invasive.

Also ensure that the behavior you are witnessing isn't due to the sequence's CACHE setting.