EBS_DBA_问题:跟踪登陆db的用户

用sys用户登陆db

1.创建表:

CREATE TABLE LOG$INFORMATION
(
   ID        NUMBER(10),
   USERNAME VARCHAR2(30),
   LOGINTIME DATE,
   TERMINAL VARCHAR2(50),
   IPADRESS VARCHAR2(20),
   OSUSER    VARCHAR2(30),
   MACHINE   VARCHAR2(64),
   PROGRAM   VARCHAR2(64),
   SID       NUMBER,
   SERIAL#   NUMBER,
   AUSID     NUMBER
);

2.创建序列:

CREATE SEQUENCE LOGIN_SEQ
minvalue 1
maxvalue 9999999999
start with 1
increment by 1
cache 20;

3.创建触发器:

CREATE OR REPLACE TRIGGER LOGIN_RECORD_TR
AFTER logon ON DATABASE
DECLARE
mtSession v$session%ROWTYPE;
CURSOR cSession(iiQuerySid IN NUMBER) IS
    SELECT * FROM v$session
       WHERE audsid = iiQuerySid;
BEGIN
OPEN cSession(userenv('SESSIONID'));
   FETCH cSession INTO mtSession;
      IF cSession%FOUND AND SYS_CONTEXT ('USERENV','IP_ADDRESS') IS NOT NULL THEN
         INSERT INTO log$information(
            id,
            username,
            logintime,
            terminal,
            ipadress,
            osuser,
            machine,
            program,
            sid,
            serial#,
            ausid
         ) VALUES(
            login_seq.nextval,
            USER,
            SYSDATE,
            mtSession.Terminal,
            SYS_CONTEXT ('USERENV','IP_ADDRESS'),
            mtSession.Osuser,
            mtSession.Machine,
            mtSession.Program,
            mtSession.Sid,
            mtSession.Serial#,
            userenv('SESSIONID')
         );
      END IF;
   CLOSE cSession;
EXCEPTION
   WHEN OTHERS THEN
     RAISE;
END;

4.查询:

Select * from sys.LOG$INFORMATION
Where USERNAME = 'PDM';

5.查询sql

SELECT lk.session_id sid,
      se.serial#,
       lpad(' ', decode(lk.xidusn, 0, 3, 0)) || lk.oracle_username User_name,
        lk.locked_mode,
      lk.oracle_username,
      se.user#,
      lk.os_user_name,
      se.machine,
      se.terminal,
      a.sql_text,
      a.action
FROM v$sqlarea a, v$session se, v$locked_object lk
WHERE lk.session_id = se.sid
 AND se.prev_sql_addr = a.address
  and sid= &sid      --利用sid找到相应的sql
ORDER BY sid, se.serial#;