No question at this time
DBA Top 10
1 M. Cadot 17900
2 A. Kavsek 15900
3 M. Hidayathullah ... 9200
4 B. Vroman 8600
5 T. Boles 4900
6 P. Wisse 4700
7 J. Schnackenberg 3800
8 K. Van Nieuwenhov... 2100
9 G. Van Cauter 1400
10 A. Hudspith 800
10 T. P 800
About
DBA-Village
Download PLATO
The free tool for auditing and tuning your database
Version 55 now available
Sep 02, 2016
The DBA-Village forum
Forum as RSS
as RSS feed
Site Statistics
Ever registered users48079
Total active users1704
Act. users last 24h9
Act. users last hour0
Registered user hits last week295
Registered user hits last month1831
Go up

flash back tables
Next thread: Move partitions of table to new tablespace
Prev thread: query

Message Score Author Date
Hi Guys I want to flash back tables from recycl...... Tso P Nov 08, 2017, 10:44
Hello Tso, you can guess that if the table has ...... Score: 300 PtsScore: 300 PtsScore: 300 PtsScore: 300 PtsScore: 300 Pts Bruno Vroman Nov 08, 2017, 11:11
Thanks Bruno I would like to try the timestamp ...... Tso P Nov 08, 2017, 11:48
I am trying to use this script that I modified: ...... Tso P Nov 08, 2017, 11:51
When I try flashback to scn or timestamp I get the...... Tso P Nov 08, 2017, 12:59
I think I am confusing myself here: We have tab...... Tso P Nov 08, 2017, 13:28
Hi, where is the problem? Find out which tab...... Score: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 Pts Ales Kavsek Nov 08, 2017, 15:46
Thanks Ales I just confused myself. Much app...... Tso P Nov 09, 2017, 08:19
Dear TSO, You are trying to restore deleted and...... Prasathi Ji Nov 13, 2017, 06:35

Follow up by mail Click here


Subject: flash back tables
Author: Tso P, South Africa
Date: Nov 08, 2017, 10:44, 14 days ago
Os info: solaris 10
Oracle info: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
Message: Hi Guys

I want to flash back tables from recycle bin. My developer dropped tables by mistakes, now he recreated the tables with the script but he would like me to flashback the dropped tables as they contain data.

How do I flashback those tables...I can see the tables in the show recyclebin;

Can I flashback the table if it is recreated?

Thanks in advance!!!

Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: flash back tables
Author: Bruno Vroman, Belgium
Date: Nov 08, 2017, 11:11, 14 days ago
Score:   Score: 300 PtsScore: 300 PtsScore: 300 PtsScore: 300 PtsScore: 300 Pts
Message: Hello Tso,

you can guess that if the table has been recreated this will cause an issue... But it is easy to workaround.
What about a small test?
CREATE TABLE mytable AS SELECT sysdate - 1 dte, 'old' txt FROM dual;

DROP TABLE mytable;
CREATE TABLE mytable AS SELECT sysdate dte, 'new' txt FROM dual;
FLASHBACK TABLE mytable TO BEFORE DROP;
*
ERROR at line 1:
ORA-38312: original name is used by an existing object
But we can do things like:
RENAME mytable TO newtable;

Table renamed.

FLASHBACK TABLE mytable TO BEFORE DROP;
Flashback complete.

SELECT * FROM mytable
UNION ALL SELECT * FROM newtable
;
DTE TXT
--------- ---
07-NOV-17 old
08-NOV-17 new
And we can put the 2 tables together, with for example:
INSERT INTO newtable SELECT * FROM mytable;

DROP TABLE mytable;
RENAME newtable TO mytable;
(you might prefer to keep the old table, but beware of privileges, constraints, indexes... that presumably have been recreated by the developer)

Best regards,

Bruno Vroman.
P.S. Hopefully no triggers on the table, but beware!
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: flash back tables
Author: Tso P, South Africa
Date: Nov 08, 2017, 11:48, 14 days ago
Message: Thanks Bruno

I would like to try the timestamp with a flashback...preferably the between certain timestamps.

Thanks


Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: flash back tables
Author: Tso P, South Africa
Date: Nov 08, 2017, 11:51, 14 days ago
Message: I am trying to use this script that I modified:

SET SERVEROUTPUT ON SIZE 2000000
BEGIN
FOR cur_rec IN (SELECT original_name, type
FROM user_recyclebin
WHERE type IN ('TABLE'))
LOOP
BEGIN
IF cur_rec.type = 'TABLE' THEN
EXECUTE IMMEDIATE 'ALTER ' || cur_rec.type || ' "' || cur_rec.original_name || '" ENABLE ROW MOVEMENT';
EXECUTE IMMEDIATE 'FLASHBACK ' || cur_rec.type || ' "' || cur_rec.original_name || '" to TIMESTAMP TO_TIMESTAMP('2017-11-08 10:19:03', 'YYYY-MM-DD HH24:MI:SS')';
END IF;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.put_line('FAILED: FLASHBACK ' || cur_rec.type || ' "' || cur_rec.original_name || '" to timestamp TIMESTAMP TO_TIMESTAMP('SET SERVEROUTPUT ON SIZE 2000000
BEGIN
FOR cur_rec IN (SELECT original_name, type
FROM user_recyclebin
WHERE type IN ('TABLE'))
LOOP
BEGIN
IF cur_rec.type = 'TABLE' THEN
EXECUTE IMMEDIATE 'ALTER ' || cur_rec.type || ' "' || cur_rec.original_name || '" ENABLE ROW MOVEMENT';
EXECUTE IMMEDIATE 'FLASHBACK ' || cur_rec.type || ' "' || cur_rec.original_name || '" to TIMESTAMP TO_TIMESTAMP('2017-11-08 10:19:03', 'YYYY-MM-DD HH24:MI:SS')';
END IF;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.put_line('FAILED: FLASHBACK ' || cur_rec.type || ' "' || cur_rec.original_name || '" to timestamp TIMESTAMP TO_TIMESTAMP('2017-11-08 10:19:03', 'YYYY-MM-DD HH24:MI:SS'));
END;
END LOOP;
END;
/ ', 'YYYY-MM-DD HH24:MI:SS'));
END;
END LOOP;
END;
/

But I get errors.

I need to flashback tables that were dropped between 2017-11-08 10:19:03 and 2017-11-08 11:30:03.

Please help!!!

Thanks in advance..


/
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: flash back tables
Author: Tso P, South Africa
Date: Nov 08, 2017, 12:59, 14 days ago
Message: When I try flashback to scn or timestamp I get there error below:

FLASHBACK TABLE TABLE_NAME TO SCN 19424282032;

ERROR at line 1:
ORA-00942: table or view does not exist

Yet on show recyclebin I can see the table.

What I am doing wrong here...

Thanks
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: flash back tables
Author: Tso P, South Africa
Date: Nov 08, 2017, 13:28, 14 days ago
Message: I think I am confusing myself here:

We have tables dropped then we recreated the tables with the script.

Now, we tried to flashback with timestamp only to find out that the structures are not the same.

Now, how do I restore the tables that were dropped before the we recreated them with scripts?

Thanks
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: flash back tables
Author: Ales Kavsek, Slovenia
Date: Nov 08, 2017, 15:46, 14 days ago
Score:   Score: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 Pts
Message: Hi,

where is the problem?

Find out which table exactly you want to "un-drop":


select object_name, original_name, droptime from user_recyclebin;

OBJECT_NAME ORIGINAL_NAME DROPTIME
------------------------------ -------------------- -------------------
BIN$XXqHn7anKVvgU6ogBApheg==$0 TEST_DROP 2017-11-08:15:38:40


if you have several "copies" of the table, simply specify system generated name for a table for particular timestamp...(and you don't really need to rename your current table), let's say that I dropped table test_drop:


sql> flashback table "BIN$XXqHn7anKVvgU6ogBApheg==$0" to before drop rename to test_drop_old;


Regards,
Ales
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: flash back tables
Author: Tso P, South Africa
Date: Nov 09, 2017, 08:19, 13 days ago
Message: Thanks Ales

I just confused myself.

Much appreciated !!!
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: flash back tables
Author: Prasathi Ji, India
Date: Nov 13, 2017, 06:35, 10 days ago
Message: Dear TSO,

You are trying to restore deleted and commited records using timestamp or SCN which is different scenario. you can't use flashback table tablename to before command using timestamp or SCN.

Best Regards
Prasathiji
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here