Oracle Truncate Table Question

[Jack C. Mahaffey]

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?

[Steven E. Protter]

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

[Jack C. Mahaffey]

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 · Accepted solution]

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]

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]

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

[Alexander M. Ermes]

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

[Yogeeraj_1]

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

[Sanjay Kumar Suri]

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

[Jack C. Mahaffey]

Thanks all...
Jack...