OraFAQ Forum: SQL & PL/SQL » Get the deletion date for a certain DB object (Stored Procedure)

Home » SQL & PL/SQL » SQL & PL/SQL » Get the deletion date for a certain DB object (Stored Procedure) (11.0.2.10)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Get the deletion date for a certain DB object (Stored Procedure) [message #656389]

Wed, 05 October 2016 02:07

OraFerro
Messages: 433
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

Report message to a moderator

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

John Watson
Messages: 8929
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.

Report message to a moderator

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

OraFerro
Messages: 433
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

Report message to a moderator

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

John Watson
Messages: 8929
Registered: January 2010
Location: Global Village

Senior Member

I hope you have paid for the Lifecycle Management Pack.

Report message to a moderator

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

OraFerro
Messages: 433
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

Report message to a moderator

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

Michel Cadot
Messages: 68641
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

You can use Log Miner.

Report message to a moderator

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

EdStevens
Messages: 1376
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.

Report message to a moderator

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

John Watson
Messages: 8929
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.

Report message to a moderator

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

Bill B
Messages: 1971
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.

Report message to a moderator

Previous Topic:	EGO_ITEM_USER_ATTRS_CP_PUB.Process_Item_User_Attrs_Data API Error
Next Topic:	REGEXP_REPLACE

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Apr 19 16:29:32 CDT 2024