oracle笔记

分组排序example：

 merge into t_directory d

  using (select rank() over(partition by parentdircode order by id ) rk,s.* from t_directory s) t
  on (t.dircode = d.dircode)
  when matched then

    update set d.rank = t.rk;

 

数据库查询某个session会话开启的游标数
    
    select o.sid, osuser, machine, count(*) num_curs

    from v$open_cursor o, v$session s  

   where user_name = 'ADMIN2' and o.sid=s.sid
   
    and machine = 'wsnjc-1970'

    group by o.sid, osuser, machine

  order by  num_curs desc;   

oracle数据库记录错误堆栈信息：

Create Or Replace Procedure Proc_testErrLine
 Is
    i    number(8);
 Begin
    i:=1/0;
    
exception
    when others then
        dbms_output.put_line(dbms_utility.format_error_stack());
        dbms_output.put_line(dbms_utility.format_call_stack());
        dbms_output.put_line(dbms_utility.format_error_backtrace());
 End proc_testErrLine;
/
Show Err;

【输出结果】

ORA-01476: divisor is equal to zero

----- PL/SQL Call Stack -----
  object      line  object
  handle    number  name
6978D4BC        10  procedure CQMKT.PROC_TESTERRLINE
6846F2C0         7  anonymous block

ORA-06512: at "CQMKT.PROC_TESTERRLINE", line 5

sql跟踪、计时：

执行
@$ORACLE_HOME/rdbms/admin/utlxplan.sql
@$ORACLE_HOME/sqlplus/admin/plustrce.sql

grant all on plan_table to public;
grant plustrace to public;

set autotrace on;
set timing on;