A new oracle dba can find in this article about the different types of LOB’s. LOB means Large objects. so when one want’s to store some word documents or zip files or huge amounts of data then many oracle developers use LOB’s. The article contains the syntax for different commands like creating LOB columns, inserting data into the table containing LOB columns etc. Have fun.
| : | Example SQL Demonstrating use of LOBs in Oracle | |||
| Doc ID: | 66046.1 | Type: | BULLETIN | |
| Modified Date: | 23-NOV-2008 | Status: | PUBLISHED | |
Checked for relevance on 23-Nov-2008.
PURPOSE
This note in the form of a SQL script demonstrates the use of LOBs in
Oracle8i.
SCOPE & APPLICATION
Instructional.
Oracle LOBs:
===============
Manipulating LOBs in Oracle
This script is based on Note 47740.1 Oracle8.
— There are 4 types of LOBs:
—
— 1. CLOB – Character LOB (single byte characters only)
— a. V8.0: The database charset must be fixed width
— b. V8i: The database charset may be fixed or variable width; variable width data is stored in the database as UCS2 (Unicode double byte) regardless of database charset)
— c. V8 & V9 <= 4Gig
—
— 2. BLOB – Binary LOB
— a. V8 & V9 <= 4Gig
—
— 3. NCLOB – National Character LOB
— a. 8.0: Fixed width multibyte national charset characters only
— b. 8i: Fixed or variable width multibyte national charset characters; variable width data is stored in the database as UCS2 regardless of database charset)
— c. V8 & V9 <= 4Gig
— d. Note, unlike the other types of LOBs, NCLOBs may not be part of an Object type though they may be arguments to an Object method.
—
— 4. BFILE – Binary file
— a. <=4Gig
— b. Stored in the O/S, not the database.
— c. The database holds the directory alias and filename.
— d. V8 & V9 <= 4Gig
—
— 5. 10G Size Restrictions:
— a. The size of BLOB, CLOB, and NCLOB data can be up to (4 gigabytes -1) * (the value of the CHUNK parameter of LOB storage).
— b. If the tablespaces in your database are of standard block size, and if you have used the default value of the CHUNK parameter of LOB storage when creating a LOB column, then this is equivalent to (4 gigabytes – 1) * (database block size).
— c. BFILE data can be up to 232-1 bytes, although your operating system may impose restrictions on this maximum.
—
— For more information on sizing please refer to the Oracle® Database SQL Reference.
drop table test_lobs
/
drop directory tmp_dir
/
drop table long_data
/
—
–When a LOB is stored in a table the data (LOB VALUE) and a pointer to
–that data, called a LOB LOCATOR, are held separately. The data may
–be stored along with the locator in the table itself or in a separate
–table. The LOB clause on the create table can specify whether an
–attempt should be made to store data in the main table or a separate
–one. It may also be used to specify a separate tablespace and storage
–clause for both the LOB table and its associated index.
–Note if you request data to be stored inline for a given LOB column,
–this will only happen so long as the data for any given row does NOT
–exceed 4K.
—
–Other important LOB specific storage characteristics:
—
— PCTVERSION – % of LOB storage space kept for old versions of
— LOB pages in order to maintain read consistency
— of reads started before an update. Default 10%.
— CHUNK – the number of database blocks of LOB data
— accessed at once via a read or write.
—
–Create a table with a CLOB, BFILE and BLOB column, the BLOB column’s
–data to always be stored separately from the row.
—
create table test_lobs (
c1 number,
c2 clob,
c3 bfile,
c4 blob
)
LOB (c2) STORE AS (ENABLE STORAGE IN ROW)
LOB (c4) STORE AS (DISABLE STORAGE IN ROW)
/
—
desc test_lobs
—
— Name Null? Type
— ——————————- ——– —-
— C1 NUMBER
— C2 CLOB
— C3 BINARY FILE LOB
— C4 BLOB
—
—
–Insert some rows. The empty_<lob>() function acts as a constructor
–to generate a LOB locator for that column. Without a locator that
–LOB cannot be accessed through PL/SQL etc.
—
–First row – no locators.
—
insert into test_lobs values (1,null,null,null)
/
—
–Second row – "null" locators – ie locators created but point to nothing.
–Note there is not an empty locator as such for the BFILE though we
–could also initialise the BFILE using BFILENAME with a null directory
–and file, ie BFILENAME(null,null).
—
insert into test_lobs values (2,EMPTY_CLOB(),null,EMPTY_BLOB())
/
—
–Third row – it is possible to insert data directly up to 4K.
–Even though you are only really accessing the locator, the data is
–stored as appropriate behind the scenes. When inserting directly into
–a BLOB either the string must be hex as an implicit HEXTORAW will be
–done or you can call UTL_RAW.CAST_TO_RAW(‘the string’) to convert it
–for you. Note ‘48656C6C6F’ = ‘Hello’.
—
insert into test_lobs values (3,’Some data for record 3.’,
BFILENAME(null,null),
‘48656C6C6F’||UTL_RAW.CAST_TO_RAW(‘ there!’))
/
—
–Try and select back the data.
—
select * from test_lobs
/
—
–gives:
—
–Column or attribute type can not be displayed by SQL*Plus
—
–SQL*Plus cannot convert the data behind the locator to hex for the
–BLOB nor interpret a locator for a BFILE (even when null). Hence:
—
column c2 format a60 wrap
select c1, c2 from test_lobs
/
—
— C1 C2
———— ————————————————–
— 1
— 2
— 3 Some data for record 3.
—
—
–In the above case we are really fetching only the LOB locator. SQL*Plus
–will also then fetch the corresponding data. If we use a 3GL or PL/SQL
–we can insert data from a character string variable but not select it
–into one. For example:
—
declare
c_lob varchar2(10);
begin
c_lob := ‘Record 4.’;
insert into test_lobs values (4,c_lob,BFILENAME(null,null),
EMPTY_BLOB());
end;
/
—
–works, but:
—
declare
c_lob varchar2(10);
begin
select c2 into c_lob from test_lobs where c1 = 4;
end;
/
—
–gives:
—
–ERROR at line 4:
–ORA-06550: line 4, column 19:
–PLS-00385: type mismatch found at ‘C_LOB’ in SELECT…INTO statement
–ORA-06550: line 4, column 4:
–PL/SQL: SQL Statement ignored
—
—
—
create table long_data (c1 number, c2 long)
/
insert into long_data values
(1, ‘This is some long data to be migrated to a CLOB’)
/
—
–TO_LOB may be used in CREATE TABLE AS SELECT or INSERT…SELECT
–statements, eg:
—
insert into test_lobs select 5, TO_LOB(c2), null, null
from long_data
/
select c2 from test_lobs
where c1 = 5
/
—
–C2
————————————————————–
–This is some long data to be migrated to a CLOB
—
rollback
/
—
–Set up the BFILE column. To do this it is first necessary to create a
–handle to any directory where a BFILE may reside.
—
–In /tmp create a 4 line file, rec2.txt, as follows:
—
–This is some data for record 2’s BFILE column. The data is
–stored in a file called "rec2.txt". The file is placed in
–/tmp.
–The file comprises a total of 4 lines of text.
—
—
–In /tmp create a second 5 line file, rec3.txt, as follows:
—
–This is some data for record 3’s BFILE column. The data is
–stored in a file called "rec3.txt". The file is placed in
–/tmp. The file comprises a total of 5 lines of text and
–will be used to demonstrate the functionality of the
–DBMS_LOB package.
—
—
–First create the ALIAS for the directory /tmp.
—
create directory tmp_dir as ‘/tmp’
/
—
–Now update the records to associate the BFILE column with the two files
–created above.
—
update test_lobs set c3 = BFILENAME(‘TMP_DIR’,’rec2.txt’)
where c1 = 2
/
update test_lobs set c3 = BFILENAME(‘TMP_DIR’,’rec3.txt’)
where c1 = 3
/
—
–Note the files associated with these columns are READ-ONLY through
–Oracle – they must be maintained via the operating system itself.
–To access the BFILE columns you must use e.g. the DBMS_LOB package
–or OCI.
—
—
–Using DBMS_LOB
—
–Note any DBMS_LOB routines that take a CLOB as a parameter may also
–be passed an NCLOB since this is simply a special type of CLOB.
—
–Getting lengths of the LOB data. Notice the zero lengths where "empty"
–locators were specified.
—
column len_c2 format 9999
column len_c3 format 9999
column len_c4 format 9999
select c1, DBMS_LOB.GETLENGTH(c2) len_c2, DBMS_LOB.GETLENGTH(c3) len_c3,
DBMS_LOB.GETLENGTH(c4) len_c4
from test_lobs
/
—
— C1 LEN_C2 LEN_C3 LEN_C4
———— —— —— ——
— 1
— 2 0 172 0
— 3 23 247 12
— 4 9 0
—
—
–Using SUBSTR/INSTR – both may be used on all 3 types (CLOB, BLOB and
–BFILE) however for BFILEs the file must first have been opened – hence
–the functions may only be used within PL/SQL in this case.
—
–For SUBSTR the parameters are LOB, amount, offset – the opposite to
–the standard substr function; for INSTR they are LOB, string, offset,
–occurence, the latter 2 defaulting to 1 if omitted. So the following
–does a substr from offset 3 in the CLOB for 9 characters and returns
–the first occurence of the binary string representing "ello" in the
–BLOB.
—
column sub_c2 format a10
column ins_c4 format 99
select c1, DBMS_LOB.SUBSTR(c2,9,3) sub_c2,
DBMS_LOB.INSTR(c4,UTL_RAW.CAST_TO_RAW(‘ello’),1,1) ins_c4
from test_lobs
/
—
— C1 SUB_C2 INS_C4
———— ———- ——
— 1
— 2 0
— 3 me data f 2
— 4 cord 4. 0
—
—
–The following PL/SQL block demonstrates some of the DBMS_LOB
–functionality. Note the use of "set long 1000" to prevent the
–output data from being truncated.
—
set serveroutput on
set long 1000
declare
b_lob BLOB;
c_lob CLOB;
c_lob2 CLOB;
bf BFILE;
buf varchar2(100) :=
‘This is some text to put into a CLOB column in the’ ||
chr(10) ||
‘database. The data spans 2 lines.’;
n number;
fn varchar2(50); –Filename
fd varchar2(50); –Directory alias
–Procedure to print out the LOB value from c_lob, one line
–at a time..
procedure print_clob is
offset number;
len number;
o_buf varchar2(200);
amount number; –}
f_amt number := 0; –}To hold the amount of data
f_amt2 number; –}to be read or that has been
amt2 number := -1; –}read
begin
len := DBMS_LOB.GETLENGTH(c_lob);
offset := 1;
while len > 0 loop
amount := DBMS_LOB.INSTR(c_lob,chr(10),offset,1);
–Amount returned is the count from the start of the file,
–not from the offset.
if amount = 0 then
–No more linefeeds so need to read remaining data.
amount := len;
amt2 := amount;
else
f_amt2 := amount; –Store position of next LF
amount := amount – f_amt; –Calc position from last LF
f_amt := f_amt2; –Store position for next time
amt2 := amount – 1; –Read up to but not the LF
end if;
if amt2 != 0 then
–If there is a linefeed as the first character then ignore.
DBMS_LOB.READ(c_lob,amt2,offset,o_buf);
dbms_output.put_line(o_buf);
end if;
len := len – amount;
offset := offset+amount;
end loop;
end;
begin
–For record 1 we did not initialise the locators so do so now.
–Note the RETURNING clause will retrieve the new lob locators so
–we do not need to perform an extra select. The update also
–ensures the corresponding row is locked.
update test_lobs set c2 = EMPTY_CLOB(), c4 = EMPTY_BLOB()
where c1 = 1 RETURNING c2, c4 INTO c_lob, b_lob;
–Also select the CLOB locator for record 2.
select c2 into c_lob2 from test_lobs where c1 = 3;
–Write the above buffer into the CLOB column. Offset is 1, amount
–is the size of the buffer.
DBMS_LOB.WRITE(c_lob,length(buf),1,buf);
–See what we’ve got – a line at a time.
print_clob;
–Add some more data to the above column and row. First commit what
–we have. Note when we commit, under 8.0, our LOB locators we
–previously held in c_lob, b_lob and c_lob2 will be lost and so must be
–reselected.
–We must lock the row we are going to update through DBMS_LOB.
select c2 into c_lob from test_lobs where c1 = 1 FOR UPDATE;
–**As of 8i**: no longer need this select:
–select c2 into c_lob2 from test_lobs where c1 = 3;
–First append a linefeed then some data from another CLOB.
–Under 8.0 this was a two step process, first you had to get the
–the length of the LOB and secondly write the data using an offset
–of the length plus one. ** Since 8.1 you have a WRITEAPPEND
–function that does the two steps in a single call.
–**no longer need to get the length:
–n := DBMS_LOB.GETLENGTH(c_lob)+1;
–DBMS_LOB.WRITE(c_lob,1,n,chr(10)); — 1 char from offset n
DBMS_LOB.WRITEAPPEND(c_lob,1,chr(10));
DBMS_LOB.APPEND(c_lob,c_lob2);
dbms_output.put_line(chr(10));
print_clob;
–Compare c_lob2 with the third line of c_lob – they should be
–the same – in which case remove it. Note the TRIM function takes
–the size at which you wish the LOB to end up, NOT how much you
–want to remove.
n := DBMS_LOB.GETLENGTH(c_lob) – DBMS_LOB.GETLENGTH(c_lob2);
if DBMS_LOB.COMPARE(c_lob,c_lob2,DBMS_LOB.GETLENGTH(c_lob2),n+1,1) = 0 then
DBMS_LOB.TRIM(c_lob,n-1);
end if;
dbms_output.put_line(chr(10));
print_clob;
–Remove the data from the column completely, ie use ERASE to
–remove all bytes from offset 1. Note unlike TRIM, ERASE does not
–cause the length of the LOB to be shortened – all bytes are simply
–set to zero. Thus GETLENGTH will return 0 after TRIM’ing all bytes
–but the original length after ERASE’ing.
n := DBMS_LOB.GETLENGTH(c_lob);
DBMS_LOB.ERASE(c_lob,n,1);
–Add data from c_lob2 plus a trailing linefeed.
DBMS_LOB.COPY(c_lob,c_lob2,DBMS_LOB.GETLENGTH(c_lob2),1,1);
–**could simply use WRITEAPPEND here.
n := DBMS_LOB.GETLENGTH(c_lob2)+1;
DBMS_LOB.WRITE(c_lob,1,n,chr(10)); — 1 char from offset n
–Now append the column with data read from one of the BFILE
–columns.
select c3 into bf from test_lobs where c1 = 3;
–First get and output the file details.
DBMS_LOB.FILEGETNAME(bf,fd,fn);
dbms_output.put_line(chr(10));
dbms_output.put_line(‘Appending data from file ‘||fn||
‘ in directory aliased by ‘||fd||’:’);
dbms_output.put_line(chr(10));
–Open the file to read from it – first checking that it does in
–fact still exist in the O/S and that it is not already open.
if DBMS_LOB.FILEEXISTS(bf) = 1 and
DBMS_LOB.FILEISOPEN(bf) = 0 THEN
DBMS_LOB.FILEOPEN(bf);
end if;
DBMS_LOB.LOADFROMFILE(c_lob,bf,DBMS_LOB.GETLENGTH(bf),n+1,1);
DBMS_LOB.FILECLOSE(bf); — could use DBMS_LOB.FILECLOSEALL;
print_clob;
commit;
end;
/
—
–gives:
—
–This is some text to put into a CLOB column in the
–database. The data spans 2 lines.
—
—
–This is some text to put into a CLOB column in the
–database. The data spans 2 lines.
–Some data for record 3.
—
—
–This is some text to put into a CLOB column in the
–database. The data spans 2 lines.
—
—
–Appending data from file rec3.txt in directory aliased by TMP_DIR:
—
—
–Some data for record 3.
–This is some data for record 3’s BFILE column. The data is
–stored in a file called "rec3.txt". The file is placed in
–/tmp. The file comprises a total of 5 lines of text and
–will be used to demonstrate the functionality of the
–DBMS_LOB package.
—
—
select c1, c2 from test_lobs
/
—
— C1 C2
———— ————————————————————
— 1 Some data for record 3.
— This is some data for record 3’s BFILE column. The data is
— stored in a file called "rec3.txt". The file is placed in
— /tmp. The file comprises a total of 5 lines of text and
— will be used to demonstrate the functionality of the
— DBMS_LOB package.
— 2
— 3 Some data for record 3.
— 4 Record 4.
—
—
–An important thing to note when using LOB locators within DBMS_LOB
–and PL/SQL is that a given locator always gives a read consistent
–image from when it was selected. You will see any changes that you
–make to the LOB using that locator and DBMS_LOB, but not those made,
–even in the same transaction, through other LOB locators pointing to
–the same LOB values or made via SQL directly. For example:
—
declare
c_lob CLOB;
begin
select c2 into c_lob from test_lobs where c1 = 1;
dbms_output.put_line(‘Before update length of c2 is ‘||
DBMS_LOB.GETLENGTH(c_lob));
update test_lobs set c2 = ‘This is a string.’ where c1 = 1;
dbms_output.put_line(‘After update length of c2 is ‘||
DBMS_LOB.GETLENGTH(c_lob));
select c2 into c_lob from test_lobs where c1 = 1;
dbms_output.put_line(‘After reselecting locator length of c2 is ‘||
DBMS_LOB.GETLENGTH(c_lob));
rollback;
end;
/
—
–gives:
—
–Before update length of c2 is 271
–After update length of c2 is 271
–After reselecting locator length of c2 is 17
—
—
–**The following PL/SQL blocks demonstrate the remaining
–new DBMS_LOB functionality introduced in version 8.1.
—
–Temporary LOBs
–~~~~~~~~~~~~~~
–In version 8.1 it is now possible to create temporary LOBs. These are
–LOB locators that point to LOB values held in the user’s temporary
–tablespace. Temporary LOBs are automatically initialised upon creation
–and exist for the duration specified in the create command or until
–explicitly freed by the user. The duration of a temporary LOB may be
–be session or call. At the end of the given duration the temporary
–LOB is automatically deleted. Temporary LOBs can be used in the
–same way as normal internal LOBs through the DBMS_LOB package (note
–there is no temporary version of a BFILE), however being only part of
–the temporary tablespace they are not permanently stored in the database
–and they cause no rollback or undo information to be generated.
–Temporary LOBs may be cached though. Because versioning (ie keeping
–copies of pages prior to updates) is not performed for temporary LOBs,
–if a temporary LOB locator is copied and then used to update the LOB
–value, the whole LOB value must be copied in order to maintain a read
–consistent image via both locators. For this reason it is recommended
–that whenever LOB locators are passed as IN OUT or OUT parameters to
–procedures, functions or methods, NOCOPY is specified so they are
–passed by reference.
—
–The following example uses a temporary LOB to reverse one of the LOB
–values in the table and then inserts the reversed LOB as a new row.
—
declare
c_lob CLOB; –permanent LOB locator
t_lob CLOB; –temporary LOB locator
buf varchar2(32000); –}this example assumes the LOB is
buf2 varchar2(32000); –}less than 32K.
chunk number;
len number;
offset number;
amount number;
begin
select c2 into c_lob from test_lobs where c1 = 1;
–Create a temporary LOB. The parameters to CREATETEMPORARY are
–locator, use caching or not and duration. Set no caching and a
–duration of call since the temporary LOB is not required outside
–of this PL/SQL block.
DBMS_LOB.CREATETEMPORARY(t_lob,FALSE,DBMS_LOB.CALL);
–**Use GETCHUNKSIZE to get the amount of space used in a LOB
–chunk for storing the LOB value. Using this amount for reads and
–writes of the LOB will improve performance.
chunk := DBMS_LOB.GETCHUNKSIZE(c_lob);
dbms_output.put_line(‘Chunksize of column c2 is ‘||chunk);
dbms_output.put_line(‘Chunksize of temporary LOB is ‘||
DBMS_LOB.GETCHUNKSIZE(t_lob)); –for info only
len := DBMS_LOB.GETLENGTH(c_lob);
offset := 1;
buf := null;
while offset < len loop
if len – (offset-1) > chunk then
amount := chunk;
else
amount := len – (offset-1);
end if;
buf2 := null;
DBMS_LOB.READ(c_lob,amount,offset,buf2);
buf := buf||buf2;
offset := offset + amount;
end loop;
–Reverse the read data and write it to the temporary LOB.
buf2 := null;
for i in reverse 1..len loop
buf2 := buf2||substr(buf,i,1);
end loop;
–Write the whole lot in one go. Note, if this was a large
–amount of data then ideally it should be written using the
–available chunksize of the temporary LOB.
DBMS_LOB.WRITEAPPEND(t_lob,len,buf2);
–Now insert a new row into the table setting the CLOB column to
–the value of the temporary LOB. This can be done in one of
–two ways:
–(i) A new row can be inserted with an empty locator, the locator
— retrieved and the LOB value copied with DBMS_LOB.COPY.
–(ii) A new row can be inserted passing the temporary LOB locator
— as a bind variable to the insert.
—
–Using the second method:
insert into test_lobs values (5,t_lob,null,null) returning c2 into c_lob;
–Free the temporary LOB explicitly.
if DBMS_LOB.ISTEMPORARY(t_lob) = 1 then
DBMS_LOB.FREETEMPORARY(t_lob);
end if;
dbms_output.put_line(‘Length of CLOB inserted into record 5 is ‘||
DBMS_LOB.GETLENGTH(c_lob));
commit;
end;
/
—
–gives:
—
–Chunksize of column c2 is 1988
–Chunksize of temporary LOB is 1988
–Length of CLOB inserted into record 5 is 271
—
select c1, c2 from test_lobs
where c1 = 5
/
—
— C1 C2
———— ————————————————————
— 5
— .egakcap BOL_SMBD
— eht fo ytilanoitcnuf eht etartsnomed ot desu eb lliw
— dna txet fo senil 5 fo latot a sesirpmoc elif ehT .pmt/
— ni decalp si elif ehT ."txt.3cer" dellac elif a ni derots
— si atad ehT .nmuloc ELIFB s’3 drocer rof atad emos si sihT
— .3 drocer rof atad emoS
—
—
–Open and Close Operations
–~~~~~~~~~~~~~~~~~~~~~~~~~
–Under version 8.0 the only concept of opening and closing a LOB applies
–to BFILEs and the opening and closing of the physical O/S files they
–represent. **As of 8.1 it is possible to open and close
–any type of LOB. The new calls introduced for this functionality were
–DBMS_LOB.OPEN, DBMS_LOB.CLOSE and DBMS_LOB.ISOPEN. When the given
–locator is a BFILE, these three routines behave as DBMS_LOB.FILEOPEN,
–DBMS_LOB.FILECLOSE and DBMS_LOB.FILEISOPEN. When applied to internal
–LOBs they have the effect of batching up any writes such that triggers
–on an extensible index will not fire until the DBMS_LOB.CLOSE is called.
–When a LOB is opened it is with a mode of either read-only or read/write.
–Setting this mode to read-only, prevents any writes from being performed
–on the LOB in the current transaction until the LOB is closed. Note it
–is an error to attempt to open a BFILE for read/write. The concept of
–openness itself applies to a LOB rather than a locator, hence a LOB may
–only be opened once within a transaction and closed only when open.
–Attempting to do otherwise will result in an error.
—
declare
c_lob1 CLOB;
c_lob2 CLOB;
begin
–Select without locking the LOB.
select c2 into c_lob1 from test_lobs where c1 = 2;
c_lob2 := c_lob1;
–Open the LOB as read-only using locator 1.
DBMS_LOB.OPEN(c_lob1,DBMS_LOB.LOB_READONLY);
–Writes are not permitted. The following gives an error:
begin
DBMS_LOB.WRITEAPPEND(c_lob1,5,’Hello’);
exception
when others then
dbms_output.put_line(sqlerrm);
end;
–Commit and rollback are allowed because no transaction is started.
–The LOB will still be open afterwards.
rollback;
–Close – can use either locator.
if DBMS_LOB.ISOPEN(c_lob2) = 1 then
dbms_output.put_line(‘Closing LOB via locator 2’);
DBMS_LOB.CLOSE(c_lob2);
end if;
if DBMS_LOB.ISOPEN(c_lob1) = 1 then
dbms_output.put_line(‘Closing LOB via locator 1′);
DBMS_LOB.CLOSE(c_lob1);
end if;
–To open for read/write the record in the database must be locked.
select c2 into c_lob1 from test_lobs where c1 = 2 for update;
DBMS_LOB.OPEN(c_lob1,DBMS_LOB.LOB_READWRITE);
DBMS_LOB.WRITEAPPEND(c_lob1,5,’Hello’);
DBMS_LOB.WRITEAPPEND(c_lob1,7,’ there.’);
–The LOB MUST be closed before committing or rolling back.
DBMS_LOB.CLOSE(c_lob1);
commit;
end;
/
—
–gives:
—
–ORA-22294: cannot update a LOB opened in read-only mode
–Closing LOB via locator 2
—
select c2 from test_lobs where c1 = 2
/
—
–C2
————————————————————–
–Hello there.
—
. SQL-sys@PERS> select table_name , column_name from dba_lobs where segment_name like ‘%SYS_LOB0000006893C00007$$%’;
TABLE_NAME COLUMN_NAME
—————————————————————————————————-
P_SUR_VERS QUESTIONNAIRE
1 row selected.
SQL-sys@PERS> desc pers.p_sur_vers
Name Null? Type
—————————————————– ——– ———————————–
GROUPID NUMBER(38)
PROJECTID NUMBER(38)
VERSION NUMBER(38)
CHANGE NUMBER(38)
MODIFIED NUMBER(38)
MODIFIER NUMBER(38)
QUESTIONNAIRE NCLOB
select count(*) from dba_lobs where segment_name like ‘%SYS_LOB0000006893C00007$$%’;
SQL-sys@PERS> select sum(chunk) from dba_lobs where table_name like ‘%P_SUR_VERS%’;
SUM(CHUNK)
———-
8192 bytes
1 row selected.
SQL-sys@PERS> select sum(bytes) from dba_extents where segment_name like ‘P_SUR_VERS’;
SUM(BYTES)
———-
131072
1 row selected.
SQL-sys@PERSEUS> select sum(bytes) from dba_extents where tablespace_name = ‘TS_SURV’;
SUM(BYTES)
———-
105644032
1 row selected.
select segment_name , sum(bytes) from dba_extents where tablespace_name = ‘TS_SURV’group by segment_name order by sum(bytes)
PAYM5 2097152
SYS_LOB0000006889C00005$$ 2097152
SYS_LOB0000006919C00006$$ 4194304
SYS_LOB0000006893C00007$$
