Home » SQL & PL/SQL » SQL & PL/SQL » Get the deletion date for a certain DB object (Stored Procedure) (11.0.2.10)
Get the deletion date for a certain DB object (Stored Procedure) [message #656389] Wed, 05 October 2016 02:07 Go to next message
OraFerro
Messages: 315
Registered: July 2011
Senior Member
Hi All,

I've searching the net with no luck on how to get the deletion date for a certain Stored Procedure, is that possible?

My problem is that I discovered that a few Stored Procedures where deleted, I tried getting them from older backup but also did not find them. It seems they were deleted longer ago than I expected and wanted to know the deletion date to save time. Is that possible?

Thanks,
Ferro
Re: Get the deletion date for a certain DB object (Stored Procedure) [message #656390 is a reply to message #656389] Wed, 05 October 2016 02:11 Go to previous messageGo to next message
John Watson
Messages: 7220
Registered: January 2010
Location: Global Village
Senior Member
Note that you can't DELETE a procedure, you would DROP it.

The date of the event would not be recorded unless you happen to have an appropriate audit enabled, or if the ENABLE_DDL_LOGGING parameter (which does have licensing implications) has been set.
Re: Get the deletion date for a certain DB object (Stored Procedure) [message #656391 is a reply to message #656390] Wed, 05 October 2016 02:23 Go to previous messageGo to next message
OraFerro
Messages: 315
Registered: July 2011
Senior Member
Dear John Watson,

I meant dropped not deleted, my typo.

Unfortunately enable_ddl_logging was set to FALSE, now I executed: alter system set enable_ddl_logging=true;

Is there any other way to know the drop date of those SPs?

Thanks,
Ferro
Re: Get the deletion date for a certain DB object (Stored Procedure) [message #656392 is a reply to message #656391] Wed, 05 October 2016 02:25 Go to previous messageGo to next message
John Watson
Messages: 7220
Registered: January 2010
Location: Global Village
Senior Member
I hope you have paid for the Lifecycle Management Pack.
Re: Get the deletion date for a certain DB object (Stored Procedure) [message #656393 is a reply to message #656392] Wed, 05 October 2016 02:38 Go to previous messageGo to next message
OraFerro
Messages: 315
Registered: July 2011
Senior Member
Frankly I have no idea, I was surprised that enable_ddl_logging needs an additional license as it was available.

For the Lifecycle Management Pack, from my internet reading, its used to manage oracle version upgrades among other features but did not find something related to dropped objects dates.

How do I check if I have this license, and how to use it to know the drop date of a SP?
Thanks,
Ferro
Re: Get the deletion date for a certain DB object (Stored Procedure) [message #656394 is a reply to message #656393] Wed, 05 October 2016 04:25 Go to previous messageGo to next message
Michel Cadot
Messages: 65317
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

You can use Log Miner.

Re: Get the deletion date for a certain DB object (Stored Procedure) [message #656407 is a reply to message #656393] Wed, 05 October 2016 06:39 Go to previous messageGo to next message
EdStevens
Messages: 889
Registered: September 2013
Senior Member
OraFerro wrote on Wed, 05 October 2016 02:38
Frankly I have no idea, I was surprised that enable_ddl_logging needs an additional license as it was available.

For the Lifecycle Management Pack, from my internet reading, its used to manage oracle version upgrades among other features but did not find something related to dropped objects dates.

How do I check if I have this license, and how to use it to know the drop date of a SP?
Thanks,
Ferro
Nothing in the database knows what is licensed. Licensing is strictly a contractual arrangement between your company and Oracle Corp. To know what add-ons are licensed, you have to check with whoever in your company is responsible for negotiating licenses with Oracle Corp.
Re: Get the deletion date for a certain DB object (Stored Procedure) [message #656409 is a reply to message #656393] Wed, 05 October 2016 06:52 Go to previous messageGo to next message
John Watson
Messages: 7220
Registered: January 2010
Location: Global Village
Senior Member
Quote:
I was surprised that enable_ddl_logging needs an additional license as it was available.
If you go to the supermarket, there are lot of things available. But if you take them, you have to pay.
Re: Get the deletion date for a certain DB object (Stored Procedure) [message #656414 is a reply to message #656409] Wed, 05 October 2016 09:53 Go to previous message
Bill B
Messages: 1709
Registered: December 2004
Senior Member
Do you have the procedure in a source control system. If this is a production system you sure should have some sort of backup outside of the database.
Previous Topic: EGO_ITEM_USER_ATTRS_CP_PUB.Process_Item_User_Attrs_Data API Error
Next Topic: REGEXP_REPLACE
Goto Forum:
  


Current Time: Sun Jan 21 09:31:55 CST 2018

Total time taken to generate the page: 0.01127 seconds