How do I find out when a stored procedure was last modified or compiled in Oracle?

sql oracle stored-procedures plsql oracle10g
ninesided picture ninesided · Nov 18, 2008 · Viewed 83.9k times · Source

I'm preferably looking for a SQL query to accomplish this, but other options might be useful too.

Answer

WW. picture WW. · Nov 18, 2008 
SELECT LAST_DDL_TIME, TIMESTAMP
FROM USER_OBJECTS
WHERE OBJECT_TYPE = 'PROCEDURE'
AND OBJECT_NAME = 'MY_PROC';

LAST_DDL_TIME is the last time it was compiled. TIMESTAMP is the last time it was changed.

Procedures may need to be recompiled even if they have not changed when a dependency changes.

Related questions

Solution to "cannot perform a DML operation inside a query"?

I am using a Data Analysis tool and the requirement I have was to accept a value from the user, pass that as a parameter and store it in a table. Pretty straighforward so I sat to write this create …

Read More
How to use Alias in Where clause?

I have this procedure: PROCEDURE P_LOAD_EXPIRED_ACCOUNT ( pDayDiff IN NUMBER, ExpiredCur OUT MEGAGREEN_CUR ) IS BEGIN OPEN ExpiredCur FOR SELECT ACCOUNT_NAME, SERVICE_TYPE, CASE WHEN SERVICE_TYPE = 1 THEN ADD_MONTHS(ACTIVATED_DATE,3) WHEN SERVICE_TYPE = 2 THEN ADD_…

Read More
Searching for Text within Oracle Stored Procedures

I need to search through all of the stored procedures in an Oracle database using TOAD. I am looking for anywhere that the developers used MAX + 1 instead of the NEXTVAL on the sequence to get the next ID number. I've …

Read More