AFTER LOGON ON DATABASE
DECLARE
IP VARCHAR2(30);
UNAME VARCHAR2(30);
Begin
select SYS_CONTEXT('USERENV','IP_ADDRESS'),USERNAME into IP,UNAME from V$SESSION
WHERE AUDSID=USERENV('SESSIONID');
IF IP != '192.168.0.1' and UNAME != 'PLM' then
RAISE_APPLICATION_ERROR(-1,'CONNECTION REFUSED');
END IF;
END;
ERROR:
ORA-00604: error occurred at recursive SQL level 1
ORA-21000: error number argument to raise_application_error of -1 is out of
range
ORA-06512: at line 8
而不是4098。
这里的问题我认为在于,lz在建立这个trigger的时候,出现了如下error吧:
SQL> show error
Errors for TRIGGER PLM.LOGON_DB_TRIGGER:
LINE/COL ERROR
-------- -----------------------------------------------------------------
5/4 PL/SQL: SQL Statement ignored
5/75 PL/SQL: ORA-00942: table or view does not exist
这是因为v$session找不到。
所以,解决的办法就是到sys用户:
grant select on v_$session to plm;
重新compile这个trigger。
----------------下面我模拟lz的错误--------------------
SQL> create user plm identified by plm;
User created.
SQL> grant dba to plm;
Grant succeeded.
通过plm登陆:
SQL> CREATE OR REPLACE TRIGGER PLM.LOGON_DB_TRIGGER
2 AFTER LOGON ON DATABASE
3 DECLARE
IP VARCHAR2(30);
UNAME VARCHAR2(30);
Begin
select SYS_CONTEXT('USERENV','IP_ADDRESS'),USERNAME into IP,UNAME from V$SESSION
WHERE AUDSID=USERENV('SESSIONID');
IF IP != '192.168.0.1' and UNAME != 'PLM' then
RAISE_APPLICATION_ERROR(-1,'CONNECTION REFUSED');
END IF;
END; 4 5 6 7 8 9 10 11 12
13 /
Warning: Trigger created with compilation errors.
SQL> show error
Errors for TRIGGER PLM.LOGON_DB_TRIGGER:
LINE/COL ERROR
-------- -----------------------------------------------------------------
5/4 PL/SQL: SQL Statement ignored
5/75 PL/SQL: ORA-00942: table or view does not exist
通过另外某个用户登陆:
> sqlplus testbyhao/xxx
SQL*Plus: Release 10.2.0.4.0 - Production on Fri Jun 19 05:33:32 2009
Copyright (c) 1982, 2007, Oracle. All Rights Reserved.
ERROR:
ORA-04098: trigger 'PLM.LOGON_DB_TRIGGER' is invalid and failed re-validation
返回sys:
SQL> grant select on v_$session to plm;
Grant succeeded.
返回plm:
SQL> alter trigger LOGON_DB_TRIGGER compile;
Trigger altered.
再通过其他用户登陆,得到期望的error:
sqlplus testbyhao/xxx@xxx
SQL*Plus: Release 10.2.0.4.0 - Production on Fri Jun 19 05:39:34 2009
Copyright (c) 1982, 2007, Oracle. All Rights Reserved.
ERROR:
ORA-00604: error occurred at recursive SQL level 1
ORA-21000: error number argument to raise_application_error of -1 is out of
range
ORA-06512: at line 8
restart instance ,drop trigger,再修改: _system_trig_enabled=true;
或者 delete from trigger$
CREATE TRIGGER TRI_LOGON
AFTER LOGON ON DATABASE
BEGIN
INSERT INTO LOGON_TABLE VALUES (SYS_CONTEXT('USERENV', 'HOST'),
SYSDATE);
END;
现在有一个问题:
打个比方:我假设当LOGON_TABLE所使用的用户表空间满了,那么就无法往LOGON_TABLE
里写入数据,那么此时候触发器就会抱错误,个人理解是不是这样,oracle 系统的所有用户
都没办法登陆。
当表空间满的时间,登陆都是可以的,只是没有再往表中insert数据。
我的步骤如下:
1.建立一个表空间大小为1M
CREATE TABLESPACE test01
DATAFILE 'E:ORACLEPRODUCT10.2.0ORADATAORCLTEST01.DBF'
SIZE 1M;
2.建立一个表,表空间为test01
CREATE TABLE LOGON_TABLE(info VARCHAR2(50),logondate date)
TABLESPACE test01;
3.向表中循环insert 数据,将表空间全部用完
BEGIN
FOR i IN 1..29840 LOOP --这里你可循环insert当直到表空间用满为止
insert into logon_table values(SYS_CONTEXT('USERENV', 'HOST'),sysdate);
END LOOP;
COMMIT;
END;
如果再向其存入数据,则会出现如下错误:
SQL> insert into logon_table values(SYS_CONTEXT('USERENV', 'HOST'),sysdate);
insert into logon_table values(SYS_CONTEXT('USERENV', 'HOST'),sysdate)
*
第 1 行出现错误:
ORA-01653: 表 SYS.LOGON_TABLE 无法通过 8 (在表空间 TEST01 中) 扩展
4.建立登录事件触发器
CREATE TRIGGER TRI_LOGON
AFTER LOGON ON DATABASE
BEGIN
INSERT INTO LOGON_TABLE VALUES (SYS_CONTEXT('USERENV', 'HOST'),
SYSDATE);
END;
6.查看现在表中的数据是多少
SQL> select count(*) from logon_table;
COUNT(*)
----------
29840
7.以别的用户反复登录
SQL> connect fttest/fttest
已连接。
SQL> connect /as sysdba
已连接。
8.再查看表中的数据记录数,没有变化,但登录仍正常。
所以--我得出上面的结论
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/13165828/viewspace-606970/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/13165828/viewspace-606970/