Friday, June 05, 2015

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…)

No comments: