Home » RDBMS Server » Backup & Recovery » how to recover a truncated table
how to recover a truncated table [message #30353] Mon, 26 April 2004 20:08 Go to next message
lalitha
Messages: 39
Registered: June 2002
Member
Hi

I've truncated a database table.

Can anybody tell me how to recollect my data.
Re: how to recover a truncated table [message #30357 is a reply to message #30353] Mon, 26 April 2004 22:16 Go to previous messageGo to next message
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
Re: how to recover a truncated table [message #30359 is a reply to message #30357] Mon, 26 April 2004 22:26 Go to previous messageGo to next message
lalitha
Messages: 39
Registered: June 2002
Member
Hi Sashi,
Thanks for ur reply. But I'm using Oracle 8.
Is there any way to recollect the data after truncating it?
Thanks in advance
lalitha
Re: how to recover a truncated table [message #30361 is a reply to message #30359] Mon, 26 April 2004 22:50 Go to previous messageGo to next message
Sashi
Messages: 22
Registered: January 2004
Junior Member
The only option left, as far as my knowledge goes, is
by the use of archived data files.

Will Post U If I get any updates on this.
Re: how to recover a truncated table [message #31297 is a reply to message #30359] Tue, 22 June 2004 21:21 Go to previous message
Venu
Messages: 44
Registered: September 1999
Member
I don't think it is possible in any oracle version. Only dml statement can be rollbacked. If ddl is used it cannot be rollbacked.
Previous Topic: Hot Backup issue
Next Topic: Rman do not backup online redo.log?
Goto Forum:
  


Current Time: Mon May 27 01:13:24 CDT 2024