oracle 常用SQL(不断更新)

select file#,checkpoint_change# from v$datafile;
select file#,checkpoint_change# from v$datafile_header;

等待大于2小时：

select * from v$session where seconds_in_wait >=2*3600;  

rman控制备份速度

configure CHANNEL DEVICE TYPE DISK RATE 50M;

SET ESCAPE ON
show escape

带回车符号的字段为field_abc
替换回车符：
replace(field_abc,CHR(13),'')

替换换行符：
replace(field_abc,CHR(10),'')

替换回车换行符：
replace(replace(field_abc,CHR(13),''),CHR(10),'')

select remark,replace(remark,CHR(13),'') dd1,replace(replace(remark,CHR(13),''),CHR(10),'') dd2  from "CS".tb1 where c1='987104071118'

 

 -- 带条件的左连接  
select *  
  from table_a  
  left join table_b on table_a.field_aa = table_b.field_ba and table_b.field_bb = 'y'; -- 先过滤再连接(先把table_b表中符合条件的数据过滤出来,之后再跟table_a进行left join操作)  
select *  
  from table_a  
  left join table_b on table_a.field_aa = table_b.field_ba where table_b.field_bb = 'y'; -- 先连接再过滤(也就是说先按照left jion的on条件关联出一个虚拟表,之后再对这个虚拟表进行where后的过滤操作)  
select *  
  from table_a, table_b  
 where table_a.field_aa = table_b.field_ba(+) and table_b.field_bb = 'y'; -- 先连接再过滤