DBMS_SESSION.set_context提示ORA-01031问题解决

联系:手机/微信(+86 17813235971) QQ(107644445)QQ咨询惜分飞

标题:DBMS_SESSION.set_context提示ORA-01031问题解决

作者:惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]

最近给客户把oracle数据库从11.2.0.3 aix平台迁移到19.23 linux平台,使用impdp+network_link 按照用户的方式处理,结果发现有一个程序运行异常
ORA-01031


ORA-01031: insufficient privileges通过程序跟踪确认是在调用以下部分异常

 DBMS_SESSION.set_context (
         'back_exec',
         'back_exec_log_no',
         v_back_exec_log_no
……

通过跟踪执行用户权限,确认EXECUTE ON SYS.DBMS_SESSION已经授权.做一个简单测试重现给问题:

SQL> show user;
USER 为 "SYS"

SQL> exec DBMS_SESSION.SET_CONTEXT('test_ctx', 'a', '1');
BEGIN DBMS_SESSION.SET_CONTEXT('test_ctx', 'a', '1'); END;

*
第 1 行出现错误:
ORA-01031: 权限不足
ORA-06512: 在 "SYS.DBMS_SESSION", line 114
ORA-06512: 在 line 1

SQL> GRANT EXECUTE ON SYS.DBMS_SESSION TO sys;

授权成功。

SQL> exec DBMS_SESSION.SET_CONTEXT('test_ctx', 'a', '1');
BEGIN DBMS_SESSION.SET_CONTEXT('test_ctx', 'a', '1'); END;

*
第 1 行出现错误:
ORA-01031: 权限不足
ORA-06512: 在 "SYS.DBMS_SESSION", line 114
ORA-06512: 在 line 1

基于这种情况,肯定不是权限问题,查询官方DBMS_SESSION.set_context部分描述
dbms_session.set_context


确认namespace:The namespace of the application context to be set, limited to 128 bytes. Exceeding the maximum permissible length will result in an error during the execution of the procedure.

SQL> create context test_ctx using sys.DBMS_SESSION;

上下文已创建。

SQL> exec DBMS_SESSION.SET_CONTEXT('test_ctx', 'a', '1');

PL/SQL 过程已成功完成。

官方有相关的执行例子:Example: Creating a Global Application Context that Uses a Client Session ID

触发器找出密码错误应用

联系:手机/微信(+86 17813235971) QQ(107644445)QQ咨询惜分飞

标题:触发器找出密码错误应用

作者:惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]

经常会遇到修改oracle业务用户密码之后,由于部分应用密码没有被正确修改(忘记修改,或者修改错误了),导致数据库被hang或者用户被锁的情况,对于这样的情况,通过一个logon触发器可以快速找到

create or replace trigger logon_denied_to_alert
after servererror on database
declare
 message   varchar2(120);
 IP        varchar2(15);
 v_os_user varchar2(80);
 v_module  varchar2(50);
 v_action  varchar2(50);
 v_pid     varchar2(10);
 v_sid     number;
 v_username  varchar2(50);
 v_suser      varchar2(50);
begin
 IF (ora_is_servererror(1017)) THEN
   if sys_context('userenv', 'network_protocol') = 'tcp' then
     IP := sys_context('userenv', 'ip_address');
   else
     select distinct sid into v_sid from sys.v_$mystat;
     SELECT p.SPID
       into v_pid
       FROM V$PROCESS p, V$SESSION v
      WHERE p.ADDR = v.PADDR
        AND v.sid = v_sid;
   end if;
   v_os_user := sys_context('userenv', 'os_user');
   v_username := sys_context('userenv', 'CURRENT_USER');
   v_suser := SYS_CONTEXT('USERENV','SESSION_USER');
   dbms_application_info.READ_MODULE(v_module, v_action);
   message := to_char(sysdate, 'Dy Mon dd HH24:MI:SS YYYY') ||
              ' logon denied from '||v_username||' '||v_suser||' '|| nvl(IP, v_pid) || ' ' || v_os_user ||
              ' with ' || v_module || ' ' || v_action;
   sys.dbms_system.ksdwrt(2, message);
 end if;
end;
/

测试

SQL> create user test identified by oracle;

User created.

SQL> grant dba to test;

Grant succeeded.

直接本地登录

SQL> conn test/test
ERROR:
ORA-01017: invalid username/password; logon denied


Warning: You are no longer connected to ORACLE.

---alert日志记录
Wed Nov 01 23:15:04 2023 logon denied from SYS  12886 oracle with sqlplus@iZbp1hx0enix3hix1kvyrxZ (TNS V1-V3) 

通过tns登录

SQL> conn  test/oracl1@172.27.54.81:1522/orcl
ERROR:
ORA-01017: invalid username/password; logon denied


Warning: You are no longer connected to ORACLE.

---alert日志报错
Wed Nov 01 23:31:51 2023 logon denied from SYS  172.27.54.81 oracle with sqlplus@iZbp1hx0enix3hix1kvyrxZ (TNS V1-V3) 

plsql 插入blob

联系:手机/微信(+86 17813235971) QQ(107644445)QQ咨询惜分飞

标题:plsql 插入blob

作者:惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]

plsql 插入blob

SQL> create bigfile tablespace t_xifenfei datafile 'e:/oradata/orcl/t_blob.dbf' size 128M autoextend on;

表空间已创建。


SQL> create table u0425.t_blob(a int, b varchar(10), c blob) tablespace t_xifenfei ;

表已创建。


SQL> create or replace directory expdp_dir as 'e:/';

目录已创建。


SQL> declare
  2  b_file bfile;
  3  b_lob blob;
  4  begin
  5  insert into u0425.t_blob(a,b,c) values(1,'xifenfei', empty_blob()) return c into b_lob;
  6  b_file:=bfilename('EXPDP_DIR','0430.DMP');
  7  dbms_lob.open(b_file,dbms_lob.file_readonly);
  8  dbms_lob.loadfromfile(b_lob,b_file,dbms_lob.getlength(b_file));
  9  dbms_lob.close(b_file);
 10  commit;
 11  end;
 12  /

PL/SQL 过程已成功完成。

SQL> select * from u0425.t_blob where rownum<2;

         A B
---------- ----------
C
--------------------------------------------------------------------------------
         1 xifenfei
0301914E3B98006780BC00019FC70146A1A3633DBC4B089DCF3DFCD951D559000000010000100003
6907E6041E0D3037000000020200000077D10000000000FA1A880022535953222E225359535F4558


SQL> select a,b,dbms_lob.getlength(c) from u0425.t_blob where rownum<2;

         A B          DBMS_LOB.GETLENGTH(C)
---------- ---------- ---------------------
         1 xifenfei               142024704

插入的blob字段和实际文件大小,内容一致,插入是成功的
20220502164359
20220502164727


实现trigger集中记录所有库ddl操作

今天客户说了一个我感觉有意思的需求:在一个库上的一张表记录所有库的ddl操作,实现方式:在一个库上建立表和触发器,其他库上通过dblink+同义词+触发器实现ddl操作记录到远程的表中.他当时写了一个触发器,但是有错误,想让我协助解决.在我们的一起努力下,解决了该触发器在dblink同义词的库上出错的问题.我这里测试使用的是10g的库做为存储所有库的ddl记录的库,11g库做为一个通过dblink插入ddl操作记录的库.
在10g数据库库中操作
1.创建记录ddl操作表

SQL> conn chf/xifenfei
Connected.
SQL> create table t_ddl_audit(
  2  db_name varchar2(30),
  3  login_user varchar2(30),
  4  ddl_time date,
  5  ip_address varchar2(20),
  6  audsid varchar2(20),
  7  schema_user varchar2(30),
  8  schema_object varchar2(40),
  9  login_tool varchar2(40),
 10  os_user varchar2(40),
 11  ddl_sql varchar2(4000));

Table created.

2.创建触发器

SQL> create or replace trigger tri_ddl_audit
  2    before ddl on database
  3  declare
  4    n           number;
  5    str_stmt    varchar2(4000);
  6    sql_text    ora_name_list_t;
  7    l_trace     number;
  8    v_module    varchar2(50);
  9    v_action    varchar2(50);
 10    str_session v$session%rowtype;
 11  begin
 12    n := ora_sql_txt(sql_text);
 13    for i in 1 .. n loop
 14      str_stmt := substr(str_stmt || sql_text(i), 1, 3000);
 15    end loop;
 16    dbms_application_info.READ_MODULE(v_module, v_action);
 17    INSERT INTO chf.t_ddl_audit
 18      (db_name,
 19       login_user,
 20       ddl_time,
 21       ip_address,
 22       audsid,
 23       schema_user,
 24       schema_object,
 25       login_tool,
 26       os_user,
 27       ddl_sql)
 28    VALUES
 29      (sys_context('USERENV', 'db_name'),
 30       ora_login_user,
 31       SYSDATE,
 32       sys_context('USERENV', 'IP_ADDRESS'),
 33       userenv('SESSIONID'),
 34       ora_dict_obj_owner,
 35       ora_dict_obj_name,
 36       v_module,
 37       sys_context('userenv', 'os_user'),
 38       str_stmt);
 39  exception
 40    when no_data_found then
 41      null;
 42  end;
 43  /

Trigger created.

3.测试触发器

SQL> conn chf/xifenfei
Connected.
SQL> create table t_xff as select * from dba_tables where rownum=1;

Table created.

SQL> select db_name,login_user,ddl_sql from t_ddl_audit;

DB_NAME                        LOGIN_USER
------------------------------ ------------------------------
DDL_SQL
-----------------------------------------------------------------
XFF                            CHF
create table t_xff as select * from dba_tables where rownum=1

在11g数据库中操作
1.创建dblink和同义词

SQL> create database link "ora10g_dblink"
  2   connect to chf
  3    identified by "xifenfei"
  4     using 'ora10g';

Database link created.

SQL> create  synonym t_ddl_audit for t_ddl_audit@ora10g_dblink;

Synonym created.

2.第一次创建触发器

SQL> create or replace trigger tri_ddl_audit
  2    before ddl on database
  3  declare
  4    n           number;
  5    str_stmt    varchar2(4000);
  6    sql_text    ora_name_list_t;
  7    l_trace     number;
  8    v_module    varchar2(50);
  9    v_action    varchar2(50);
 10    str_session v$session%rowtype;
 11  begin
 12    n := ora_sql_txt(sql_text);
 13    for i in 1 .. n loop
 14      str_stmt := substr(str_stmt || sql_text(i), 1, 3000);
 15    end loop;
 16    dbms_application_info.READ_MODULE(v_module, v_action);
 17    INSERT INTO t_ddl_audit
 18      (db_name,
 19       login_user,
 20       ddl_time,
 21       ip_address,
 22       audsid,
 23       schema_user,
 24       schema_object,
 25       login_tool,
 26       os_user,
 27       ddl_sql)
 28    VALUES
 29      (sys_context('USERENV', 'db_name'),
 30       ora_login_user,
 31       SYSDATE,
 32       sys_context('USERENV', 'IP_ADDRESS'),
 33       userenv('SESSIONID'),
 34       ora_dict_obj_owner,
 35       ora_dict_obj_name,
 36       v_module,
 37       sys_context('userenv', 'os_user'),
 38       str_stmt);
 39  exception
 40    when no_data_found then
 41      null;
 42  end;
 43  /

Trigger created.

3.测试触发器

SQL> create table t_xff as select * from dba_objects where rownum<10;
create table t_xff as select * from dba_objects where rownum<10
                                    *
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-02070: database  does not support  in this context
ORA-06512: at line 15

出现ORA-02070错误,估计是类此sys_context(‘userenv’, ‘os_user’)导致。

4.第二次创建触发器

SQL> create or replace trigger tri_ddl_audit
  2    before ddl on database
  3  declare
  4    n           number;
  5    str_stmt    varchar2(4000);
  6    sql_text    ora_name_list_t;
  7    l_trace     number;
  8    v_module    varchar2(50);
  9    v_action    varchar2(50);
 10    v_db_name   varchar2(50);
 11    v_ip_addr   varchar2(50);
 12    v_os        varchar2(50);
 13    v_session_id varchar2(50);
 14    str_session v$session%rowtype;
 15  begin
 16    n := ora_sql_txt(sql_text);
 17    for i in 1 .. n loop
 18      str_stmt := substr(str_stmt || sql_text(i), 1, 3000);
 19    end loop;
 20    dbms_application_info.READ_MODULE(v_module, v_action);
 21    v_db_name :=sys_context('USERENV', 'db_name');
 22    v_ip_addr :=sys_context('USERENV', 'IP_ADDRESS');
 23    v_os:=sys_context('userenv', 'os_user');
 24    v_session_id:=userenv('SESSIONID');
 25    INSERT INTO t_ddl_audit
 26      (db_name,
 27       login_user,
 28       ddl_time,
 29       ip_address,
 30       audsid,
 31       schema_user,
 32       schema_object,
 33       login_tool,
 34       os_user,
 35       ddl_sql)
 36    VALUES
 37      (v_db_name,
 38       ora_login_user,
 39       SYSDATE,
 40       v_ip_addr,
 41      v_session_id,
 42       ora_dict_obj_owner,
 43       ora_dict_obj_name,
 44       v_module,
 45       v_os,
 46       str_stmt);
 47  exception
 48    when no_data_found then
 49      null;
 50  end;
 51  /

Trigger created.

5.继续测试触发器

SQL> drop table t3;
drop table t3
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-02069: global_names parameter must be set to TRUE for this operation
ORA-06512: at line 23

根据ORA-02069,查询资料发现是通过dblink插入数据使用了变量和常量的方式混合使用导致该错误,修改触发器全部使用变量方式

6.第三次创建触发器

SQL> create or replace trigger tri_ddl_audit
  2    before ddl on database
  3  declare
  4    n           number;
  5    str_stmt    varchar2(4000);
  6    sql_text    ora_name_list_t;
  7    l_trace     number;
  8    v_module    varchar2(50);
  9    v_action    varchar2(50);
 10    v_db_name   varchar2(50);
 11    v_ip_addr   varchar2(50);
 12    v_os        varchar2(50);
 13    v_session_id varchar2(50);
 14    v_loginuser    varchar2(50);
 15     v_obj_name varchar2(50);
 16    v_owner    varchar2(50);
 17    str_session v$session%rowtype;
 18  begin
 19    n := ora_sql_txt(sql_text);
 20    for i in 1 .. n loop
 21      str_stmt := substr(str_stmt || sql_text(i), 1, 3000);
 22    end loop;
 23    dbms_application_info.READ_MODULE(v_module, v_action);
 24    v_db_name :=sys_context('USERENV', 'db_name');
 25    v_ip_addr :=sys_context('USERENV', 'IP_ADDRESS');
 26    v_os:=sys_context('userenv', 'os_user');
 27    v_session_id:=userenv('SESSIONID');
 28    v_loginuser:= ora_login_user;
 29    v_owner:=ora_dict_obj_owner;
 30    v_obj_name:=ora_dict_obj_name;
 31    INSERT INTO t_ddl_audit
 32      (db_name,
 33       login_user,
 34       ddl_time,
 35       ip_address,
 36       audsid,
 37       schema_user,
 38       schema_object,
 39       login_tool,
 40       os_user,
 41       ddl_sql)
 42    VALUES
 43      (v_db_name,
 44       v_loginuser,
 45       SYSDATE,
 46       v_ip_addr,
 47      v_session_id,
 48       v_owner,
 49       v_obj_name,
 50       v_module,
 51       v_os,
 52       str_stmt);
 53  exception
 54    when no_data_found then
 55      null;
 56  end;   
 57  /

Trigger created.

7.测试触发器

SQL> create table t_xff11 as select * from dba_tables where rownum<10;

Table created.

SQL> select db_name,login_user,ddl_sql from t_ddl_audit;

DB_NAME                        LOGIN_USER
------------------------------ ------------------------------
DDL_SQL
-----------------------------------------------------------------
ora11g                         CHF
create table t_xff11 as select * from dba_tables where rownum<10

XFF                            CHF
create table t_xff as select * from dba_tables where rownum=1

补充说明
这个方案个人感觉是一个实验室中的方案,在实际的生成环境中很难应用上
1.trigger记录ddl操作本身效率不高
2.如果某个库不能访问存储ddl操作的表的数据库,将导致该数据库所有ddl操作hang住,从而可能使得该数据库hang住的风险.

ORACLE 十进制与二进制互转函数

十进制转换二进制

CREATE OR REPLACE FUNCTION NUMBER_TO_BIT(V_NUM NUMBER) 
RETURN VARCHAR IS V_RTN VARCHAR(8);--注意返回列长度
  V_N1  NUMBER;
  V_N2  NUMBER;
BEGIN
V_N1 := V_NUM;
    LOOP
      V_N2  := MOD(V_N1, 2);
      V_N1  := ABS(TRUNC(V_N1 / 2));
      V_RTN := TO_CHAR(V_N2) || V_RTN;
      EXIT WHEN V_N1 = 0;
    END LOOP;
--返回二进制长度
 SELECT lpad(V_RTN,8,0) 
    INTO   V_RTN
    FROM dual;
return V_RTN;
end;

SQL> select NUMBER_TO_BIT(208) from dual;

NUMBER_TO_BIT(208)
-----------------------------
11010000

二进制转换十进制

CREATE OR REPLACE FUNCTION BIT_TO_NUMBER(P_BIN IN VARCHAR2) RETURN NUMBER AS
  V_SQL    VARCHAR2(30000) := 'SELECT BIN_TO_NUM(';
  V_RETURN NUMBER;
BEGIN
  IF LENGTH(P_BIN) >= 256 THEN
    RAISE_APPLICATION_ERROR(-20001, 'INPUT BIN TOO LONG!');
  END IF;
  IF LTRIM(P_BIN, '01') IS NOT NULL THEN
    RAISE_APPLICATION_ERROR(-20002, 'INPUT STR IS NOT VALID BIN VALUE!');
  END IF;
  FOR I IN 1 .. LENGTH(P_BIN) LOOP
    V_SQL := V_SQL || SUBSTR(P_BIN, I, 1) || ',';
  END LOOP;
  V_SQL := RTRIM(V_SQL, ',') || ') FROM DUAL';
  EXECUTE IMMEDIATE V_SQL
    INTO V_RETURN;
  RETURN V_RETURN;
END;

SQL> SELECT BIT_TO_NUMBER('11010000') FROM DUAL;

BIT_TO_NUMBER('11010000')
-------------------------
                      208