|
|
| Re: how to recover a truncated table [message #30357 is a reply to message #30353] |
Mon, 26 April 2004 22:16   |
Sashi
Messages: 22
Registered: January 2004
|
Junior Member |
|
|
If you are using 9I version then U can make use of FLASH BACK Queries based on either TIME factor or SCN factor.
Below are the examples using each:
We can make use of them either at SQl prompt or in
Pl/SQl Blocks:
--Pl/sql getting the rows trucnated.
declare
cursor old_rows is select * from dept;
vrec old_rows%rowtype;
begin
DBMS_FLASHBACK.DISABLE;
DBMS_FLASHBACK.ENABLE_AT_SYSTEM_CHANGE_NUMBER(sysdate-15/1440);
OPEN old_rows;
DBMS_FLASHBACK.DISABLE;
LOOP
fetch old_rows into vrec;
dbms_output.put_line('vrec.dno==>'||vrec.deptno);
exit when old_rows%notfound;
insert into dept(deptno,dname) values (vrec.deptno, vrec.dname);
END LOOP;
CLOSE old_rows;
commit;
end;
NOTE: DBMS_FLASHBACK.ENABLE_AT_SYSTEM_CHANGE_NUMBER(sysdate-<time at which u wnat the data from the table>/1440);
2) At SQl prompt
1..Transaction start
2..varibale SCN_NUM
3..EXECUTE :SCN_NUM := DBMA_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER:
4..DML Operations
5..COMMIT;
6..EXECUTE DBMS_FLASHBACK.ENABLE_AT_SYSTEM_CHANGE_NUMBER(:scn_num);
7..SELECT the data from
8..EXECUTE DBMS_FLASHBACK.DISABLE
variable scn_num number
execute :scn_num := dbms_flashback.get_system_change_number;
print scn_num
delete from dept;
commit;
execute dbms_flashback.enable_at_system_change_number(:scn_num);
SQL> select * from dept;
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
EXECUTE DBMS_FLASHBACK.DISABLE;
Rgds
Sashi
|
|
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
