Uncommon Oracle Startup Errors

An installation running Oracle 9iR2 databases in production had non-typical messages in the alert.log. The databases had been installed on a fresh OS install, so there were no upgrade issues. The databases had been running for over a year.

During startup, non-default parameters are listed in the alert log. There were several messages about deprecated mts_ parameters. The installation DBA had taken favorite Oracle 8i settings and dropped them into Oracle 9i. The DBA had English as a second language, and did not investigate the meaning of deprecate. The alert.log message did not include an "ORA-99999", so the DBA ignored the message.
Simply setting the values to zero did not remove the messages - the parameters needed to be removed from the spfile. From alert.log:

Deprecated system parameters with specified values:
mts_servers
mts_max_servers
mts_max_dispatchers
End of deprecated system parameter listing

The next alert.log message was, "Oracle instance running on a system with low open file descriptor limit. Tune your system to increase this limit to avoid severe performance degradation."
This message did not include an "ORA-99999", so this message was also ignored. The solution for this issue was to increase the operating system file descriptors. In the case of HP-UX 11.11, query the OS kernel to check the open file limits:

$ /usr/sbin/kmtune |grep files
maxfiles 4000 - 4000
maxfiles_lim 4000 Y 4000

Oracle EXP-00091, Exporting Questionable Statistics

When performing an Oracle export, you may receive error, "EXP-00091: Exporting questionable statistics."

The first thing to do is gather a little more information with "oerr" on the command line:
$ oerr exp 91
00091, 00000, "Exporting questionable statistics."
// *Cause: Export was able export statistics, but the statistics may not be
// usuable. The statistics are questionable because one or more of
// the following happened during export: a row error occurred, client
// character set or NCHARSET does not match with the server, a query
// clause was specified on export, only certain partitions or
// subpartitions were exported, or a fatal error occurred while
// processing a table.
// *Action: To export non-questionable statistics, change the client character
// set or NCHARSET to match the server, export with no query clause,
// export complete tables. If desired, import parameters can be
// supplied so that only non-questionable statistics will be imported,
// and all questionable statistics will be recalculated.

Then search Oracle's documentation:
The precalculated optimizer statistics are flagged as questionable at export
time if:
* There are row errors while exporting
* The client character set or NCHAR character set does not match the server character set or NCHAR character set
* A QUERY clause is specified
* Only certain partitions or subpartitions are exported

The NCHAR setting often causes this message, and the message will often be avoided by setting the NLS_LANG environment variable to match the database setting.

Questionable statistics are ususally a minor inconvenience, because after the import it is easy to re-analyze. Additionally, you may set "statistics=safe" on the import to skip importing questionable statistics.

Oracle Role Insufficient For User's Procedure To Compile

Roles are useful, though there are a few limitations. This is one limitation of Oracle roles.

Table owned by userA. UserB has "select any table" and creates SQL that sucessfully runs in sql*plus. When userB tries to put the SQL in a package, there is ORA-00942 compile error.
The solution is to directly grant the userA.table_name SELECT privilege to userB. It's not enough to have a role that allows the SELECT permission.

Example:
Create package header with no problems, then try to create package body:

Warning: Package Body created with compilation errors.

SQL> show err
Errors for PACKAGE BODY xPKG:

LINE/COL ERROR
--------
-----------------------------------------------------------------
27/7 PL/SQL: SQL Statement ignored
72/29 PL/SQL: ORA-00942: table or view does not exist

-- In another session, directly grant SELECT on table_name to this user.

-- In this session, re-submit package body.

SQL> /
Package body created.