My Oracle Support Banner

How To Know If An Object (Table / Procedure / View /...) Is Currently Being Used (Doc ID 1370016.1)

Last updated on DECEMBER 27, 2019

Applies to:

Oracle Database Exadata Express Cloud Service - Version N/A and later
Oracle Database Cloud Service - Version N/A and later
Oracle Database - Enterprise Edition - Version 10.2.0.5 and later
Oracle Database Cloud Schema Service - Version N/A and later
Oracle Database Exadata Cloud Machine - Version N/A and later
Information in this document applies to any platform.
***Checked for relevance on 09-Sep-2016***

Goal

NOTE: In the images and/or the document content below, the user information and environment data used represents fictitious data from the Oracle sample or bulit-in schema(s), Public Documentation delivered with an Oracle database product or other training material.  Any similarity to actual environments, actual persons, living or dead, is purely coincidental and not intended in any manner.

The information is intended to help with situations where you want to know whether a certain object is being used, e.g.

1. If the objects is currently being used, e.g. by a DML statement, DDL on the objects will fail. How can you check it before executing the DDL command?

2. If a procedure "USER2.TEST_PROCEDURE" is being executed by user USER3 and owner USER2 wants to replace the procedure code using "create or replace procedure test_procedure ....". How can you find the session that is running the procedure "USER2.TEST_PROCEDURE"?

Solution

To view full details, sign in with your My Oracle Support account.

Don't have a My Oracle Support account? Click to get started!


My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.