闪回的使用
1.查看闪回设定时间
show parameter undo_retention;
2.设定可闪回时间
alter system set undo_retention=18000 -----单位是秒
3.查询SCN变化
select name,first_change# fscn,next_change# nscn,next_time from v$archived_log;
4.查看当前SCN号
select dbms_flashback.get_system_change_number fsc from dual;
5.创建还原表
create table emp_rev as select * from emp where 1 = 0;
6.根据SCN号确定数据
select count(*) from emp as of scn &scn;
7.根据SCN号还原数据
insert into emp_rev select * from emp as of scn 1136137;
8.根据还原表恢复原表
drop table 原表
rename 还原表 to 原表
Errorstack跟踪错误
Errorstack设置主要有4个级别
0 仅转储错误堆栈
1 转存错误堆栈和函数调用堆栈
2 Level1 + ProcessState
3 Level2 + Context area(显示所有cursors,着重显示当前cursor)
可在实例级或会话级别设置,也可在参数文件中设置
如:alter session setevents '942 trace name errorstack level1';
2.打开errorstack设置
ORA-01438
alter system setevents '1438 trace name errorstack level 3'; ----打开
alter system setevents '1438 trace name errorstack level off'; ---关闭
3.查看alter日志