CODEWITHSUNDEEP
oracle10g
user3478236
user3478236

Reputation: 1

I want to find the list of procedures and packages which uses a specific table or view?

I have a specific oracle DB. I have around 600 procedures and 26 packages in it . I have a view name consider view_1 which was replaced by another view recently as view_2. i want to change the view name from View_1 to View_2 in all the packages and procedure which uses it. can you please get me a solution for this. how can i know the list of procedure and packages which uses this view_1.

thanks in advance,

Upvotes: 0

Views: 160

Answers (1)

Justin Cave
Justin Cave

Reputation: 231681

You can use the dba_dependencies (or all_dependencies or user_dependencies depending on your privileges and whether you are logged in as the user that owns the schema that has all the objects.

This will show you the owner, object name, and object type of every object in the database that depends on view_1.

SELECT owner, name, type
  FROM dba_dependencies
 WHERE referenced_owner = <<owner of view_1>>
   AND referenced_name  = 'VIEW_1'
   AND referenced_type  = 'VIEW'

If you only care about objects on which you have privileges, you can substitute the all_dependencies data dictionary table for dba_dependencies. If you only care about objects in your schema, you can use user_dependences but then you'd omit the owner column from your projection.

Note that this will only resolve cases where Oracle knows that there are dependencies. If you have code that is using dynamic SQL to refer to view_1, that's not something that Oracle is aware of so that's not something that you can find by querying the data dictionary. You'd potentially need to manually inspect any pieces of code that are doing that.

If you are replacing one view with another, however, I'd question why you wouldn't just replace the implementation of view_1 with the implementation of view_2. Why bother going through and modifying every place in your code that references view_1 when you can just change the implementation of view_1? After all, one of the benefits of having a view layer as an API is that you can change the implementation of the views when the underlying data model changes without needing to touch all the code that depends on the view.

Upvotes: 1

Related Questions