Home » RDBMS Server » Server Administration » rollback and tablespace space usage
rollback and tablespace space usage [message #297695] Sat, 02 February 2008 10:47 Go to next message
varu123
Messages: 754
Registered: October 2007
Senior Member
If a transaction is rollbacked,then would the free space of the tablespace be increased?
And after the undo_retention time ,the free space in undo tablespace would be 100%?

[Updated on: Sat, 02 February 2008 10:54]

Report message to a moderator

Re: rollback and tablespace space usage [message #297697 is a reply to message #297695] Sat, 02 February 2008 10:55 Go to previous messageGo to next message
Michel Cadot
Messages: 64145
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Which tablespace and rollback of what?
I doubt rolling back delete statements will increase free space in table tablespace.

Regards
Michel
Re: rollback and tablespace space usage [message #297698 is a reply to message #297695] Sat, 02 February 2008 10:57 Go to previous messageGo to next message
varu123
Messages: 754
Registered: October 2007
Senior Member
haha,yes except delete if any other DML is rollbacked,so the data tablespace free space will increase.
Re: rollback and tablespace space usage [message #297699 is a reply to message #297698] Sat, 02 February 2008 11:11 Go to previous messageGo to next message
Michel Cadot
Messages: 64145
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
No, never except, maybe, in the case of direct load inserts but I doubt it.
I don't have a database by hand at this time but it is easy to make a test.

Regards
Michel
Re: rollback and tablespace space usage [message #297702 is a reply to message #297695] Sat, 02 February 2008 11:23 Go to previous messageGo to next message
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 #297703 is a reply to message #297702] Sat, 02 February 2008 11:34 Go to previous messageGo to next message
Michel Cadot
Messages: 64145
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Right.

Regards
Michel
Re: rollback and tablespace space usage [message #297704 is a reply to message #297695] Sat, 02 February 2008 11:39 Go to previous messageGo to next message
varu123
Messages: 754
Registered: October 2007
Senior Member
So Is it a good practice to reorganise tables after bulk deletes to retrieve free space from used blocks?
Re: rollback and tablespace space usage [message #297707 is a reply to message #297704] Sat, 02 February 2008 11:56 Go to previous messageGo to next message
Michel Cadot
Messages: 64145
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
If you don't plan to reinsert data, yes it is a good practice.

Regards
Michel
Re: rollback and tablespace space usage [message #298801 is a reply to message #297695] Thu, 07 February 2008 14:41 Go to previous messageGo to next message
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 #298808 is a reply to message #298801] Thu, 07 February 2008 15:19 Go to previous messageGo to next message
Michel Cadot
Messages: 64145
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
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 #299019 is a reply to message #298808] Fri, 08 February 2008 11:26 Go to previous messageGo to next message
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 Go to previous messageGo to next message
Michel Cadot
Messages: 64145
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 #299046 is a reply to message #297695] Fri, 08 February 2008 13:15 Go to previous messageGo to next message
varu123
Messages: 754
Registered: October 2007
Senior Member
Quote:
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 Laughing


yes but when you are drunk there is more probability of meeting with an accident.
hence when data buffers are full,dbwr immediately flushes the data[only the changed data ie dirty buffers]to datafiles.

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

when you rollback,undo is applied and the actual data is modified to the previous image of data and the corresponding re do is generated too.
locks are also released.
so a commit = rollback but the opposite way Shocked
Re: rollback and tablespace space usage [message #299050 is a reply to message #299046] Fri, 08 February 2008 13:24 Go to previous messageGo to next message
Michel Cadot
Messages: 64145
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
dbwr immediately flushes the data

Immediately is not true and it depends on what you meant with "full". Full of what?

Quote:
so a commit = rollback but the opposite way

Not quite true.
When you commit, nothing is done (except notifying lgwr to write log buffer (actually up to commit marker) onto disk and release lock).
When you roll back, in addition you have to undo what has been done so this may lead to a big activity on buffer cache and disks.
So not quite the opposite, one is always a fraction of second (unless you are very unlucky), the other one may last hours.

Regards
Michel
Re: rollback and tablespace space usage [message #299054 is a reply to message #297695] Fri, 08 February 2008 13:37 Go to previous messageGo to next message
varu123
Messages: 754
Registered: October 2007
Senior Member
Quote:
When you commit, nothing is done (except notifying lgwr to write log buffer (actually up to commit marker) onto disk and release lock).


you put all the major activities in brackets and nothing is done??

A commit marker is necessarily the scn?
and what happens at checkpoint is also the scn marked to datafiles and control files.

whats the difference?This is where i confuse all the time Confused
Re: rollback and tablespace space usage [message #299059 is a reply to message #299054] Fri, 08 February 2008 14:00 Go to previous message
Michel Cadot
Messages: 64145
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



Previous Topic: ORA-39778
Next Topic: Old sql problem
Goto Forum:
  


Current Time: Fri Dec 09 11:40:32 CST 2016

Total time taken to generate the page: 0.13681 seconds