Oracle Session Infos
With the following Statement on can get many infos about the processes currently running in an oracle database (assumed you have the necessary privileges)
SELECT /*+ choose */
s.status "Status", s.serial# "Serial#", s.TYPE "Type",
s.username "DB User", s.osuser "Client User", s.server "Server",
s.machine "Machine", s.module "Module", s.client_info "Client Info",
s.terminal "Terminal", s.program "Program", p.program "O.S. Program",
s.logon_time "Connect Time", lockwait "Lock Wait",
si.physical_reads "Physical Reads", si.block_gets "Block Gets",
si.consistent_gets "Consistent Gets",
si.block_changes "Block Changes",
si.consistent_changes "Consistent Changes", s.process "Process",
p.spid, p.pid, si.sid, s.audsid, s.sql_address "Address",
s.sql_hash_value "Sql Hash", s.action
FROM v$session s, v$process p, sys.v_$sess_io si
WHERE s.paddr = p.addr(+)
AND si.sid(+) = s.sid
AND (s.username IS NOT NULL)
AND (NVL (s.osuser, 'x') <> 'SYSTEM')
AND (s.TYPE <> 'BACKGROUND')
ORDER BY 3
DBMS_Alert signals when inserting in a table
Sometimes you want to be informed, when data are inserted in a oracle table.
One possibility is to use the sys.dbms_alert package to send signals to other
client progams. Here first a simple example about the usage of the package methods:
DECLARE
NAME VARCHAR2(200);
MESSAGE VARCHAR2(200);
STATUS NUMBER;
TIMEOUT NUMBER;
BEGIN
NAME := 'HURZ';
MESSAGE := 'This is a test';
SYS.DBMS_ALERT.REGISTER ( NAME );
SYS.DBMS_ALERT.SIGNAL ( NAME, MESSAGE );
commit;
STATUS := NULL;
TIMEOUT := 20;
SYS.DBMS_ALERT.WAITONE ( NAME, MESSAGE, STATUS, TIMEOUT );
DBMS_OUTPUT.Put_Line('MESSAGE = ' || MESSAGE);
DBMS_OUTPUT.Put_Line('STATUS = ' || TO_CHAR(STATUS));
END;
Now after some first tests, how can this be used in a real world example?
- Let's assume we have created a test-user with the name ALERT_TESTER with the commands [as user "SYS AS SYSDBA"]:
create user alert_tester identified by YOUR_ALERT_TESTER_PASSWORD;
GRANT CONNECT, RESOURCE to alert_tester;
- The user which wants to use the SYS.DBMS_ALERT package has to have the necessary rights i.e. the EXECUTE right for package SYS.DBMS_ALERT. This can be granted by the command:
GRANT EXECUTE ON SYS.DBMS_ALERT to alert_tester;
- Login now as user ALERT_TESTER and create a test table with:
CREATE TABLE ALERT_TESTER.Alert_table
(
id NUMBER,
name VARCHAR2(200)
)
- A trigger which fires a alert when some data are inserted can be created by:
CREATE OR REPLACE TRIGGER ALERT_TRIGGER
AFTER INSERT ON Alert_table
FOR EACH ROW
BEGIN
DBMS_ALERT.SIGNAL('Alert_table#ALERT', 'Data inserted into table Alert_table');
END ALERT_TRIGGER;
/
- The trigger can now be tested by executing the following command from a first sql-client, which will block until some alert arrives or a timeout is reached:
DECLARE
NAME VARCHAR2(200);
MESSAGE VARCHAR2(200);
STATUS NUMBER;
TIMEOUT NUMBER;
BEGIN
NAME := 'Alert_table#ALERT';
MESSAGE := 'DUMMY';
SYS.DBMS_ALERT.REGISTER ( NAME );
STATUS := NULL;
TIMEOUT := 60;
SYS.DBMS_ALERT.WAITONE ( NAME, MESSAGE, STATUS, TIMEOUT );
DBMS_OUTPUT.Put_Line('MESSAGE = ' || MESSAGE);
DBMS_OUTPUT.Put_Line('STATUS = ' || TO_CHAR(STATUS));
END;
- During the last statement blocks you should insert some data to the test table in a second sql-client and commit the transaction. After the commit
you should see a message from the trigger in the first sql-client:
INSERT INTO ALERT_TESTER.Alert_table (ID,NAME) Values (0,'Test');
commit
Further information can be found for example
here
