ITPub博客

今天终于把session的sid, db server操作系统进程，application server操作系统进程，以及相关用户信息全部联系起来。[@more@]select /*+ ORDERED */chr(39)||s.sid||','||s.serial#||chr(39) sid_serial, to_char(s.logon_time,'yyyymmdd HH24:MI:SS') logon, floor(last_call_et/3600)||':'||floor(mod(last_call_et,3600)/60)||':'||mod(mod(last_call_et,3600),60) IDLE, s.username o_user, s.osuser os_user, s.status status, u.user_name apps_user--, n.TERMINAL_ID terminal, s.process midTier_process, p.spid dbTier_process,

DBA经常需要用如下SQL来查看某个数据块数据哪个SEGMENT，但是在10g里这句SQL返回结果通常是空的。select * from dba_extents where file_id=30 and block_id<=1000 and block_id+blocks>=1000;[@more@]SOLUTION：SQL> alter session set "_optimizer_push_pred_cost_based"=false;Session alteredSQL> select * from dba_extents where file_id=30 and block_id<=1000 and block_id+blocks>=1000;OWNER SEGMENT_NAME

包括隐含参数[@more@]SQL> col KSPPINM for a40SQL> col KSPPSTVL for a40SQL> set lines 100SQL> set pages 200SQL> list 1 Select a.ksppinm, b.ksppstvl 2 from x$ksppi a, x$ksppsv b 3 where a.indx = b.indx 4 order by 1比如 _kghdsidx_countSQL> Select a.ksppinm, b.ksppstvlfrom x$ksppi a, x$ksppsv bwhere a.indx = b.indxand a.ksppinm = '_kghdsidx_count'; 2 3 4KSPPINM-------------------------------------------------------------------------KSPPSTVL-----------------------

Oracle instance异常终止的情况下，如crash, shutdown abort等， 有时候memory,semaphores等资源并不能及时释放，这会影响下一次数据库的启动。[@more@]参见http://zhyuh.itpub.net/resource/334/26709

alter session set timed_statistics=true /alter session set max_dump_file_size=unlimited /alter session set tracefile_identifier='Identifier'/alter session set events '10046 trace name context forever, level 12' /SQL/alter session set events '10046 trace name context off'/[@more@]see http://zhyuh.itpub.net/resource/334/26661