Rollback segment issue !!

[Chris Fung]

Hi all,

I have a question for Rollback segment!!

Suppose I have a table "ABC" which contains 100M data. If my rollback segment tablespace contains 1 datafile which is 50MB. There are 2 rollback segments (Non-System) defined with autoextend off. Each with storage paramter (inital 1M next 1M minextent 10 maxextent 50)

What happen if:
1. I issue a sql command "select * from ABC'"??

2. I issue a sql command "select * from ABC where x='123';" Suppose the result set contains only 30 MB of data.

Cheers,

Chris,

[Jeanine Kone]

Rollback segments are used when you are updating, deleting, or inserting data. The result set of an insert statement will not be affected by the size of your rollback segements.

The problem you need to watch out for is if you are running select statements while also performing inserts, updates, and deletes. Once the rollback segement is filled up with transactional data that has not yet been committed or rolled back - your dml may fail, or your queries could die with snapshot too old.

[Brian Crabtree]

Jeanie is correct. You are thinking of sorting, which is done by the temporary tablespace. This is defined by the "temporary_tablespace" setting under "dba_users" for your user.

Rollback segments would be used in something like the following:

insert into DEF select * from ABC where x='123'

Brian

[Jean-Luc Oudart]

Hi Chris,

if you have access to Oracle Metalink (you should if you have an active support contract), you can search for "optimizing rollback segments" - Select doc id 107085.1
This doc contains pointers to others documents on same subject.

Jean-Luc