sqlplus katerina/katerina
create table dept
(
deptid number primary key,
deptname varchar2(2)
);
insert into dept values ( 1,'a');
insert into dept values ( 2,'b');
insert into dept values ( 3,'c');
insert into dept values ( 4,'d');
insert into dept values ( 5,'e');
commit;
create table emp
(
empid number primary key,
salary number,
deptid number references dept(deptid)
);
insert into emp values ( 10,1000,1);
insert into emp values ( 20,1200,1);
insert into emp values ( 30,1300,2);
insert into emp values ( 40,1400,2);
commit;
create table maxSalary
(
emp_max_salary number
);
create or replace function mazSalary return number
is
n number;
begin
select max(salary) into n from emp;
return n;
end;
/
create or replace trigger emp_sal_audit
after update of salary on emp
for each row
begin
update maxsalary set emp_max_salary = mazSalary;
end;
/
SQL> select * from dept;
DEPTID DE
---------- --
1 a
2 b
3 c
4 d
5 e
SQL> select * from emp;
EMPID SALARY DEPTID
---------- ---------- ----------
10 1000 1
20 1200 1
30 1300 2
40 1400 2
SQL> select mazSalary from dual;
MAZSALARY
----------
1400
But update will be occured by the Rollis programmer
update emp set deptid=1;
--where empid=40
update emp set salary=20000 where deptid=1
commit;
DBA actions
============
select
versions_startscn,
versions_endscn,
versions_operation,
empid,
deptid,
salary
from katerina.emp
versions between scn minvalue and maxvalue;
VERSIONS_STARTSCN VERSIONS_ENDSCN V EMPID DEPTID SALARY
----------------- --------------- - ---------- ---------- ----------
9375531 U 40 1 20000
9375531 U 30 1 20000
9375531 U 20 1 20000
9375531 U 10 1 20000
9375512 9375531 U 40 1 1400
9375512 9375531 U 30 1 1300
9375512 9375531 U 20 1 1200
9375512 9375531 U 10 1 1000
9375512 10 1 1000
9375512 20 1 1200
9375512 30 2 1300
VERSIONS_STARTSCN VERSIONS_ENDSCN V EMPID DEPTID SALARY
----------------- --------------- - ---------- ---------- ----------
9375512 40 2 1400
select
versions_startscn,
versions_endscn,
versions_operation,
deptid,
deptname
from dept
versions between scn minvalue and maxvalue;
VERSIONS_STARTSCN VERSIONS_ENDSCN V DEPTID DE
----------------- --------------- - ---------- --
1 a
2 b
3 c
4 d
5 e
SQL> conn /as sysdba;
Connected.
SQL> select current_scn from v$database;
CURRENT_SCN
-----------
9375865
SQL> alter table katerina.emp enable row movement;
Table altered.
SQL> flashback table katerina.emp to scn 9375512-1;
Flashback complete.
SQL> select * from katerina.emp;
EMPID SALARY DEPTID
---------- ---------- ----------
10 1000 1
20 1200 1
30 1300 2
40 1400 2
VERSIONS_STARTSCN VERSIONS_ENDSCN V EMPID DEPTID SALARY
----------------- --------------- - ---------- ---------- ----------
9375961 I 10 1 1000
9375961 I 20 1 1200
9375961 I 30 2 1300
9375961 I 40 2 1400
9375961 D 40 1 20000
9375961 D 30 1 20000
9375961 D 20 1 20000
9375961 D 10 1 20000
9375531 9375961 U 40 1 20000
9375531 9375961 U 30 1 20000
9375531 9375961 U 20 1 20000
VERSIONS_STARTSCN VERSIONS_ENDSCN V EMPID DEPTID SALARY
----------------- --------------- - ---------- ---------- ----------
9375531 9375961 U 10 1 20000
9375512 9375531 U 40 1 1400
9375512 9375531 U 30 1 1300
9375512 9375531 U 20 1 1200
9375512 9375531 U 10 1 1000
9375512 10 1 1000
9375512 20 1 1200
9375512 30 2 1300
9375512 40 2 1400
delete * from emp;
delete * from dept;
SQL> flashback table katerina.emp to scn 9376535-1;
flashback table katerina.emp to scn 9376535-1
*
ERROR at line 1:
ORA-02091: transaction rolled back
ORA-02291: integrity constraint (KATERINA.SYS_C005648) violated - parent key
not found
SQL> alter table katerina.dept enable row movement;
Table altered.
SQL> flashback table katerina.emp,katerina.dept to scn 9376535-1;
Flashback complete.
SQL> select * from emp;
select * from emp
*
ERROR at line 1:
ORA-00942: table or view does not exist
SQL> select * from katerina.emp;
EMPID SALARY DEPTID
---------- ---------- ----------
10 1000 1
20 1200 1
30 1300 2
