Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

redo log files

Posted on 1999-07-22
9
Medium Priority
?
1,903 Views
Last Modified: 2008-01-09
Oracle 7.3.4, archive mode is on

I have a big table and i want to remove this table with a
minimum redo log files entries.

Can you explain the difference on redo log files activity
for these statements:

drop table toto

truncate table toto

delete from toto

Thanks
0
Comment
Question by:djamal
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 2
  • 2
9 Comments
 
LVL 6

Accepted Solution

by:
mshaikh earned 90 total points
ID: 1087665
delete will produce redo log info. Thus it will take a long time on large table.

drop table and truncate table will not produce redo log info that can be used by the user. Thus they will be fast regardless of the size of the table. Truncate table will give the same result as delete except you can't rollback.
Truncate unlike drop ta ble will preserve the table structure and relationships etc.
0
 
LVL 6

Expert Comment

by:mshaikh
ID: 1087666
If you want more clarification on any one particular point, then just ask.
0
 
LVL 2

Expert Comment

by:avico
ID: 1087667
Drop table - (DDL) Free the blocks used by the table. Also updates the data dictionary to designate the table does not exist. This operation generates redo logs records. Generates a DDL exclusive lock on the table until it dropped.

Delete table - (DML) Deletes all rows in the table. Doesn't deallocate the storage (extents etc.). Generates much redo log records. Also locks the table in row-exclusive mode until the next commit or rollback is issued.

Truncate table - (DDL) Simply resets the number of rows in a table to 0. Doesn't require a commit (since a DDL statement always issues a commit). Frees the table's blocks and extents.Generate a very few log records.

If you need to delete all data from a table, Truncate would be the less consuming operation.

Hope it helped, Avico.
0
 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

 

Author Comment

by:djamal
ID: 1087668
Mr avico say the drop table table generate redo logs records
and Mr mshaikh say the drop table table does not generate
redo logs records.

I need more information

0
 
LVL 6

Expert Comment

by:mshaikh
ID: 1087669
I am sorry, I ment 'rollback' information not redo log. In my answer replace all 'redo log' with 'rollback' and then it will become what I was trying to say. So some reason, I was reading your question wrong.

'Redo logs' records will be generated for all three of these statements regardless.

I apologize.

0
 
LVL 6

Expert Comment

by:mshaikh
ID: 1087670
Also, you can't rollback 'drop table' and 'truncate table', you can rollback delete table thats why it takes so long.


0
 

Author Comment

by:djamal
ID: 1087671
where can i find more information on redo log files entries
for the previous statement?
0
 
LVL 2

Expert Comment

by:avico
ID: 1087672
As I wrote before, if you have have a big table and you  want to remove all data from this table, the least redo log consuming operation would be 'truncate table'.
However, if you would like to remove this table from the database, the only way of doing so is 'drop table'.

If you need to delete selctive rows, the only way to do so is by using the 'delete table'.



0
 
LVL 6

Expert Comment

by:mshaikh
ID: 1087673
Redo log only record all the activity done to the database, and is used to ROLL FORWARD to a certain point of time after a database recovery from a backup. All redo logs do is record changes to the database. If you drop a table it accually records the drop table command. If you truncate a table it will record that too, it you delete a table it will record the delete statment. In case you had to recover from a database crash or loss. You recover to your last backup, then you apply archived redo logs and redo logs to imitate all the change actions (SQL DDL and DML) done on the database including the uncommited changes. Redo log record are noting more than the DDL and DML statement executed on the database in the sequence they were executed.
For example: if you create a table, populate it and then drop it, Redo logs will record each of theses changes. If these redo log  were to be applied incase of crash, the redo logs will acctually create the table, then populate it and then drop it exactly immitating what was before the crash. It is not smart enough to see that the table does not need to be created beacause it was eventually dropped.

Some more info from Oracle Documnetation:

Truncating Tables

You can use the TRUNCATE command to quickly remove all rows from a table or cluster. Removing rows with the TRUNCATE command is faster than removing them with the DELETE command for the following reasons:

The TRUNCATE command is a data definition language (DDL) command and generates no rollback information.
Truncating a table does not fire the table's DELETE triggers.
The TRUNCATE command allows you to optionally deallocate the space freed by the deleted rows. The DROP STORAGE option deallocates all but the space specified by the table's MINEXTENTS parameter.

Deleting rows with the TRUNCATE command is also more convenient than dropping and re-creating a table because dropping and re-creating:

invalidates the table's dependent objects, while truncating does not
requires you to regrant object privileges on the table, while truncating does not
requires you to re-create the table's indexes, integrity constraints, and triggers and respecify its STORAGE parameters, while truncating does not

--------------------------------------------------------------------------------
Note:

When you truncate a table, the storage parameter NEXT is changed to be the size of the last extent deleted from the segment in the process of truncation.

 
--------------------------------------------------------------------------------
 
 


Restrictions
When you truncate a table, NEXT is automatically reset to the last extent deleted.

You cannot individually truncate a table that is part of a cluster. You must either truncate the cluster, delete all rows from the table, or drop and re-create the table.

You cannot truncate the parent table of an enabled referential integrity constraint. You must disable the constraint before truncating the table. (An exception is that you may truncate the table if the integrity constraint is self-referential.)

You cannot roll back a TRUNCATE statement.


0

Featured Post

Learn Veeam advantages over legacy backup

Every day, more and more legacy backup customers switch to Veeam. Technologies designed for the client-server era cannot restore any IT service running in the hybrid cloud within seconds. Learn top Veeam advantages over legacy backup and get Veeam for the price of your renewal

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

721 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question