If you're using a logon trigger to turn on tracing for the session, you're changing the session's environment, and therefore you're causing the optimizer to generate new plans. In your case, this could mean that a "bad" index is not being used in the new plans, allowing the sql to execute successfully. What you can do to further analyze it, is - analyze table <table_name> validate structure cascade; or - analyze index <index_name> validate structure; to see if there's logical corruptions. HTH Cheers Stefan On Wed, Jun 25, 2008 at 2:31 AM, Tony Adolph <tony.adolph.dba@xxxxxxxxx> wrote: > Hi folks, > > I don't know if I have a database or an Oracle BRM (Infranet) problem. > > Its been a seemingly random problem over the last year and goes > something like this: > > A test environment is "rebuilt" > -o- test user's objects all dropped > -o- test user's object re-created from an export from a "reference" schema > -o- test schema has no invalid objects > -o- on the application server the cm and dm are started. > > Now we run testnap to check the cm (connection manager) -> dm > (database manager) -> database connectivity. > > it throws an error: > > ERROR: testnap: pcm_connect():: err 56:PIN_ERR_BAD_LOGIN_RESULT, field > 0/1:PIN_FLD_ERR_BUF, > loc 6:PIN_ERRLOC_FLIST, errclass > 1:PIN_ERRCLASS_SYSTEM_DETERMINATE, rec_id 0, resvd 0 > > If I create a logon trigger to trace this user's session to find out > what's going on, start and stop the cm/dm > testnap works. But when I drop the trigger, bounce the cm / dm and > try again, it fails. > > The only fix we've found to date is to rebuild an index on SERVICE_T, any > index. > But note as mentioned above, none of these indexes were invalid,... > nothing is invalid. > > Everything then seems to run fine for weeks / months, then fails again > in the same way... same fix. > > I'm struggling with this one,..I can't see why rebuilding any index on > this table would > fix the problem. > > Is it something to do with invalidating plans? > But then, why should a plan cause testnap to fail and why would > tracing the session "fix" the problem? > > SQL> show parameter cursor > > NAME TYPE VALUE > ------------------------------------ ----------- > ------------------------------ > cursor_sharing string exact > cursor_space_for_time boolean FALSE > open_cursors integer 1080 > session_cached_cursors integer 0 > > We're running Infranet 7.0 > Oracle9i Enterprise Edition Release 9.2.0.6.0 (going to 9.2.0.8 soon) > > I'd appreciate any ideas > > Cheers > Tony > -- > //www.freelists.org/webpage/oracle-l > > > -- ========================= Stefan P Knecht Senior Consultant Infrastructure Managed Services Trivadis AG Europa-Strasse 5 CH-8152 Glattbrugg Phone +41-44-808 70 20 Fax +41-808 70 12 Mobile +41-79-571 36 27 stefan.knecht@xxxxxxxxxxxx http://www.trivadis.com OCP 9i/10g SCSA SCNA =========================