Home » SQL & PL/SQL » SQL & PL/SQL » Undo a dropped Column from a table (Oracle 10g)
Undo a dropped Column from a table [message #590004] Sat, 13 July 2013 08:14 Go to next message
sss111ind
Messages: 480
Registered: April 2012
Location: India
Senior Member

Hi All,

Flashback query is working fine for dropped table,but throwing error when trying to get back the previous date by dropping a column from a table.

FLASHBACK TABLE emp TO TIMESTAMP TO_TIMESTAMP('2013-07-13 05:00:00', 'YYYY-MM-DD HH24:MI:SS')

SQL Error: ORA-01466: unable to read data - table definition has changed


Is that a limitation of Oracle by not having query to flashback a column but a table.

Regards,
Nathan

[Updated on: Sat, 13 July 2013 08:34]

Report message to a moderator

Re: Undo a dropped Column from a table [message #590007 is a reply to message #590004] Sat, 13 July 2013 08:46 Go to previous messageGo to next message
John Watson
Messages: 4577
Registered: January 2010
Location: Global Village
Senior Member
It's a technology limitation. Your FLASHBACK TABLE... command uses undo data to construct a set of DML statements that will reverse the effect of any preceding DML statements. But your DROP COLUMN command is a DDL statement: the data dictionary definitions needed to reconstruct the SQL no longer exist.
Re: Undo a dropped Column from a table [message #590008 is a reply to message #590007] Sat, 13 July 2013 09:12 Go to previous messageGo to next message
sss111ind
Messages: 480
Registered: April 2012
Location: India
Senior Member

Thanks John,

Flashback query is perfectly working for DROP TABLE even it is a DDL statement.Because flashback query does not recreates the table which is dropped but gets it from recycle bin and then performs a set of DML statements.So why it unable to do in case of DROP COLUMN.

Regards,
Nathan
Re: Undo a dropped Column from a table [message #590009 is a reply to message #590008] Sat, 13 July 2013 09:50 Go to previous messageGo to next message
John Watson
Messages: 4577
Registered: January 2010
Location: Global Village
Senior Member
Flashback drop is a different technology. If you have recyclebin=on then when you "drop" a table, it doesn't get dropped at all: it is simply renamed, so that you can't see it anymore. When you FLASHBACK TO BEFORE DROP all that happens is that it gets renamed back to what is was originally.

If you want equivalent functionality for a column, the closest is ALTER TABLE...SET UNUSED COLUMN...; but Oracle has not provided syntax to reverse this, though you can see it as a hidden column in dba_tab_cols.

Re: Undo a dropped Column from a table [message #590035 is a reply to message #590009] Sat, 13 July 2013 13:01 Go to previous message
Michel Cadot
Messages: 59166
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Another solution is to upgrade to 11.2 and use ARCHIVE feature.

Regards
Michel
Previous Topic: how escape character in query
Next Topic: Query on invalid objects
Goto Forum:
  


Current Time: Tue Sep 23 00:21:49 CDT 2014

Total time taken to generate the page: 0.08418 seconds