Database Reference
In-Depth Information
EODA@ORA12CR1> insert into t values ( 1, 'hello world' );
1 row created.
EODA@ORA12CR1> commit;
Commit complete.
If we fetch out the LOB locator and open a cursor on this table as follows
EODA@ORA12CR1> declare
2 l_clob clob;
3
4 cursor c is select id from t;
5 l_id number;
6 begin
7 select txt into l_clob from t;
8 open c;
and then we modify that row and commit
9
10 update t set id = 2, txt = 'Goodbye';
11 commit;
12
we'll see upon working with the LOB locator and opened cursor that the data is presented “as of the point in time we
retrieved or opened them”
13 dbms_output.put_line( dbms_lob.substr( l_clob, 100, 1 ) );
14 fetch c into l_id;
15 dbms_output.put_line( 'id = ' || l_id );
16 close c;
17 end;
18 /
hello world
id = 1
PL/SQL procedure successfully completed.
but the data is most certainly updated/modified in the database
EODA@ORA12CR1> select * from t;
ID TXT
---------- ---------------
2 Goodbye
The read-consistent images for the cursor
C
came from the undo segments, whereas the read-consistent images
for the LOB came from the
LOBSEGMENT
itself. So, that gives us a reason to be concerned: if the undo segments are not
used to store rollback for LOBs
and
LOBs support read consistency, how can we prevent the dreaded
ORA-01555:
snapshot too old
error from occurring? And, as important, how do we control the amount of space used by these old
versions? That is where
RETENTION
, and alternatively,
PCTVERSION
come into play.
