1.sysdba/sysoper 权限用户验证日志;
2.非sysdba/sysoper 权限用户验证日志;
3.关于sqlcode;
1.sysdba/sysoper 权限用户验证日志:
在数据库设置了参数 audit_sys_operations=true 的情况下,系统会根据 audit_trail 参数的设置记录 sysdba/sysoper 权限用户日志到 audit_file_dest
参数设置的目录下,记录日志的内容包括(数据库启动操作、登录验证信息、DML操作),其它非 sysdba/sysoper 权限的用户信息会被记录到AUD$表中;
audit_trail 可以设置为:DB、DB,EXTENDED、OS、XML,当audit_trail=xml 时产生的日志数量多慎用;
1 SYS@sydb>show parameter audit 2 3 NAME TYPE VALUE 4 ------------------------------------ ----------- ------------------------------ 5 audit_file_dest string /u01/app/admin/sydb/adump 6 audit_sys_operations boolean TRUE 7 audit_syslog_level string 8 audit_trail string DB 9 10 [sywu@wusuyuan ~]$ cd /u01/app/admin/sydb/adump 11 [sywu@wusuyuan adump]$ rm * 12 [sywu@wusuyuan adump]$ sqlplus / as sysdba 13 SYS@sydb>show parameter audit 14 15 查看跟踪文件: 16 [sywu@wusuyuan adump]$ ls 17 sydb_ora_26587_1.aud 18 [sywu@wusuyuan adump]$ cat sydb_ora_26587_1.aud 19 Thu May 29 12:48:05 2014 +08:00 20 LENGTH : '158' 21 ACTION :[7] 'CONNECT' --执行操作,相关操作参照 audit_actions 表 22 DATABASE USER:[1] '/' 23 PRIVILEGE :[6] 'SYSDBA' 24 CLIENT USER:[4] 'sywu' 25 CLIENT TERMINAL:[5] 'pts/0' 26 STATUS:[1] '0' --状态,登录成功为:'0',失败为:'1017',值是数据库返回的sqlcode, 27 DBID:[10] '3589320526' 28 29 Thu May 29 12:48:05 2014 +08:00 30 LENGTH : '188' 31 ACTION :[36] 'BEGIN DBMS_OUTPUT.ENABLE(NULL); END;' 32 DATABASE USER:[1] '/' 33 PRIVILEGE :[6] 'SYSDBA' 34 CLIENT USER:[4] 'sywu' 35 CLIENT TERMINAL:[5] 'pts/0' 36 STATUS:[1] '0' 37 DBID:[10] '3589320526' 38 39 Thu May 29 12:48:23 2014 +08:00 40 LENGTH : '442' --show parameter audit 语句产生的后台查询 41 ACTION :[289] 'SELECT NAME NAME_COL_PLUS_SHOW_PARAM,DECODE(TYPE,1,'boolean',2,'string',3,'integer',4,'file',5,'number', 6,'big integer', 'unknown') TYPE,DISPLAY_VALUE VALUE_COL_PLUS_SHOW_PARAM FROM V$PARAMETER WHERE UPPER(NAME) LIKE UPPER(:NMBIND_SHOW_OBJ) ORDER BY NAME_COL_PLUS_SHOW_PARAM,ROWNUM' 42 DATABASE USER:[1] '/' 43 PRIVILEGE :[6] 'SYSDBA' 44 CLIENT USER:[4] 'sywu' 45 CLIENT TERMINAL:[5] 'pts/0' 46 STATUS:[1] '0' 47 DBID:[10] '3589320526'
如果数据库启动为只读模式,则audit_trail 值默认为:OS;
1 AUDIT_TRAIL initialization parameter is changed to OS, as DB,EXTENDED is NOT compatible for database opened with read-only access
2.非sysdba/sysoper 权限用户验证日志:
非sysdba/sysoper 权限用户验证日志会被记录在AUD$系统表上,aud$记录用户验证userId,userhost,action#,comment$text,process#,dbid..,当用户断开连接时
记录用户断开时间(LOGOFF$TIME);
1 SYS@sydb> select sessionid,userhost,userid,to_char(TIMESTAMP#,'YYYY-MM-DD HH24:mi:ss')TIMESTAMP,action#,process#,LOGOFF$TIME,RETURNCODE 2 2 from aud$ where userid=upper('SYWU'); 3 4 SESSIONID USERHOST USERID TIMESTAMP ACTION# PROCESS# LOGOFF$TIME RETURNCODE 5 ---------- --------------- ------------- ------------------- ---------- ---------------- ------------------- ---------- 6 ########## wusuyuan SYWU 101 25404 2014-06-04 12:40:24 0 7 ########## wusuyuan SYWU 100 25411 0 8 ########## wusuyuan SYWU 101 25411 2014-06-04 12:41:18 0 9 ########## wusuyuan SYWU 100 25413 0 10 ########## wusuyuan SYWU 101 25413 2014-06-04 12:46:51 0 11 ########## wusuyuan SYWU 100 25444 0 12 ########## wusuyuan SYWU 101 25444 2014-06-04 12:50:36 0
sys.AUD$ 表所有者是SYS用户,占用SYSTEM表空间,长时间使用会存储很多信息,可以在sysdba权限用户下(截取、删除)数据:
1 SYS@sydb>truncate table aud$;
也可以将表存储到其它表空间下:
1 SYS@sydb>alter table aud$ move tablespace tbs02;
3.关于sqlcode:
在执行任何sql 时,后台都会返回sqlcode值,执行正确默认返回'0',如果发生错误或异常是返回错误值,类似我们看到的 ORA-00001 错误;
1 OPS$LANSTON@sydb>declare 2 2 v_date date; 3 3 begin 4 4 select sysdate into v_date from dual; 5 5 dbms_output.put_line('this is v_date value:'||v_date); 6 6 dbms_output.put_line('this is database return sqlcode:'||sqlcode); 7 7 end; 8 8 / 9 this is v_date value:2014-06-04 21:12:51 10 this is database return sqlcode:0 11 12 PL/SQL procedure successfully completed.
当执行出错时:
1 OPS$LANSTON@sydb> declare 2 2 v_date date; 3 3 begin 4 4 execute immediate 'select tcol from dual' into v_date; 5 5 6 6 exception when others then 7 7 dbms_output.put_line('this is database return sqlcode:'||sqlcode); 8 8 dbms_output.put_line('this is database return error message:'||sqlerrm); 9 9 end; 10 10 / 11 this is database return sqlcode:-904 12 this is database return error message:ORA-00904: "TCOL": invalid identifier 13 14 PL/SQL procedure successfully completed.
在PL/SQL体内执行DML操作时,建议使用 execute immediate 执行,这样容易捕获错误,并且可以在异常体对异常处理;如上例子如果不使用execute immediate 执行,数据库
在第一时间就抛出错误并终止程序运行;