topic Re: Oracle Truncate Table Question in Operating System - HP-UX

Oracle Truncate Table Question

Jack C. Mahaffey — Mon, 02 Feb 2004 16:32:15 GMT

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?

Re: Oracle Truncate Table Question

Steven E. Protter — Mon, 02 Feb 2004 16:43:05 GMT

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

Re: Oracle Truncate Table Question

Jack C. Mahaffey — Mon, 02 Feb 2004 16:52:46 GMT

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.

Re: Oracle Truncate Table Question

Volker Borowski — Mon, 02 Feb 2004 16:56:14 GMT

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

Re: Oracle Truncate Table Question

Jack C. Mahaffey — Mon, 02 Feb 2004 16:59:40 GMT

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...

Re: Oracle Truncate Table Question

Sanjay Kumar Suri — Tue, 03 Feb 2004 01:05:30 GMT

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

Re: Oracle Truncate Table Question

Alexander M. Ermes — Tue, 03 Feb 2004 01:55:45 GMT

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

Re: Oracle Truncate Table Question

Yogeeraj_1 — Tue, 03 Feb 2004 02:44:07 GMT

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

Re: Oracle Truncate Table Question

Sanjay Kumar Suri — Tue, 03 Feb 2004 03:28:01 GMT

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

Re: Oracle Truncate Table Question

Jack C. Mahaffey — Tue, 03 Feb 2004 14:14:12 GMT

Thanks all...
Jack...