CODEWITHSUNDEEP
databaseoracle-database
Leo
Leo

Reputation: 1919

in Oracle DB 12c, how do I find what function or procedure ran a query listed in v$sql?

I have sql_id, that gives me a query

select sql_text from v$sql where sql_id = '111Xyz'

but now I would like to know what called this query , a function or a trigger ?

I have tried looking at the documentation of v$sql and the column definitions, but I have not found something clear.

Upvotes: 0

Views: 288

Answers (1)

Andrew Sayer
Andrew Sayer

Reputation: 2336

The information is in v$sql. The program_id will give you an object_id you can lookup in dba_objects to give you the program name. There will also be a program_line# that will give you the line number in the source of the program that calls the SQL (this is from after PL/SQL compiler optimisations so it might not always match up with where it appears in your source files). This will only be given for the program that created the child cursor, if you have multiple programs with exactly the same query, it will only tell you about the program that first called it.

Upvotes: 1

Related Questions