Simpler Navigation for Servers and Operating Systems
Completed: a much simpler Servers and Operating Systems section of the Community. We combined many of the older boards, so you won't have to click through so many levels to get at the information you need. Check the consolidated boards here as many sub-forums are now single boards.
cancel
Showing results for 
Search instead for 
Did you mean: 

Oracle Truncate Table Question

SOLVED
Go to solution
Jack C. Mahaffey
Super Advisor

Oracle Truncate Table Question

Got an oracle database where I want to run truncate table on. [ 7.3.4 on HP-UX ]

I understand that the data is unrecoverable unless I do a point in time recovery before the truncate or go to a previous database export.

My question is:

Will changes to the table after the truncate is executed continue to be be logged so that the new entries will be protected and recoverable? Let's say say I ran truncate a table today and next week I need to restore the table to sometime after the truncate operation. Will the online backups work for the new contents?

9 REPLIES
Steven E. Protter
Exalted Contributor

Re: Oracle Truncate Table Question

Old Oracle.

truncate is permanent. You can not back off on it even if you have archive logs running.

In your scenario I believe once the table data is restored it will be included in hot backups or cold backups.

SEP
Steven E Protter
Owner of ISN Corporation
http://isnamerica.com
http://hpuxconsulting.com
Sponsor: http://hpux.ws
Twitter: http://twitter.com/hpuxlinux
Founder http://newdatacloud.com
Jack C. Mahaffey
Super Advisor

Re: Oracle Truncate Table Question

Don't think I'm clear in the question.

Scenario:

Today I truncate table my_table

I now insert 3000 rows. Table is static, won't change anymore.
Tonight I do a normal online backup.
Archiving remains on. Cold backup is not taken.

Server crashes two days from now.

When database is restored two days from now, will I still have the 3000 rows. I don't care about the data that got truncated.

Volker Borowski
Honored Contributor
Solution

Re: Oracle Truncate Table Question

Hi,

"truncate" table is a special way to delete all data belonging to a table.
It is considered a DDL-statement, which means it autocommits !
This is why you can not use it in a transaction and "rollback" out of it.
It works directly on storage, by de-allocatine the attached extents from a table (and related indexes) and returns them to the freespace.
This is, why it is so speedy against DELETE, which works on rows. So there is no ROLLBACK possible for this statement within a transaction.

But

t1) truncate table ....
t2) insert into table
commit;

A point in time recovery including the SCN which commited t2) will lead to the table containing the rows inserted from t2) and nothing else.

Truncate can be compared to a drop/create, with many additional nice things, because in oposition to drop, all dependend dictionary objects (views, constraints, index, ...) will remain active !
After a drop and create, you have to recreate/recompile all of them, after a truncate not.

Not to be mixed up with statements that have a NOLOGGING-clause attached to it. Whenever these are used, special actions might be neccessary after a recovery !

Hope this helps
Volker
Jack C. Mahaffey
Super Advisor

Re: Oracle Truncate Table Question

If it's similar to a drop and create then I should be ok. I just want to be sure that the new data is protected by archiving.

Thanks...
Sanjay Kumar Suri
Honored Contributor

Re: Oracle Truncate Table Question

To add on:

You are correct in your understading that truncate will not generate undo information.

Truncate does not change the behaviour of future DML operations (like Insert/Update/Delete) done on that table.

sks
A rigid mind is very sure, but often wrong. A flexible mind is generally unsure, but often right.
Alexander M. Ermes
Honored Contributor

Re: Oracle Truncate Table Question

Hi there.
Truncate a table is shooting the horse immediately. Data are completely lost.
You cannot rollback anything from this table.
But what is also important is the deallocating of the extents and resetting the highwater mark in the table you truncated. That is different to the delete command ( with a commit ). The delete keeps thge highwater mark, where it is.
Rgds
Alexander M. Ermes
.. and all these memories are going to vanish like tears in the rain! final words from Rutger Hauer in "Blade Runner"
Yogeeraj_1
Honored Contributor

Re: Oracle Truncate Table Question

hi,

please note that:

the operation (truncate) is recoverable. it is considered DDL so it is done as an atomic statement all by itself -- but it is "recoverable".

you do not need a backup after a truncate.

if you are deleting all of the data -- you obviously do not care about it.


a single:

truncate table t;

is quite simply an efficient:

delete from t;
commit;


hope this helps too!

regards
Yogeeraj
No person was ever honoured for what he received. Honour has been the reward for what he gave (clavin coolidge)
Sanjay Kumar Suri
Honored Contributor

Re: Oracle Truncate Table Question

Yes recoverable to the extent that it will be incomplete/point-in-time recovery.

Being a DDL, it has a implict commit and therefore rollback can't be issued.

sks
A rigid mind is very sure, but often wrong. A flexible mind is generally unsure, but often right.
Jack C. Mahaffey
Super Advisor

Re: Oracle Truncate Table Question

Thanks all...
Jack...