OraFAQ Forum: Backup & Recovery » Recover a package body

Home » RDBMS Server » Backup & Recovery » Recover a package body (oracle 9)

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

Recover a package body [message #411788]

Mon, 06 July 2009 14:15

DennisL
Messages: 3
Registered: July 2009

Junior Member

Hello, I need to recover the body of a package. I have tried to use the as of timestampfunction on the dba_source table, but to no avail. I get an ORA-01031: insufficient privileges error.
Any ideas?

select *
from
(select
text
from
dba_source
where
owner = 'ONCPROD' and
name = 'PK_ONC_NIGHTLY_SYNC' And
type = 'PACKAGE BODY'
order by line asc)
AS OF TIMESTAMP SYSDATE - 1;

Report message to a moderator

Re: Recover a package body [message #411790 is a reply to message #411788]

Mon, 06 July 2009 14:37

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

Senior Member
Account Moderator

1/ Do it with one that has sufficient privilege, a DBA for example
2/ Do it on base table not on view

Regards
Michel

[Updated on: Mon, 06 July 2009 14:59]

Report message to a moderator

Re: Recover a package body [message #411791 is a reply to message #411788]

Mon, 06 July 2009 14:42

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

> I need to recover the body of a package
as which schema are you logged in as to attempt SQL?

try all_source instead of dba_source

Report message to a moderator

Re: Recover a package body [message #412017 is a reply to message #411788]

Tue, 07 July 2009 07:41

DennisL
Messages: 3
Registered: July 2009

Junior Member

We tried it as a dba, different error. We also tried it on table all_source, same thing.

Report message to a moderator

Re: Recover a package body [message #412024 is a reply to message #412017]

Tue, 07 July 2009 07:53

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

Senior Member
Account Moderator

Quote:

We tried it as a dba, different error.

That you don't of course post as we mandatory know what is displayed on your screen.
This is expected and this is why I mentioned 2 points, so do them BOTH.

Regards
Michel

Report message to a moderator

Re: Recover a package body [message #412035 is a reply to message #411788]

Tue, 07 July 2009 08:37

DennisL
Messages: 3
Registered: July 2009

Junior Member

Here is the error ORA-01555: snapshot too old: rollback segment number 47 with name "_SYSSMU47$" too small

I don't understand item 2, Do it on base table not on view
What is the base table?

Report message to a moderator

Re: Recover a package body [message #412037 is a reply to message #412035]

Tue, 07 July 2009 08:48

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

Senior Member
Account Moderator

A view is defined by a text, this text references tables, this is what is named "base tables".

Regards
Michel

Report message to a moderator

Re: Recover a package body [message #412038 is a reply to message #412035]

Tue, 07 July 2009 08:49

babuknb
Messages: 1736
Registered: December 2005
Location: NJ

Senior Member

Hello,

Quote:

We tried it as a dba, different error. We also tried it on table all_source, same thing.

Then you should try USER_SOURCE

Quote:

Here is the error ORA-01555: snapshot too old: rollback segment number 47 with name "_SYSSMU47$" too small

I don't understand item 2, Do it on base table not on view

What is the base table?

We also not able to understand what you tried? Post your SQL Query

I think this thread going different topic.

Babu

Report message to a moderator

Previous Topic:	PLS-00553: character set name is not recognized
Next Topic:	How to restart a restore after it failed ?

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Apr 25 04:25:38 CDT 2024