日记 [2009年09月29日]

select sid,serial#,spid,machine,program,sql_fulltext,cpu_time,user_io_wait_time
from
(select *
from
( select sid,serial#,machine,program,sql_fulltext,cpu_time,user_io_wait_time,paddr
 from
 (select * from
 (select hash_value,sql_fulltext,cpu_time,user_io_wait_time from v$sql
 where hash_value in (select prev_hash_value from v$session
 where username like 'REGULATIONS' and program like 'JDBC Thin Client') order by cpu_time desc) t1
 left join
 (select prev_hash_value hash_value,sid,serial#,machine,program,paddr from v$session
 where username like 'REGULATIONS' and program like 'JDBC Thin Client') t2 on t1.hash_value = t2.hash_value)) t
 left join
 (select spid,addr from v$process) t2 on t.paddr = t2.addr);

loadDic.sql
insert /*append*/ into regulations.dic_unit
select dm,zm,dwlx,   --decode(dwlx,1,ssfj,3,sslj,4,'00000'),
case
when dm<>ssz then ssz
else case
     when dwlx=1 then case
                       when sscwd is not null then sscwd 
                       else case
                            when ssfj is not null then ssfj
                            else sslj
                            end
                       end
     when dwlx=2 then case
                      when ssfj is not null then ssfj
                      else sslj
                      end
     when dwlx=3 then sslj
     when dwlx=4 then '00000'
     end
end,
0,0,'',0,0,0,dwlx,sssf
from ZD_STATION
where dm not in (select unitcode from regulations.dic_unit);