Re: dilemma - need rescue

Brain,

I did try to recreate the sysauth$ and other system/object privilege dictionary tables. There are 288 system privileges(8i) defined in the system_privilege_map table where the privilege numbers are used in the sysauth$ table. I have recreated the sysauth$ table but , for some reasons, the "ORA-00604: error occurred at recursive SQL level 1" still occur. In addition, there is no way to recreate sys schema on the fly while preserving others.
Neither import nor recover works. I think the old backup is the only solution to this. I am recreating the instance from scratch but still proud of my 'career move' scripts anyway. Need some tweaks of course. Thanks for your suggestions.

Ali88

"Brian Peasland" <peasland_at_usgs.gov> wrote in message news:3A9A77EF.C5D15E06_at_usgs.gov...
> Ali88,
>
> You could sign on as SYS and recreate your SYSAUTH$ table (and any
> others). But this will not populate the SYSAUTH$ table with the
> information you need. So you are left with 2 options:
> 1. Restore the database from a backup (you did back it up right?)
> or
> 2. Recreate the database from scratch.
>
> HTH,
> Brian
>
>
> > ali88 wrote:
> >
> > Hi all,
> >
> > I noticed that some static dictionary tables and their indexes are
> > fragmented after a while in my Oracle8i database where a typical
> > installation was choose. I was trying to defragment a non-production
> > Oracle8i(8.1.7) database. Unfortunately, the dictionary tables
> > SYSAUTH$ and SYSTEM_PRIVILEGE_MAP were dropped among other objects
> > during the process. After that, any sql statement and import
> > utility will generate an error, ORA-00604: error occurred at recursive
> > SQL level 1, followed by ORA-00942: table or view does not exist. All
> > of a sudden, users and system accounts can't even login due to lack of
> > create session privilege. I try to recreate these tables from the
> > sql.bsq but couldn't fix the problem. I believe that's because all the
> > system privileges and authentications settings were lost.
> > Is there any solution to fix this without having to recreate the
> > instance? Any suggestion is appreciated.
> >
> > Ali88
>
> --
> ========================================
> Brian Peasland
> Raytheons Systems at
> USGS EROS Data Center
> These opinions are my own and do not
> necessarily reflect the opinions of my
> company!
> ========================================
>
Received on Mon Feb 26 2001 - 20:20:38 CST