Weird Schema issue

[Patrick Newport]

I've been experiencing a strange schema issue which has started very recently.

When I connect to a database under a schema, I can see all the objects in the Browser as usual... packages, tables, triggers, etc. However, if I compile a new package to the database, then login from a separate machine with the same schema and database, the package is not found. I can go back to my machine though, reboot, and retrieve the package from the database. If I drop the package from my machine's PLSQL, and another machine creates the same named package on the database, I cannot see their copy.

Furthermore, I can try to step through code that calls it, and PLSQL will hang with maximum CPU processing time.

Any suggestions at all? I've tried a complete uninstall and reinstall with no success.

 

[Marco Kalter]

If your Browser Filter is set to "My objects", you will only see the packages from the current schema. You will need to set it to "All objects" to see packages owned by others.

Futhermore, another user's package bodies will only be visible if you have select privileges on the sys.dba_objects and sys.dba_source views. The "Use DBA views if available" preference (Tools > Preferences > Options tab page) needs to be enabled.

 

[ScottMattes]

Marco,
I think Patrick means

- on PC A log onto DB1, schema XYZ, create package
- on PC B log onto DB1, schema XYZ, can't see new package
- go back to PC A, reboot Windows, log onto DB1, schema XYZ, can see new package

I think.

 

[Patrick Newport]

That is correct. It is the same schema on two different PCs, connecting to the same server, but it is almost like they are pulling back two separate instances (though v$session disproves this.)

Another interesting feature is that even though I could typically run debug on this machine before, it now hangs on a pipe get (this is with no changes to the user's configuration on the database.)

 

[Marco Kalter]

On 2 different machines the "Use DBA views if available" might be different. Perhaps you can verify this?

 

[Patrick Newport]

Unfortunately, they are set the same.

I left the process running and finally got an error "Unable to allocate 252816 bytes of shared memory," so it looks like it is just going on a memory eating loop or something.

 

[Marco Kalter]

I'm not aware of any loops (on the Server). Maybe this is a problem with the state of the shared memory pool. Can you shutdown/restart the instance?