Home » RDBMS Server » Backup & Recovery » Recover a package body (oracle 9)
Recover a package body [message #411788] Mon, 06 July 2009 14:15 Go to next message
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;

Re: Recover a package body [message #411790 is a reply to message #411788] Mon, 06 July 2009 14:37 Go to previous messageGo to next message
Michel Cadot
Messages: 68722
Registered: March 2007
Location: Saint-Maur, France, https...
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 Go to previous messageGo to next message
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
Re: Recover a package body [message #412017 is a reply to message #411788] Tue, 07 July 2009 07:41 Go to previous messageGo to next message
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.
Re: Recover a package body [message #412024 is a reply to message #412017] Tue, 07 July 2009 07:53 Go to previous messageGo to next message
Michel Cadot
Messages: 68722
Registered: March 2007
Location: Saint-Maur, France, https...
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
Re: Recover a package body [message #412035 is a reply to message #411788] Tue, 07 July 2009 08:37 Go to previous messageGo to next message
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?
Re: Recover a package body [message #412037 is a reply to message #412035] Tue, 07 July 2009 08:48 Go to previous messageGo to next message
Michel Cadot
Messages: 68722
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
A view is defined by a text, this text references tables, this is what is named "base tables".

Regards
Michel
Re: Recover a package body [message #412038 is a reply to message #412035] Tue, 07 July 2009 08:49 Go to previous message
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
Previous Topic: PLS-00553: character set name is not recognized
Next Topic: How to restart a restore after it failed ?
Goto Forum:
  


Current Time: Fri Dec 13 05:44:48 CST 2024