OraFAQ Forum: SQL & PL/SQL » Can we get back any column after DROP

Home » SQL & PL/SQL » SQL & PL/SQL » Can we get back any column after DROP (Oracle 11g, Window 7 )

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Can we get back any column after DROP [message #629299]

Sat, 06 December 2014 10:54

Bilal Khan
Messages: 128
Registered: April 2010
Location: Pakistan

Senior Member

Dear All, can we recover or get back any column.
let i have table Student having 4 column std_id, std_name, std_address, std_age. i drop the column std_age, can i get it back or not.

Thanks and Regards.

Report message to a moderator

Re: Can we get back any column after DROP [message #629300 is a reply to message #629299]

Sat, 06 December 2014 11:03

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

BTW - having STD_AGE column is flawed design to begin with; since AGE is a computed value based upon date of birth & today's date

It depends.
How long ago was column dropped?
Is FLASHBACK enabled?

Report message to a moderator

Re: Can we get back any column after DROP [message #629301 is a reply to message #629300]

Sat, 06 December 2014 11:08

Bilal Khan
Messages: 128
Registered: April 2010
Location: Pakistan

Senior Member

as column is not derived, and newly drop column, can we recover, if yes then how??

Report message to a moderator

Re: Can we get back any column after DROP [message #629302 is a reply to message #629301]

Sat, 06 December 2014 11:10

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

when all else fails Read The Fine Manual

https://docs.oracle.com/apps/search/search.jsp?word=flashback&product=e50529-01&book=sqlrf

Report message to a moderator

Re: Can we get back any column after DROP [message #629303 is a reply to message #629301]

Sat, 06 December 2014 11:35

Michel Cadot
Messages: 68641
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Restore/recover PITR.

Report message to a moderator

Re: Can we get back any column after DROP [message #629304 is a reply to message #629303]

Sat, 06 December 2014 11:38

Michel Cadot
Messages: 68641
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Performing Flashback and Database Point-in-Time Recovery

Report message to a moderator

Re: Can we get back any column after DROP [message #629305 is a reply to message #629304]

Sat, 06 December 2014 11:46

Bilal Khan
Messages: 128
Registered: April 2010
Location: Pakistan

Senior Member

Thanks Michel Cadot and BlackSawa, as i could not find any solution, is there any query like we use for recovery of table:
FLASHBACK TABLE STudent to before drop;
is there any specific query for recovery of column after drop.

Report message to a moderator

Re: Can we get back any column after DROP [message #629306 is a reply to message #629305]

Sat, 06 December 2014 11:48

Michel Cadot
Messages: 68641
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

No.

Report message to a moderator

Re: Can we get back any column after DROP [message #629307 is a reply to message #629306]

Sat, 06 December 2014 11:58

Bilal Khan
Messages: 128
Registered: April 2010
Location: Pakistan

Senior Member

Ok thanks Michel, i studied from your reference documents that when we drop any table it will store in Recyclebin, is there any recyclebin for column that we drop.

Report message to a moderator

Re: Can we get back any column after DROP [message #629308 is a reply to message #629307]

Sat, 06 December 2014 12:05

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

Bilal Khan wrote on Sat, 06 December 2014 09:58

Ok thanks Michel, i studied from your reference documents that when we drop any table it will store in Recyclebin, is there any recyclebin for column that we drop.

are you incapable or just unwilling to query the RECYCLEBIN yourself?

Report message to a moderator

Re: Can we get back any column after DROP [message #629309 is a reply to message #629307]

Sat, 06 December 2014 12:05

Michel Cadot
Messages: 68641
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

No.
How many times I will repeat it?
Do you think if there was I'd not already mention it?

[Updated on: Sat, 06 December 2014 12:06]

Report message to a moderator

Re: Can we get back any column after DROP [message #629310 is a reply to message #629309]

Sat, 06 December 2014 12:14

Bilal Khan
Messages: 128
Registered: April 2010
Location: Pakistan

Senior Member

Thanks Michel and BlakSwan, As i explore the RECYCLEBIN and could not find the drop column, now it become clear to me that there is no simple query to recover the drop column.

Thanks for your nice cooperation and suggestion.
Regards.

Report message to a moderator

Re: Can we get back any column after DROP [message #629402 is a reply to message #629310]

Mon, 08 December 2014 08:57

Bill B
Messages: 1971
Registered: December 2004

Senior Member

I would just store the birthdate. the calculation for age is easy.

Report message to a moderator

Re: Can we get back any column after DROP [message #629417 is a reply to message #629402]

Tue, 09 December 2014 00:07

Littlefoot
Messages: 21807
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

Bill B

the calculation for age is easy.

Not really. Vast majority of "solutions" out there is wrong.

Report message to a moderator

Re: Can we get back any column after DROP [message #629453 is a reply to message #629417]

Tue, 09 December 2014 06:58

EdStevens
Messages: 1376
Registered: September 2013

Senior Member

Littlefoot wrote on Tue, 09 December 2014 00:07

Bill B

the calculation for age is easy.

Not really. Vast majority of "solutions" out there is wrong.

What's wrong with simply "age = trunc(sysdate) - trunc(day_of_birth)" ??

It's a dead certainty that any value for stored age will be wrong. It will be wrong the day after it is inserted.

Report message to a moderator

Re: Can we get back any column after DROP [message #629455 is a reply to message #629453]

Tue, 09 December 2014 07:02

Bill B
Messages: 1971
Registered: December 2004

Senior Member

AGE = floor(MONTHS_BETWEEN(TRUNC(SYSDATE),BIRTH_DATE)/12)

[Updated on: Tue, 09 December 2014 07:38]

Report message to a moderator

Re: Can we get back any column after DROP [message #629457 is a reply to message #629455]

Tue, 09 December 2014 07:12

Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web

Senior Member

If there is really a business need to have the age column, a virtual column would suffice.

Edit : Sorry, seems like my answer has actually nothing to do with the topic title, just saw the previous replies and responded.

[Updated on: Tue, 09 December 2014 07:17]

Report message to a moderator

Re: Can we get back any column after DROP [message #629458 is a reply to message #629300]

Tue, 09 December 2014 07:21

gazzag
Messages: 1118
Registered: November 2010
Location: Bedwas, UK

Senior Member

I suspect that in this instance the STD prefix means "student" Shocked

Report message to a moderator

Re: Can we get back any column after DROP [message #629460 is a reply to message #629458]

Tue, 09 December 2014 07:51

Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web

Senior Member

But age would be just age, whether student or principal Wink

Just kidding Smile

[Updated on: Tue, 09 December 2014 07:55]

Report message to a moderator

Re: Can we get back any column after DROP [message #629461 is a reply to message #629460]

Tue, 09 December 2014 07:54

Littlefoot
Messages: 21807
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

Age, as a decimal number, is easily calculated. But people don't like to see "Scott is 28.1123412352 years old". They prefer "Scott is 28 years 01 month 14 days old" (figures aren't accurate). So, when you put TRUNCs, MODs, LPADs and stuff into the play, things get complex and that's what I was talking about.

P.S. Oh, yes: that was just a remark, really off topic. Forget it.

[Updated on: Tue, 09 December 2014 07:55]

Report message to a moderator

Re: Can we get back any column after DROP [message #629462 is a reply to message #629461]

Tue, 09 December 2014 07:57

Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web

Senior Member

And I guess, LF was talking about the display format of the age, as we mostly see the majority of questions on any forum. And not that anything is wrong with the calculation. I am just guessing, unless he has something else to say.

Edit : LF alread posted, didn't see.

[Updated on: Tue, 09 December 2014 08:02]

Report message to a moderator

Re: Can we get back any column after DROP [message #629463 is a reply to message #629461]

Tue, 09 December 2014 08:31

joy_division
Messages: 4963
Registered: February 2005
Location: East Coast USA

Senior Member

Littlefoot wrote on Tue, 09 December 2014 08:54

But people don't like to see "Scott is 28.1123412352 years old". They prefer "Scott is 28 years 01 month 14 days old" (figures aren't accurate)

Pretty close though! 1 month and 11 days (vs. 14) into the year gives a remainder of .112328767.

Report message to a moderator

Previous Topic:	dumping data from *.rtf file to oracle table
Next Topic:	ora-06550

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Apr 19 22:12:49 CDT 2024