|
|
|
|
|
|
|
|
| Re: rollback and tablespace space usage [message #297702 is a reply to message #297695] |
Sat, 02 February 2008 11:23   |
varu123
Messages: 754
Registered: October 2007
|
Senior Member |
|
|
Let suppose a table has 100 rows.After insertion,it has 1000 rows.
At this point,undo tablespace contain 100 rows and datafile contains uncommitted 1000 rows.
Now if the entire transaction is rollbacked,then undo tablespace would apply all the previous 100 rows to datafiles.
and the rest 900 rows are deleted by oracle.
But since the datafile now contains used blocks,so the datafile size would remain as it was before rollback.
Right?
|
|
|
|
|
|
|
|
|
|
| Re: rollback and tablespace space usage [message #298801 is a reply to message #297695] |
Thu, 07 February 2008 14:41 |
IT Guru
Messages: 59
Registered: January 2007
|
Member |
|
|
Questions is not complete.
Need to stat what kind of transaction rollback.
Yes once trasaction is over + undo_retention time exipre it will release space from undo table space.
Only if you have add more raw to table [or modify present raw with more dat ] & as a reslut of no space in data buffer pool it got flush to data file............ then you roll back ...............it will free up space in data file...
I dont thinks proper word for this would be "free space of the tablespace be increased"
rather one can say it would release space in that tablespace
[Updated on: Thu, 07 February 2008 15:16] by Moderator Report message to a moderator |
|
|
|
|
|
| Re: rollback and tablespace space usage [message #299019 is a reply to message #298808] |
Fri, 08 February 2008 11:26 |
IT Guru
Messages: 59
Registered: January 2007
|
Member |
|
|
AS buffer pool become full it flsuh its data to datafile [tablespace].
If data buffer pool has space, data will remain in there only & when you issue rollback data buffer pool gets clear but no changes will have to tablespace.
| Michel Cadot wrote on Thu, 07 February 2008 15:19 | | Quote: | Only if you have add more raw to table [or modify present raw with more dat ] & as a reslut of no space in data buffer pool it got flush to data file............ then you roll back ...............it will free up space in data file...
|
I don't think this is true and I don't see the relation with buffer pool.
Buffer pool is almost always full (but just after startup time or if database does nothing).
Regards
Michel
|
|
|
|
|
| Re: rollback and tablespace space usage [message #299028 is a reply to message #299019] |
Fri, 08 February 2008 12:14 |
 |
Michel Cadot
Messages: 68686
Registered: March 2007
Location: Nanterre, France, http://...
|
Senior Member
Account Moderator |
|
|
| Quote: | AS buffer pool become full it flsuh its data to datafile [tablespace].
|
This is not true and not wrong.
In the same way that saying that if your are drunk you can have an accident. You may have one without being drunk, you may not have one being drunk.
As I said, being full is the normal situation of a buffer pool.
And a buffer does not flushes anything, it is just a container, it does not do anything.
| Quote: | If data buffer pool has space, data will remain in there only & when you issue rollback data buffer pool gets clear but no changes will have to tablespace.
|
"data buffer pool gets clear" this is wrong, buffer pool is not clear at all.
"but no changes will have to tablespace", this is also wrong, tablespace (at least blocks in buffer pool and may be on disk) changed and this is the reason why rollbacks take long.
Regards
Michel
|
|
|
|
|
|
|
|
|
|
| Re: rollback and tablespace space usage [message #299059 is a reply to message #299054] |
Fri, 08 February 2008 14:00 |
|
Michel Cadot
Messages: 68686
Registered: March 2007
Location: Nanterre, France, http://...
|
Senior Member
Account Moderator |
|
|
| Quote: | you put all the major activities in brackets
|
No, I put in brackets the activity that is common to commit and rollback to emphasize the differences.
| Quote: | A commit marker is necessarily the scn?
|
A commit marker is a record which contains a code saying it is a commit record, it is associated to a SCN as all redo records (change vectors). The difference is that it generates its own SCN.
| Quote: | whats the difference?
|
At checkpoint, the SCN in datafiles and the checkpoint SCN in control file is the one associated to the greatest SCN of checkpointed blocks. Or, if you prefer, at checkpoint a SCN is choosed and all blocks with SCN less than this one is flushed. This chosen SCN is named checkpoint SCN.
At commit time, a new SCN is generated.
In v$database, you have CHECKPOINT_CHANGE# (for the first one) and CURRENT_SCN (for more or less the second one, more or less because there are other events than commit that generates a new scn).
Regards
Michel
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
