rollback issues. Any ideas

[clinton]

My partner and I are having a debate on whether a select statement uses rollback segments or not?
Does selects use rollback segments ? If so How?

[marist89]

You may have a locking issue. Run a query against dba_ddl_locks such as:

col username for a12
col object_locked for a40
col MACHINE/PID for a30
col program for a30
set linesize 132
set verify off
accept objowner prompt 'Object Owner: '
accept objname prompt 'Object Name : '

select s.username, s.sid, s.program, d.owner || '.' || d.name object_locked,
decode(s.process, NULL, s.machine, s.machine || ' PID: ' ||  to_char(s.process)) "MACHINE/PID"
from v$session s, dba_ddl_locks d
where s.sid = d.session_id
and d.type like '%/Procedure/%'
and d.owner = upper('&objowner')
and d.name = upper('&objname')
/

[marist89]

Sorry about that, another poster deleted the question I was answering at the same time you added yours.

[marist89]

And Yes, select queries do use rollback segments in order to get a consistent view of the data.

[dknight]

A select does not use RBS's, but it does use cursors.

[dknight]

Marist89,

Do you mean that a SELECT selects from a RBS, but it does not place data into a RBS?

[marist89]

A SELECT statement will use a rollback segment to get data that may have changed after the start of a transaction. Nothing gets WRITTEN to an RBS, but they are USED during a SELECT.

[clinton]

I mean selecting from a table e.g the EMP table. Does that use rollback segments?

[marist89]

Checkout the following links for details:
http://otn.oracle.com/docs/products/...block.htm#4146
http://otn.oracle.com/docs/products/...cnsis.htm#2599
http://otn.oracle.com/docs/products/...intro.htm#3978

[woldcl]

Yes. For example.....

If you issue the statement

insert into EMP values...
commit;

Your friend issues

update EMP set .....

And then you do a select on EMP before he issues the commit, you will see your original values, not your friends. That's how rollback segments are used in a select statement.

Hope that helps.