-
If, in one Oracle session, I do :
scott#1::SQL>create table t as select * from all_users;
Table created.
and, in another session, I do :
scott#2::SQL>variable x refcursor;
scott#2::SQL>begin
2 open :x for select * from t;
3 end;
4 /
PL/SQL procedure successfully completed.
The cursor is opened but not readed yet in sesion 2. Regardless any other work, it will be time-read consistent :
scott#1::SQL>delete from t;
5 rows deleted.
scott#1::SQL>commit;
Commit complete.
==> session 2 yet have the data as readed :
scott#2::SQL>print x
USERNAME USER_ID CREATED
------------------------------ --------- ---------
SYS 0 16-SEP-97
SYSTEM 5 16-SEP-97
SCOTT 10 16-SEP-97
DBSNMP 13 16-SEP-97
PO7 14 04-JUL-01
5 rows selected.
The reason , AFAIK, is because Oracle keeps the data needed for read-consistent in rollback segments. So, my question is :
HOW to find rollback segs holding blocks what will be needed to read-consistency ? It´s possible ? In the above example, I tried to look at dba_segments, dba_extents, but no luck.
[]s
Chiappa
-
SELECT r.name "ROLLBACK SEGMENT NAME ",
p.pid "ORACLE PID",
p.spid "SYSTEM PID ",
NVL ( p.username , 'NO TRANSACTION'),
p.terminal
FROM v$lock l, v$process p, v$rollname r
WHERE l.sid = p.pid(+)
AND TRUNC (l.id1(+)/65536) = r.usn
AND l.type(+) = 'TX'
AND l.lmode(+) = 6
ORDER BY r.name
/
Oracle DBA and Developer
-
It works ONLY while I have an open transaction (in the example, while I did the DELETE but don´t the COMMIT, yet).
When I ended the transaction in session 1 with COMMIT, the needed data for the cursor in session 2 will be in some rollback slot. Is possible to find in what rollback segment/extent it will be ?
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
|