Home » RDBMS Server » Server Administration » Undo info of insert statement (Oracle 10g windows xp)
Undo info of insert statement [message #558576] Sun, 24 June 2012 07:51 Go to next message
morad_dba
Messages: 73
Registered: June 2008
Member

Dear all,

If we insert a row in a database table then the new row stays at database buffer cache in SGA (until commit), right?. The target table is not affected (before commit). The new row is saved after commit.

I saw a concepts at Sybex oracle 10g oca book (Page 406) as follows:

" INSERT statements use little space in an undo segment; only the pointer to the new row is stored in the undo tablespace. To undo an INSERT statement, the pointer locates the new row and deletes it from the table if the transaction is rolled back. "

My question is If the row is not saved at table before commit, if we issue rollback then how oracle delete from table? I think the new row is deleted from database buffer cache in SGA.

Hope you all help me to understand the concept.

regards,

Morad
Re: Undo info of insert statement [message #558579 is a reply to message #558576] Sun, 24 June 2012 08:31 Go to previous messageGo to next message
BlackSwan
Messages: 23134
Registered: January 2009
Senior Member
Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/


What occurs when INSERT changes more rows than can be contained within RAM?
Re: Undo info of insert statement [message #558584 is a reply to message #558576] Sun, 24 June 2012 09:27 Go to previous message
Michel Cadot
Messages: 59977
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
If we insert a row in a database table then the new row stays at database buffer cache in SGA (until commit), right?.


Wrong.

Quote:
The target table is not affected (before commit)


Wrong.

Quote:
The new row is saved after commit.


Wrong.

Quote:
My question is If the row is not saved at table before commit, if we issue rollback then how oracle delete from table?


Because it is.

Quote:
Hope you all help me to understand the concept.


All of this (and much more) is explain with details and pictures in Database Concepts

Regards
Michel
Previous Topic: Tablespace creation (merged 3)
Next Topic: Oracle ASM
Goto Forum:
  


Current Time: Thu Dec 18 06:03:57 CST 2014

Total time taken to generate the page: 0.19122 seconds