Oracle Sessions

Just like for SQL Server, you may want to know what is going on in Oracle from inside Greenplum.  Here is an External Web Table that uses Outsourcer to give you that information.

First create a connection for Outsourcer:

INSERT INTO os.ext_connection
(type, server_name, instance_name, port, database_name, user_name, pass)
VALUES
('oracle', 'jonnywin', null, 1521, 'xe', 'os_test', 'os_password');

--get the ID you just inserted
SELECT id 
FROM os.ext_connection 
WHERE type = 'oracle' 
AND server_name = 'jonnywin'
AND port = 1521
AND database_name = 'xe';

--in my example, the value is 3.

Next create the schema in Greenplum for this table:

CREATE SCHEMA oracle;

This will create the External Table in Greenplum for Outsourcer:

--notice the double escaping of the dollar sign.
SELECT os.fn_create_ext_table('oracle.sessions', ARRAY[
'username varchar(30)','osuser varchar(30)','machine varchar(64)',
'terminal varchar(30)','program varchar(48)',
'module varchar(48)','sql_text text','logon_time timestamp',
'service_name varchar(64)'], 
3,
'SELECT s.USERNAME, s.OSUSER, s.MACHINE, s.TERMINAL, s.PROGRAM, s.MODULE,
a.sql_text, s.LOGON_TIME, s.service_name 
FROM v\\\\$session s, v\\\\$sqlarea a 
WHERE a.address = s.sql_address');

Now you can execute the query to get the session information:

select * from oracle.sessions;

More information on External Tables is in the Outsourcer documentation.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.