Menu
First, thank you all for your help.
I'm trying to locate something similar to MSSQL's sys.procedures.Modify_date in oracle(10g).
A little context:
We design many stored procedures for use in both oracle and mssql. it's simple enough in mssql to see which stored procedures have been updated (as modify_date will be newer). I've heard there was something similar for oracle but have found precious little on the intertubes. I also heard a rumor that there was something close, but it contains the last compilation date for the sproc. As i'm looking for "the last time someone altered the text of the sproc", this won't quite work.
Is this possible? is there some trick? Right now we maintain a text comment within the stored procedure and one of the devs wrote a routine to go pull out the date from that comment. It's klunky and easy to forget to do and i'd like to find a better way.
Again, thanks for your help.
885
Here's one way: SELECT owner , object_name , last_ddl_time FROM all_objects -- or dba_objects, if you have privileges WHERE object_type IN ('PROCEDURE') ORDER BY last_ddl_time ; You can't modify procedures; all you can do is CREATE or REPLACE them.
If you want to find, when a table was last modified like insert,update ,delete, then use the dictionary table dba_tab_modifications.
Perhaps the easiest way to get started enumerating the stored procedures in a database is with sys. procedures, which is a catalog view. The sys. procedures catalog view provides a SQL Server specific way of querying information about metadata, such as stored procedure object name and schema name.
SELECT LAST_DDL_TIME, TIMESTAMP
FROM DBA_OBJECTS
WHERE OBJECT_TYPE = 'PROCEDURE'
AND OBJECT_NAME = 'PRC_MINE'
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
