| How can a DBA influence all sessions currently logged in to an instance? Broadcast? [message #40318] |
Fri, 27 September 2002 01:45  |
Jerry Alderson
Messages: 1
Registered: September 2002
|
Junior Member |
|
|
We have a need to make all sessions currently logged in perform an action now.
You can all imagine the kind of thing:
a) I have just updated a table and I want all users to recache their copy of that table
b) I am about to upgrade part of an application and I want you all to cleanly log out now.
I'm trying to find an efficient way of doing this.
At the moment all of the sessions check the contents of a database table whenever they do anything. For example, a all PL/SQL stored procedures read a database table to check what today's date/time is (we support override dates to aid testing). This is clearly a very inefficient thing to do. There's no disk I/O, but it's a lot of context switches and SQL parsing.
An analyst has just asked me for a way to be able to turn on additional diagnostics in our PL/SQL stored procedures. Rather than put an entry on a table which all stored procedures read when they start, I would like them to have a more efficient method.
One solution might be for them to look at an instance (or database) wide global variable. Is that possible?
Another method might be for them all to have their own variable, e.g. a PL/SQL package state variable. They would read the table initially, and record the value of the flag. However, what we need to do is tell each of those sessions to re-read the table when the DBA has updated the table.
This sounds like a database event trigger which affects *all* sessions.
As I understand it, the database event triggers introduced in Oracle 8i only cause the trigger to be fired for the originating session.
What I'm looking for is a "broadcast" to all users. They then catch this message and throw some sort of action.
Does anyone know how this can be achieved in Oracle? Preferably 8.1.7, but I'm also interested in 9.0 and 9.2 solutions.
Thanks in advance.
Jerry
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
