Intersession Communication and how Oracle deals with it

Communication is something that we all need no matter what we do and where we are. It can solve problems which in other ways would have been very difficult or costly. We do need to communicate where ever we need to work with each other, same goes for database users. A typical form of communication while working on database and the one we all do one way or the other is sharing of data. 

Suppose you have a large table that consist of hundreds of thousands of rows in one schema. There may be a time when that same piece of information is required by another database schema. Without the availability of proper communication channel between these two schema we will had to export data from first schema and import into the second one. This is time consuming, requires more effort and resources and also negates one of the primary advantage of using database i.e. no data duplication. But of course we don't do things this way in real life. Two simple solutions are to grant second schema access to the specific table in first schema or if that is not feasible then create a synonym over the table and grant access to that synonym to the second user. This is Intersession communication and even more...

Intersession communication means two user sessions communicating with each other. The user sessions can belong to the single user or they can be of two different users. Oracle Database 10g provides two methods for doing this. First one is that in which there is a requirement of permanent or semi-permanent database structures for example tables, synonyms etc (mentioned above). This type of communication is feasible if you want your data to be shared with other users but is not a good practice for message communication. Obviously nobody will like to create database structures just for the sake of communication with the other user. Secondly this type of communication is limited by Transaction Control. Transaction Control means that a user must complete and commit the changes to the database for other users to see.

The second method is through two built in PL/SQL packages namely DBMS_PIPE and DBMS_ALERT. DBMS_PIPE uses memory structures called pipes to send and receive messages from one user session to the other. The pipes are pretty much similar to UNIX pipes. The advantage is there is no requirement of permanent or semi-permanent database structures. The communication takes place in memory. And also this type of communication is not limited by Transaction Control limitations. DBSM_ALERT works pretty much similar and implements the publish and subscribe paradigm. An event is published and users that subscribed for the event gets notified about the event. The structure used by this package is not formally referred to as pipe but works like public pipes. 

Also posted on Tech Blessing.