We help IT Professionals succeed at work.

why truncate is faster than delete in oracle ?

MIHIR KAR
MIHIR KAR asked
on
327 Views
Last Modified: 2017-03-22
How can we recover truncate data .
Comment
Watch Question

Database Developer & Administrator
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2012
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
johnsoneSenior Oracle DBA
CERTIFIED EXPERT

Commented:
Truncate is faster because the rows are not marked as deleted.  The high water mark for the table is moved back to the start of the table so it appears to have no rows in it.

The typical way to recover from a truncate is to restore a backup.  It can be restored to a different location, then you extract the table that was truncated using expdp/impdp.  That isn't the only way, but usually the most reliable.
Geert GOracle dba
CERTIFIED EXPERT
Top Expert 2009

Commented:
in cases like this it helps to have a standby running with some lag

the big question is : how much lag
3 hours is a minimum, more lag is better

if cases like this happen, open the standby, export what you need from it
and then change it back to a standby database

that's assuming you get warned in a timely fashion of the problem ... that's before the lag window
MIHIR KAR#Hadoop #Oracle_DB  #UNIX beginner

Author

Commented:
Thnks sir i appropriate with your answer.
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.