Oracle Pipes (Part 1)

Oracle pipes use the DBMS_PIPE package to allow one session to communicate with another. I decided to try this out in an Oracle 11.1 database. Pipes can be public or private. This example only looks at public pipes. First I created a user called ANDREW. I gave it SELECT ANY DICTIONARY so that it could look at V$DB_PIPES and I allowed it to execute DBMS_PIPE so that it would be able to create a pipe: 

SQL> conn / as sysdba

Connected.

SQL> create user andrew identified by reid

  2  /

 

User created.

 

SQL> grant create session to andrew

  2  /

 

Grant succeeded.

 

SQL> grant select any dictionary to andrew

  2  /

 

Grant succeeded.

 

SQL> grant execute on sys.dbms_pipe to andrew

  2  /

 

Grant succeeded.

 

SQL> 

Then I logged in as the user and queried V$DB_PIPES to ensure there were no pipes in the database to begin with: 

SQL> conn andrew/reid

Connected.

SQL> col name format a20

SQL> select * from v$db_pipes

  2  /

 

no rows selected

 

SQL>

 

I used DBMS_PIPE.PACK_MESSAGE to create a message then I used DBMS_PIPE.SEND_MESSAGE to send it down a pipe, which I called ANDREWS_PIPE. If you do it this way, Oracle creates the pipe implicitly with the name you provide: 

SQL> declare

  2   result number;

  3  begin

  4   dbms_pipe.pack_message('Andrew was here');

  5   result := dbms_pipe.send_message('andrews_pipe');

  6  end;

  7  /

 

PL/SQL procedure successfully completed.

 

SQL> 

I queried V$DB_PIPES again and saw that Oracle had created the pipe: 

SQL> select * from v$db_pipes

  2  /

 

   OWNERID NAME                 TYPE     PIPE_SIZE

---------- -------------------- ------- ----------

           ANDREWS_PIPE         PUBLIC        1621

 

SQL>

I decided that I did not want the pipe any more so I flushed the shared pool:

SQL> conn / as sysdba

Connected.

SQL> alter system flush shared_pool

  2  /

 

System altered.

 

SQL>

I just did this to show that flushing the shared pool by itself does not remove a pipe. To prove this, I queried V$DB_PIPES again and saw that the pipe was still there:

SQL> select * from v$db_pipes

  2  /

 

   OWNERID NAME                 TYPE     PIPE_SIZE

---------- -------------------- ------- ----------

           ANDREWS_PIPE         PUBLIC        1621

 

SQL>

Then I used DBMS_PIPE.PURGE to clear the contents of the pipe:

SQL> exec dbms_pipe.purge('andrews_pipe');

 

PL/SQL procedure successfully completed.

 

SQL>

Once you have done this, an implicitly created pipe can be aged out of the SGA. I queried V$DB_PIPES and saw that the pipe was still there:

SQL> select * from v$db_pipes

  2  /

 

   OWNERID NAME                 TYPE     PIPE_SIZE

---------- -------------------- ------- ----------

           ANDREWS_PIPE         PUBLIC        1621

 

SQL> 

I wanted to finish this test but did not have time for the pipe to be aged out of the SGA so I flushed the shared pool to speed things along: 

SQL> alter system flush shared_pool

  2  /

 

System altered.

 

SQL>

… and when I queried V$DB_PIPES again, the pipe had gone:

SQL> select * from v$db_pipes

  2  /

 

no rows selected

 

SQL>

 

(More to follow…)