This discussion is archived
11 Replies Latest reply: Jan 2, 2013 12:42 AM by BillyVerreynne RSS

Message Broadcast to all connected sessions

967679 Newbie
Currently Being Moderated
Hi,

What's the method to send message to all the user whose session are active.Need to send:
-- Message to all users.
-- Message to single user.
  • 1. Re: Message Broadcast to all connected sessions
    Osama_Mustafa Oracle ACE
    Currently Being Moderated
    Refer to
    http://ayyudba.blogspot.com/2007/10/send-message-to-all-logged-in-users.html
  • 2. Re: Message Broadcast to all connected sessions
    sb92075 Guru
    Currently Being Moderated
    cooluniquesunny wrote:
    Hi,

    What's the method to send message to all the user whose session are active.Need to send:
    -- Message to all users.
    -- Message to single user.
    is application 3-tier as below?


    EndUser<=>browser<=>WebServer<=>ApplicationServer<=>DatabaseServer

    What is OS name & version for DB Server?


    How do I ask a question on the forums?
    SQL and PL/SQL FAQ
  • 3. Re: Message Broadcast to all connected sessions
    User477708-OC Journeyer
    Currently Being Moderated
    Just to note "net send" is obsolete in later versions of windows, if you get a syntax error running "net send" use the same format in that blog and dynamically generate the send statements using the dos MSG command instead. format is

    msg <os username> "message: log off the database now or a kitten will catch a cold"

    edit: as sb rightly says, if its 3 tier with shared connection, youll have to dynamically generate the command from the app servers by donig a netstat -an and look for the connected IPs on your app server port and generate it through a windows batch file and do a bit of work with the text. same theory, youre just getting the info from elsewhere to do the same thing.

    Edited by: 961469 on Dec 28, 2012 7:43 AM
  • 4. Re: Message Broadcast to all connected sessions
    967679 Newbie
    Currently Being Moderated
    I want to know:-
    If such feature of Broadcast available on Oracle 11.0.2 G or not. I want it on DB level, not on OS level.


    We can extract the connected user details from V$SESSION. Do with this info, can SYS can send a message to all connected users (Has to be done on DB level).
  • 5. Re: Message Broadcast to all connected sessions
    sb92075 Guru
    Currently Being Moderated
    since you choose to not answer my questions, I have insufficient details to answer your question.

    You're On Your Own (YOYO)!
  • 6. Re: Message Broadcast to all connected sessions
    EdStevens Guru
    Currently Being Moderated
    cooluniquesunny wrote:
    I want to know:-
    If such feature of Broadcast available on Oracle 11.0.2 G or not. I want it on DB level, not on OS level.

    Such an operation is not inherently what databases are about. If such a feature exists, it would be in a pl/sql package. Check http://docs.oracle.com/cd/E11882_01/appdev.112/e25788/toc.htm. If you don't find what you are looking for there, then you'll have your answer. If you do find such a feature there, then you'll have your answer.

    We can extract the connected user details from V$SESSION. Do with this info, can SYS can send a message to all connected users (Has to be done on DB level).
    v$session give info about the connected session as it applies to the database. If you have 3-tier architecture (client --> application server --> database) then that information is about the application server, not the end client.

    I'm sounding like a broken record here. If you'll describe the business problem you are trying to solve, instead of a pre-conceived and ill-conceived technical solution, you'll get far better help.

    Edited by: EdStevens on Dec 31, 2012 10:32 AM
  • 7. Re: Message Broadcast to all connected sessions
    BillyVerreynne Oracle ACE
    Currently Being Moderated
    cooluniquesunny wrote:
    I want to know:-
    If such feature of Broadcast available on Oracle 11.0.2 G or not. I want it on DB level, not on OS level.
    The Oracle server session is by default "passive" - meaning that such a server session will only execute and respond to explicit commands from the client.
    We can extract the connected user details from V$SESSION. Do with this info, can SYS can send a message to all connected users (Has to be done on DB level).
    You cannot push a server session to send "unexpected" data to the client, that the client has not requested. So you cannot force a server session to send a message to the client it is servicing, when that client has not explicitly requested that message from the server.

    It is also more complex with shared server sessions. In such a case there is no dedicated server process servicing that client. The client is connected via a dispatcher process (a single dispatcher can handle many client connections). A client request is passed by the dispatcher, via a virtual circuit, to an idle server process in the shared server pool.

    In this case, how is the unsolicited message from SYS to be send to a client? Who is going to send the message? Which shared server process? Keeping in mind that none of the shared server processes are servicing a single client sesion, and that a single client session can be serviced by a number of different shared server sessions during its lifetime?

     
    Bottom line. What you want to do is not possible. Oracle client sessions are not like Unix tty sessions where a root process can write a message to the display devices of all connected tty sessions - like the wall command does.

    Functionally - the business reasons for wanting this type of functionality needs to be clearly stated in detail in order to suggest possible Oracle solutions to you.

    Technically - Oracle provides a number of methods for a client to subscribe to database "messages". The most common interface for this is DBMS_ALERT. There are also more sophisticated messaging systems in Oracle like Oracle Streams Advanced Queuing (AQ). And more primitive ones like Database Pipes.

    The choice of technical solution being fully dependant on the functional requirements.
  • 8. Re: Message Broadcast to all connected sessions
    967679 Newbie
    Currently Being Moderated
    Application is 2 tier Arch.
    OS is: Red Hat Enterprise Linux 5
    Oracle version is : 11.2.0.1
  • 9. Re: Message Broadcast to all connected sessions
    967679 Newbie
    Currently Being Moderated
    Hi Stevens,

    I don't have a complex business requirement here. My only interest for this requirement is:
    -- We have multiple users logged in through various client on DB server. Its a test machine. Users are working and firing queries doing transaction. So to do a system restart, I wish to convey/pop up message on user screen, that we are going to restart DB in next 5 minutes. Kindly commit your work and logout the session, so that admin can gracefully shutdown and restart the database.
  • 10. Re: Message Broadcast to all connected sessions
    967679 Newbie
    Currently Being Moderated
    Hi Billy,
    I am quite satisfied with your points providing inside view of Oracle Architecture.

    I was looking for a solution just like WALL in REDHAT.

    My simple business requirement here is .
    We have multiple users logged in through various client on DB server. Its a test machine. Users are working and firing queries doing transaction. So to do a system restart, I wish to convey/pop up message on user screen, that we are going to restart DB in next 5 minutes. Kindly commit your work and logout the session, so that admin can gracefully shutdown and restart the database.

    I will study the other method which you have suggested for client to subscribe database messages.
  • 11. Re: Message Broadcast to all connected sessions
    BillyVerreynne Oracle ACE
    Currently Being Moderated
    There are no wall message options for Oracle db client-server. You can for example use DBMS_ALERT - but many client sessions will not subscribe and poll for alerts. As they simply do not support this feature. So there is no way to push (from the database itself), a message to all connected clients.

    The workaround is to have the database send a NetBIOS message to clients via the o/s. But then this will only work for Window clients running NetBIOS, with an active NetBIOS messaging service, and configured to accept messages from the database server.

    As for the business requirement - we use the official office communication channel to inform users that a database is going down. Which means via the company's e-mail system. There is an official record in black-and-white of the notification. The Mail Server has logs of the message and delivery.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points