月归档：十二月 2010

TKPROF使用

发表于 2010 年 12 月 6 日 由 惜分飞

1、设置sql跟踪

--系统级别
alter system set sql_trace=true;
alter system set sql_trace=false;
--会话级别
alter session set sql_trace=true;
alter session set sql_trace=false;
--其他会话
exec sys.dbms_system.set_sql_trace_in_session(16737 , 39196 , true);
exec sys.dbms_system.set_sql_trace_in_session(16737 , 39196 , false);

2、执行相关sql语句
需要跟踪的sql

3、查询trace 文件

select d.value||'/'||lower(rtrim(i.instance,chr(0)))||'_ora_'||p.spid||'.trc' trace_file_name from 
(select p.spid from v$mystat m, v$session s,v$process p where m.statistic# = 1 and s.sid = m.sid and p.addr = s.paddr ) p,
(select t.instance from v$thread t,v$parameter v where v.name = 'thread' and(v.value = 0 or t.thread# = to_number(v.value))) i,
(select value from v$parameter where name = 'user_dump_dest') d

4、使用TKPROF命令

tkprof trace_file_name out_file explain=username/password
TKPROF DLSUN12_JANE_FG_SVRMGR_007.TRC OUTPUTA.PRF
EXPLAIN=SCOTT/TIGER TABLE=SCOTT.TEMP_PLAN_TABLE_A
INSERT=STOREA.SQL SYS=NO SORT=(EXECPU,FCHCPU)

5、排序选项说明
prscnt number of times parse was called
prscpu cpu time parsing
prsela elapsed time parsing
prsdsk number of disk reads during parse
prsqry number of buffers for consistent read during parse
prscu number of buffers for current read during parse
prsmis number of misses in library cache during parse
execnt number of execute was called
execpu cpu time spent executing
exeela elapsed time executing
exedsk number of disk reads during execute
exeqry number of buffers for consistent read during execute
execu number of buffers for current read during execute
exerow number of rows processed during execute
exemis number of library cache misses during execute
fchcnt number of times fetch was called
fchcpu cpu time spent fetching
fchela elapsed time fetching
fchdsk number of disk reads during fetch
fchqry number of buffers for consistent read during fetch
fchcu number of buffers for current read during fetch
fchrow number of rows fetched
userid userid of user that parsed the cursor

发表在 Oracle性能优化 | 评论关闭

查找oracle所有表中的特定列中的数据

发表于 2010 年 12 月 6 日 由 惜分飞

找出数据库中所有表表中REMARK列中含有WN、wind、wlr中表名和数量

declare
i number:=0;
begin
 for c1 in
(select table_name from user_tab_columns where column_name='REMARK')
loop
 EXECUTE IMMEDIATE 'select count(*)  from  '|| c1.table_name
  ||' where UPPER(REMARK) LIKE ''%WN%'' or UPPER(remark) like ''%WIND%'' or UPPER(remark) like ''%WLR%'''
  into  i;
    if i>0 then 
      dbms_output.put_line(c1.table_name||'------'||i);
      end if;
    end loop;
end;
发表在 Oracle | 评论关闭

触发器实现指定用户登录oracle

发表于 2010 年 12 月 2 日 由 惜分飞

1、创建允许登录用户表

CREATE TABLE "CHF"."LOG$LOGIN_OS"
  (
    "OS_USER" VARCHAR2(60 BYTE)
  )

2、创建触发器实现限制用户登录

create or replace
TRIGGER TR_LOGIN_RECORD_TEST
AFTER logon ON DATABASE
DECLARE
mtSession v$session%ROWTYPE;
CURSOR cSession(iiQuerySid IN NUMBER) IS
   SELECT * FROM v$session where USERNAME is not null
    and nvl(osuser,'x') <> 'SYSTEM'   and type <> 'BACKGROUND' and audsid = iiQuerySid;
  USER_NUM NUMBER(5);
  V_SQL VARCHAR2(100);
BEGIN
OPEN cSession(userenv('SESSIONID'));
  FETCH cSession INTO mtSession;
  IF cSession%FOUND THEN
  select count(*) into USER_NUM FROM CHF.LOG$LOGIN_OS WHERE OS_USER=mtSession.Osuser;
  IF USER_NUM!=0 THEN
  V_SQL:=' alter system kill session '||''''||mtSession.Sid||','||mtSession.Serial#||'''';
  EXECUTE IMMEDIATE V_SQL;
  END IF;
  END IF;
  CLOSE cSession;
EXCEPTION
  WHEN OTHERS THEN
   -- dbms_output.put_line('登记登录信息错误：'||SQLERRM);
    RAISE;
END;

注：使用sysdba帐号创建触发器，因为在oracle中user不能kill掉自己的session，如果是用sysdba那么就可以kill掉其他的任何非自身的session

发表在 Oracle | 评论关闭