常用SQL

 

    oracle表的回闪操作 

1.在9i上执行的操作

查询test表中记录
select * from test;
删除test表中记录
delete from test;
获得过去的会话
exec dbms_flashback.disable;
查询出3分钟前这个test表的情况
select * from test as of timestamp(systimestamp - interval '3' minute );
将查出的记录插入到误删除数据的表中
insert into test select * from test as of timestamp(systimestamp - interval '30' minute );

2.在10G上执行的操作
例：执行将test表闪回到2005年5月7日下午3点。
SQL>flashback table test to timestamp to_timestamp('2005-05-07 15:00:00','yyyy-mm-dd hh24:mi:ss');

 

------------------------------------------------------------------------------------------------------------------------------------------------

修改数据库中某些字段存在的空格
update laagent set name=replace(name,' ','') where agentcode='116000001'

------------------------------------------------------------------------------------------------------------------------------------------------

将A表中丢失的idno补全(从B表(备份表)中查询出相应的idno修改到A表中)    

update laagent z set z.idno =( select b.idno from laagentb b where b.agentcode=z.agentcode
      and b.idno is not null and b.branchtype='1'
      and edorno =(select max(edorno) from laagentb c where b.agentcode=c.agentcode and c.idno is not null))
     where z.idno is null
      and z.branchtype = '1'

-------------------------------------------------------------------------------------------------------------------------------------------------