Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Rhinorhino on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

getting ORA-00942 on database open

Status
Not open for further replies.
Joined
Jul 25, 2000
Messages
227
Location
US
I created a new database with the scripts generated by previously using dbca. when everything finishes I get a 942 (table doesn't exist) when the database opens. it appears to successfully open and work fine (can create/user objects) but this doesn't make me particularly warm or fuzzy.

has anyone had this problem and if so is it just cosmetic as it seems?
 
NIE-man,

Can you definitively confirm that following the database create, that the following scripts ran:
Code:
@$ORACLE_HOME/rdbms/admin/catalog
@$ORACLE_HOME/rdbms/admin/catproc
@$ORACLE_HOME/rdbms/admin/catblock.sql
@$ORACLE_HOME/rdbms/admin/catexp.sql
@$ORACLE_HOME/rdbms/admin/utlxplan.sql
@$ORACLE_HOME/rdbms/admin/utltkprf.sql
(Make necessary "/"-"\" adjustments if running Windows.)

You can re-run the above scripts at any time without penalty or problem. The above scripts create hundreds of objects in the Oracle Data Dictionary. Therefore, without running these scripts, you could be missing an object ("...table or view does not exist...") that the instance is looking for upon startup.

Let us know.

[santa]Mufasa
(aka Dave of Sandy, Utah, USA)

Do you use Oracle and live or work in Utah, USA?
Then click here to join Utah Oracle Users Group on Tek-Tips.
 
I figure out what happened (by manually running each step):

while I normally dispise GUIs (my old boss would immediately disqualify a candidate for giving an answer that involved using OEM; man I miss him!) I have been essentially using the same db create scripts I wrote back in 2000 for 8.1. to see what I have may have been missing I ran dbca and had it create scripts but not run them. in looking at them I was shocked to see how hardcoded they were so went about sed-ing them to make them more generic (ex. use $ORACLE_HOME instead of absolute path) so I could cvs them for use by others.

the problem was caused by me commenting out all the "connect sys/pw as sysdba" clauses that I thought were redundant but I forget that a few things (ex. pupbld.sql) are run as system instead of sys so some scripts were ran as the wrong user. I do not know exactly which subscript caused the problem but uncommenting the connect clauses (most of which are unnecessary) caused the db to create and open w/o the error.

morals of the story:

1. GUIs are still evil (if you don't know how to do it with bash, sqlplus or perl you aren't qualified to do it)
2. dbca hardcodes EVERYTHING when it should use $ORACLE_HOME, $ORACLE_SID, etc. when possible
3. some scripts have to be run as system
4. when you only rewrite your db create script(s) every five years you forget 1-3 above.

;-)
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top