Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: How to restore views and procedures after drop user command?
Do you have source safe or even a networked sql scripts directory?
I actually wouldn't fire the developer on a 'first offence'. You (and he) now know about backup and recovery from a real users perspective. Many people only think they know about these things.
-- Niall Litchfield Oracle DBA Audit Commission UK ***************************************** Please include version and platform and SQL where applicable It makes life easier and increases the likelihood of a good answer ****************************************** "Victor Slootsky" <SLootsky_at_erols.com> wrote in message news:8fc7e62.0210131450.11028d5c_at_posting.google.com...Received on Mon Oct 14 2002 - 08:49:20 CDT
> How to restore views and procedures after drop user command?
>
> We have Oracle 8.1.7 EE on NT and one developer created a lot of
> procedures, functions and views. DB was not backuped and archived and
> export has not been done - our fault and we understand it. Sorry for
> this.
>
> Ok, now the story: another developer dropped this db user and we lost
> everything: procedures, functions and views. The new user with the
> same name was created and new schema was imported in this user, but
> all old objects are lost. We don't have export and backup and archive
> log files.
>
> Question: may we can restore this lost stuff from some other sources.
> We are looking for lost codes, not data. May be we can use redo logs
> or Shared Pool or any other things. Any idea will be appreciated.
>
> The switch has occurred after user was dropped, the data has been
> overwritten and there is be no way to use redo log files.
>
> I would like to explore another opportunity. Is possible to use Shared
> Pool or any Data Dictionary internal information to restore texts of
> the lost SQL and PL/SQL scripts executed in this DB before user was
> dropped? Not too many scripts are executed in this DB and the lost
> ones may still be in stack. I remember that Shared Pool (cash) should
> keep last executed scripts in order to improve performance. They
> probably are kept in some special format. Can we restore these
> scripts? Of course they also might be pushed out by Import that had
> been done after user was dropped.
>
> Thanks for your help,
>
> Victor