-
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?
-
You may have a locking issue. Run a query against dba_ddl_locks such as:
Code:
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')
/
Jeff Hunter
-
Sorry about that, another poster deleted the question I was answering at the same time you added yours.
Jeff Hunter
-
And Yes, select queries do use rollback segments in order to get a consistent view of the data.
Jeff Hunter
-
A select does not use RBS's, but it does use cursors.
David Knight
OCP DBA 8i, 9i, 10g
-
Marist89,
Do you mean that a SELECT selects from a RBS, but it does not place data into a RBS?
David Knight
OCP DBA 8i, 9i, 10g
-
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.
Jeff Hunter
-
I mean selecting from a table e.g the EMP table. Does that use rollback segments?
-
-
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.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|