Tom,

If the LOB tablespace is not backed up, can the table data (a different tablespace) be recovered in a failure scenario?

I know with TSPITR the process validates that no objects cross tablespaces that are not included in the set being recovered with the TSPITR check/validate funcion. This doesn't mean the tablespace won't be recovered in the auxiliary db, it just means the automated process won't continue through to export the objects, and re-import unless you pass the check. (or at least that was what happened in the test I ran).

I am just curious about what would happen to this table if its lob tablespace was lost and non-recoverable. can just the regular data be recovered

That was what I expected. The lobs are representative of data that was pulled from long term tape storage, loaded into the db, and served out of the db via the web.

The scalar data is the record of customer requests for data. A customer requests lots of data that is tarred up for the order. We are short on spinning disk, so I don't want to back up these lobs, but I want to be able to recover the order data and reload from tape the data in the event the order hadn't aged out or already been downloaded and there is a failure.

I'd be ok with the manual update to null before the manual export/import. I need to practice this scenario.

The message table is a static table.
"Alter table message modify (body CLOB) works well in 9iR2. But, in order to avoid 2-3 hours down time , I am going to adapt the following procedure as outlined in your book.

Message table has ~ 3.5 million rows and physical size is ~ 35G.
Create the table as follows for conversion:

create table message_1 (
SUBJECT VARCHAR2(255 BYTE) NOT NULL,
BODY CLOB,
CREATE_DATE DATE DEFAULT (SYSDATE) NOT NULL,
PERSONNEL_ID NUMBER(10) NOT NULL,
CLIENT_ID NUMBER(10) NOT NULL,
IS_OUTBOUND NUMBER(1) DEFAULT 1 NOT NULL,
MESSAGE_ID NUMBER(10) NOT NULL,
TYPE NUMBER(1) DEFAULT 5 NOT NULL,
IS_BOUNCED NUMBER(1) DEFAULT 0 NOT NULL,
CONTENT CLOB,
CUSTOMER_ID NUMBER(10) NOT NULL
)
TABLESPACE MESSAGE_TEST
NOLOGGING
LOB (BODY,content) STORE AS
( TABLESPACE MESSAGE_TEST
DISABLE STORAGE IN ROW
CHUNK 16384
PCTVERSION 10
NOCACHE
NOLOGGING
STORAGE (
INITIAL 1M
MINEXTENTS 1
MAXEXTENTS 2147483645
PCTINCREASE 0
BUFFER_POOL DEFAULT
)
);
Parallel insert method is used for conversion:

insert /*+ APPEND PARALLEL (MESSAGE_1,12) */ into MESSAGE_1
select /*+ PARALLEL (MESSAGE,12) */ SUBJECT,to_lob(BODY),CREATE_DATE,PERSONNEL_ID,CLIENT_ID,
IS_OUTBOUND,MESSAGE_ID,TYPE,IS_BOUNCED,CONTENT,CUSTOMER_ID
from MESSAGE;

I have the following questions:

1. What is ENABLE/DISABLE STORAGE IN clause for? Your book "expert .. page 1084" shows bulk insert as "DISABLE STORAGE IN ROWS". Any difference in selecting "DISABLE and/or ENABLE" during table creation?

2. After the insert is done do we have to convert MESSAGE_1 to logging and why?
"ALTER TABLE message_1 LOGGING";

3. The actual pysical size of the original table "message" is ~35G. After the conversion to CLOB physical size of the MESSAGE_1 grew to ~60G. Is this normal?? Or I am doing some thing wrong. Please advise
Thanks

Tom,

What would the NOCACHE CACHE READS clauses with a LOB be appropriate for?

We are trying to speed up an app that dumps data into a single table with a lob. Only 10% of the Lob is over 4K.
And we are seeing many Direct Path Write (lob) waits.

Every row that was inserted requires a lob read.
Could adding the CACHE clause help the insert without slowing down the update?

I ran a test!!

I inserted 10k rows
into a table lobtest - (lid number, data blob).

With data being 3900 bytes
it averaged 260 inserts/sec

With data beging 5000 bytes
it dropped way down to 50 inserts/sec

Could this just be Disk configuration or possibly the
buffer cache moving things out too fast?

IN regards to the previous post.

I created the same table twice.

The first like this:
create tablespace lobtest
datafile '/oracle/oradata/TEST/lobtest.dbf'
size 200m .....

The second like:
create tablespace lobtest
datafile '/oracle/oradata/TEST/lobtest.dbf'
size 50m .....

Then created the table like:
CREATE TABLE lobtest (lid number, data blob)
lob(data) store as (tablespace lobtest);

The 50m initial size insert performed 3x faster than the 200m initial size. How can this be?

I have the following little JAVA test App to test BLOB Inserts:

...
public static void main(String[] args) {
try
{

int lob1size = 4500;
int lob2size = 25000;
int iter = 3;
int sample = 1000;
long lid = 1010;
int commit = 50;
int count = 0;
showElapsed("Starting..");
DriverManager.registerDriver(new OracleDriver());
Connection conn = DriverManager.getConnection("jdbc:oracle:oci8:@perf","perf20hs","titan");
conn.setAutoCommit(false);
CallableStatement csmt1;
csmt1 = conn.prepareCall(trace);
csmt1.execute();
showElapsed("Connected..");
OracleCallableStatement ocs = (OracleCallableStatement)conn.prepareCall(sql);
ocs.registerOutParameter(1, OracleTypes.DOUBLE);
ocs.registerOutParameter(2, OracleTypes.BLOB);
showElapsed("Statement Prepared..");
byte[] data = new byte[lob1size];
Arrays.fill(data,(byte) (64+1));

for ( int i = 0;i<iter;i++)
{
for (int it = 0;it<sample;it++)
{
count++;
lid++;
ocs.setLong(1, lid);
ocs.executeUpdate();
BLOB blob = (BLOB) ocs.getBLOB(2);
OutputStream writer = blob.getBinaryOutputStream();
writer.write(data);
writer.flush();
writer.close();
if (count == commit)
{
//showElapsed("Commit.. -"+i);
conn.commit();
count=0;
}
}
showElapsed("Pass:"+i+";");
}

showElapsed("Finished..");
conn.close();
...

As you can see, the BLOB size is 4500 bytes.

I run the code for each tablespace below.

1st like:
CREATE TABLESPACE lobtest
DATAFILE '/history/oradata/PERF/lobtest'
SIZE 50m REUSE AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED
EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;
2nd like:
CREATE TABLESPACE lobtest
DATAFILE '/history/oradata/PERF/lobtest'
SIZE 200m REUSE AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED
EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;

The Table is create like:
create table lobtest (lid number, data blob)
lob(data) store as (tablespace lobtest);


The time for 50m tablespace:
22141
22863
21691

Time for 200m tablespace:
64141
62312
60122

Why would the inserts be 3x slower for the 200m tablespace.

The trace info is as follows:

200m Trace.

OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 3001 2.00 2.09 14 14 3153 3000
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3002 2.00 2.09 14 14 3153 3000

Misses in library cache during parse: 0

Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 9061 0.00 0.01
SQL*Net message from client 9061 0.16 6.73
SQL*Net more data from client 6000 0.00 0.17
direct path write (lob) 3000 0.01 0.35
log file switch completion 89 0.06 1.82
log file sync 63 0.08 1.67
log buffer space 6 0.08 0.47


OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 17 0.00 0.00 0 0 0 0
Execute 3016 1.02 1.05 13 36 3159 3009
Fetch 13 0.00 0.00 1 14 0 7
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3046 1.02 1.05 14 50 3159 3016

Misses in library cache during parse: 0

Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
db file sequential read 14 0.00 0.00
log file switch completion 12 0.03 0.24

3 user SQL statements in session.
16 internal SQL statements in session.
19 SQL statements in session.
********************************************************************************
Trace file: perf_ora_5926.trc
Trace file compatibility: 9.00.01
Sort options: default

1 session in tracefile.
3 user SQL statements in trace file.
16 internal SQL statements in trace file.
19 SQL statements in trace file.
7 unique SQL statements in trace file.
69813 lines in trace file.


Trace for 50m Tablespace:
OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 3001 2.07 2.16 12 34 3252 3000
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3002 2.07 2.16 12 34 3252 3000

Misses in library cache during parse: 1

Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 9061 0.00 0.01
SQL*Net message from client 9061 0.62 10.22
log file sync 63 0.33 2.24


OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 112 0.03 0.00 0 0 0 0
Execute 3113 1.02 1.07 11 206 3300 3057
Fetch 105 0.01 0.00 2 111 0 50
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3330 1.06 1.08 13 317 3300 3107

Misses in library cache during parse: 1

Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
db file sequential read 18 0.00 0.00
SQL*Net more data from client 6000 0.00 0.18
direct path write (lob) 3000 0.39 11.29
log file switch completion 103 0.42 2.50
log buffer space 6 0.08 0.47
latch free 1 0.01 0.01

3 user SQL statements in session.
111 internal SQL statements in session.
114 SQL statements in session.
********************************************************************************
Trace file: 50m.trc
Trace file compatibility: 9.00.01
Sort options: default

1 session in tracefile.
3 user SQL statements in trace file.
111 internal SQL statements in trace file.
114 SQL statements in trace file.
20 unique SQL statements in trace file.
72179 lines in trace file.

Tom, how to arrive at a uniform extent size (on initial/next) for the LOB tablespaces? I have the requirement for storing BLOB data ranging from 10K to 4GB. Now how do I decide on the uniform extent size for my tablespace?

Is it possible instead of pre-deciding on the extent size, can the extent size be determined based on the size of the blob being uploaded. ie. the blob is stored in a single extent (I completely subscribe to your points about single vs. multiple extents), but the blob will always be inserted/accessed as a whole, so doesnt it make a case for storing it in a single extent? Thanks.

Thanks Tom. But could you please explain the storage concept of BLOBs then? What are chunks - never heard of them, and how is it different from extents (extents are also a group of blocks, right)?

Also, can you please explain what kind of partitioning you were referring to? If possible with a small example.

Please dont mind, I have more questions after I received your answer. Not getting how the sytem extents can do better - may be after you explain the storage concept of blobs it will be clear for me.

The lob segments should be in seperate tablespace from LOB columns. If a table has multiple LOB columns should the LOB segments for each column be stored in different tablespaces or should they be stored in the same tablespace? What about LOB segments from LOB columns from different tables? What are the factors to consider for having same/different tablespaces for different LOB segemnts?

Thanks

I meant to ask can we put LOB segments from different LOB columns of the same or different table in the same tablespace (assigned for the LOB segment/s) or we have to assign different tablespaces for LOB segments from different columns ? What should be the factors we have to consider for this?

Thanks

How do we find out how many CHUNKS have been allocated to a BLOB column?

Questions #1: (see Question #1 tag below)
Why does show_space returns 56 total blocks when there are only 8 blocks in user_extents?

Question #2: (see Question #2 tag below)
Why does show_space shows 8 total blocks when there are 120 blocks (15 extents X 8 blocks) in user_extents?


SQL> CREATE TABLE t (
  2  id     INT PRIMARY KEY,
  3  in_row CLOB
  4  )
  5  LOB (in_row) STORE AS (ENABLE STORAGE IN ROW);

Table created.

SQL> 
SQL> INSERT INTO t
  2  SELECT ROWNUM, RPAD('x', 3000, 'x')
  3  FROM   all_objects
  4  WHERE  rownum < 100;

99 rows created.

SQL> commit;

Commit complete.

SQL> 
SQL> ANALYZE TABLE t COMPUTE STATISTICS;

Table analyzed.

SQL> 
SQL> SELECT num_rows, avg_space, chain_cnt, avg_row_len
  2  FROM   user_tables
  3  WHERE  table_name = 'T';

  NUM_ROWS  AVG_SPACE  CHAIN_CNT AVG_ROW_LEN
---------- ---------- ---------- -----------
        99       2037          0        3045

SQL> exec show_space('T');
Free Blocks.............................
Total Blocks............................56
Total Bytes.............................458752
Total MBytes............................0
Unused Blocks...........................0
Unused Bytes............................0
Last Used Ext FileId....................6
Last Used Ext BlockId...................9681
Last Used Block.........................8

PL/SQL procedure successfully completed.

SQL> col t_name for a15
SQL> col c_name for a15
SQL> col s_name for a25
SQL> 
SQL> SELECT table_name t_name, column_Name c_name, segment_Name s_name
  2  FROM   user_lobs
  3  WHERE  table_name IN ('T');

T_NAME          C_NAME          S_NAME
--------------- --------------- -------------------------
T               IN_ROW          SYS_LOB0000053797C00002$$

***************
Questions #1
***************

SQL> SELECT segment_Type, extent_id, bytes, blocks
  2  FROM   user_extents
  3  WHERE  segment_Name = 'SYS_LOB0000053797C00002$$'
  4  ORDER  BY extent_id;

SEGMENT_TYPE        EXTENT_ID      BYTES     BLOCKS
------------------ ---------- ---------- ----------
LOBSEGMENT                  0      65536          8

SQL> TRUNCATE TABLE t;

Table truncated.

SQL> INSERT INTO t
  2  SELECT ROWNUM, RPAD('x', 4000, 'x')
  3  FROM   all_objects
  4  WHERE  rownum < 100;

99 rows created.

SQL> commit;

Commit complete.

SQL> ANALYZE TABLE t COMPUTE STATISTICS;

Table analyzed.

SQL> 
SQL> SELECT num_rows, avg_space, chain_cnt, avg_row_len
  2  FROM   user_tables
  3  WHERE  table_name = 'T';

  NUM_ROWS  AVG_SPACE  CHAIN_CNT AVG_ROW_LEN
---------- ---------- ---------- -----------
        99       7103          0          47

SQL> exec show_space('T');
Free Blocks.............................
Total Blocks............................8
Total Bytes.............................65536
Total MBytes............................0
Unused Blocks...........................0
Unused Bytes............................0
Last Used Ext FileId....................6
Last Used Ext BlockId...................9609
Last Used Block.........................8

PL/SQL procedure successfully completed.

SQL> SELECT table_name t_name, column_Name c_name, segment_Name s_name
  2  FROM   user_lobs
  3  WHERE  table_name IN ('T');

T_NAME          C_NAME          S_NAME
--------------- --------------- -------------------------
T               IN_ROW          SYS_LOB0000053797C00002$$

***************
Questions #2
***************


SQL> SELECT segment_Type, extent_id, bytes, blocks
  2  FROM   user_extents
  3  WHERE  segment_Name = 'SYS_LOB0000053797C00002$$'
  4  ORDER  BY extent_id;

SEGMENT_TYPE        EXTENT_ID      BYTES     BLOCKS
------------------ ---------- ---------- ----------
LOBSEGMENT                  0      65536          8
LOBSEGMENT                  1      65536          8
LOBSEGMENT                  2      65536          8
LOBSEGMENT                  3      65536          8
LOBSEGMENT                  4      65536          8
LOBSEGMENT                  5      65536          8
LOBSEGMENT                  6      65536          8
LOBSEGMENT                  7      65536          8
LOBSEGMENT                  8      65536          8
LOBSEGMENT                  9      65536          8
LOBSEGMENT                 10      65536          8
LOBSEGMENT                 11      65536          8
LOBSEGMENT                 12      65536          8
LOBSEGMENT                 13      65536          8
LOBSEGMENT                 14      65536          8

15 rows selected.

SQL> spool off
 

Is it possible to have different BLOB storage parameters within each table partition?

I tried the following syntax but it did not work.

SQL> CREATE TABLE x (
  2  id            INT PRIMARY KEY,
  3  theBLOB       BLOB,
  4  BLOB_length   INT
  5  )
  6  PARTITION BY RANGE (BLOB_length)
  7  (
  8  PARTITION x_p1 VALUES LESS THAN (4000)
  9     TABLESPACE users STORAGE (INITIAL 8K NEXT 8K)
 10     LOB (theBLOB) STORE AS (
 11        TABLESPACE users ENABLE STORAGE IN ROW CHUNK 8192 CACHE
 12     )
 13  PARTITION x_p2 VALUES LESS THAN (8192)
 14     TABLESPACE users STORAGE (INITIAL 8K NEXT 8K)
 15     LOB (theBLOB) STORE AS (
 16        TABLESPACE users DISABLE STORAGE IN ROW CHUNK 8192 NOCACHE
 17     )
 18  )
 19  ENABLE ROW MOVEMENT;
PARTITION x_p2 VALUES LESS THAN (8192)
*
ERROR at line 13:
ORA-14020: this physical attribute may not be specified for a table partition

 

Hi Tom,

I have a question about inserting blobs into tables.
Previously, I used to do the following to insert blobs into tables.
create table imgtab (id number, image blob);

In order to insert data into this table from a procedure,

create or replace procedure insertimgproc
(
idinput number,
imagepar RAW
)
is
timgvar blob;

begin

.......
.......

insert into imgtab values (idinput, empty_blob() );
select image into timgvar from imgtab where id = idinput;
dbms_lob.write(timgvar,dbms_lob.getlength(timgvar),1,imagevar);

Why do we do this? And is there an improvement in 10g? Can we just do like

insert into imgtab values (idinput, imagevar);

.........

end;
/

Thanks for your suggestions.

what is imagevar - in plsql it could be a long raw upto 32k and have this work.


ops$tkyte@ORA9IR2> create table t ( id number, image blob );

Table created.

ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> create or replace procedure insertimgproc( p_id in number, p_img in long raw )
  2  as
  3  begin
  4          insert into t values ( p_id, p_img );
  5  end;
  6  /

Procedure created.

ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> declare
  2          l_data long raw := utl_raw.cast_to_raw( rpad( '0', 32123, '0' ) );
  3  begin
  4          insertimgproc( 1, l_data );
  5  end;
  6  /

PL/SQL procedure successfully completed.

ops$tkyte@ORA9IR2> select dbms_lob.getlength(image) from t;

DBMS_LOB.GETLENGTH(IMAGE)
-------------------------
                    32123

 

sorry imagevar is supposed to be changed to imagepar in my question.

Ok, that works in PL/SQL, can I do the same through a procedure with my input parameter as a long raw and insert that directly through an insert into stmt?

Thanks.

sorry i just assumed you meant SQL* Plus and posted the question. so what i do if the image is > 32K

Hi

Reading the documentation I read

"Best performance for LOBs can be achieved by specifying storage for LOBs in a tablespace different from the one used for the table that contains the LOB. If many different LOBs will be accessed frequently, it may also be useful to specify a separate tablespace for each LOB column or attribute in order to reduce device contention."

I dont understand why betetr performance can be achieved???

Tom,
I have a table with BLOB column. The size of images stored in BLOB column are typically 100k-200k. Question is, would this out of line data be read in a full table scan? My understanding is that when a BLOB size exceeds 4k, it will be stored out of line, which means that the BLOB column will only have the locator. In a full table scan scenario, Oracle will only read the locator and will skip the actual BLOB data. Is this correct?

Thanks

I was given an Oracle white paper titled LOB Performance Guidelines, May 2004. Under Enable/Disable Storage in Row, the Recommendation says:

============================
Use ENABLE STORAGE IN ROW, except in cases where the LOB data is not retrieved as much as other column's data. In this case, if the LOB data is stored out-of-line, the biggest gain is achieved while performing full table scans, as the operation does not retrieve the LOB's data.
============================
I interpreted this to imply that if I store LOB data out-of-line and do a full table scan, it will not read LOB data and will be faster. Is that correct?

Thanks

Hi Tom,

What is the syntax for modyfing the lob storage?

I have a partitioned table and I'd like to put the partition in buffer_pool KEEP and the LOB in buffer_pool DEFAULT.

ops$tkyte%ORA10GR2> CREATE TABLE t
  2  (
  3    dt  date,
  4    x   int,
  5    y   varchar2(30),
  6    z   clob
  7  )
  8  PARTITION BY RANGE (dt)
  9  (
 10    PARTITION part1 VALUES LESS THAN (to_date('13-mar-2003','dd-mon-yyyy')) ,
 11    PARTITION part2 VALUES LESS THAN (to_date('14-mar-2003','dd-mon-yyyy')) ,
 12    PARTITION junk VALUES LESS THAN (MAXVALUE)
 13  )
 14  /

Table created.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> select segment_name, buffer_pool from user_segments;

SEGMENT_NAME                   BUFFER_
------------------------------ -------
SYS_IL0000056176C00004$$       DEFAULT
SYS_IL0000056176C00004$$       DEFAULT
SYS_IL0000056176C00004$$       DEFAULT
SYS_LOB0000056176C00004$$      DEFAULT
SYS_LOB0000056176C00004$$      DEFAULT
SYS_LOB0000056176C00004$$      DEFAULT
T                              DEFAULT
T                              DEFAULT
T                              DEFAULT

9 rows selected.

ops$tkyte%ORA10GR2> alter table t storage (buffer_pool keep);

Table altered.

ops$tkyte%ORA10GR2> select segment_name, buffer_pool from user_segments;

SEGMENT_NAME                   BUFFER_
------------------------------ -------
SYS_IL0000056176C00004$$       DEFAULT
SYS_IL0000056176C00004$$       DEFAULT
SYS_IL0000056176C00004$$       DEFAULT
SYS_LOB0000056176C00004$$      DEFAULT
SYS_LOB0000056176C00004$$      DEFAULT
SYS_LOB0000056176C00004$$      DEFAULT
T                              KEEP
T                              KEEP
T                              KEEP

9 rows selected.

ops$tkyte%ORA10GR2> alter table t modify lob(z) (storage (buffer_pool recycle));

Table altered.

ops$tkyte%ORA10GR2> select segment_name, buffer_pool from user_segments;

SEGMENT_NAME                   BUFFER_
------------------------------ -------
SYS_IL0000056176C00004$$       RECYCLE
SYS_IL0000056176C00004$$       RECYCLE
SYS_IL0000056176C00004$$       RECYCLE
SYS_LOB0000056176C00004$$      RECYCLE
SYS_LOB0000056176C00004$$      RECYCLE
SYS_LOB0000056176C00004$$      RECYCLE
T                              KEEP
T                              KEEP
T                              KEEP

9 rows selected.

 

Hi Tom,
From Metalink note 66431.1

"NOLOGGING of the LOB segment means that if you have to recover the database then sections of the LOB segment will be marked as corrupt during recovery."

Can you please explain this? Is having nologging for certains objects or tablespaces generally a problem for the DBAs when they do backup/recovery?

I would like to use nologging for lobs with a view to reducing the amount of redo log generated, but the word "corrupt" is discouraging me. Our app may be installed in our customer's highly active telecom DB and I'm concerned that their DBA may not approve of certain objects being nologging.

Thanks in advance.

Tom,

I have a vendor who has a table structure similiar to the following:

MSG table
id number,
owner varchar,
..
..
)

MSGB table
msg_Id number,
text blob

I ask him why they store the blob in a seperate table. His responce was:
it's because they can be
very large, to avoid having big transactions moving around when all one wants to do is look at fields inside the MSG table.

Curious to your thoughts on this!!

Hi Tom

I work with Oracle9i Enterprise Edition Release 9.2.0.7.0
And I have one table photo_sample ( cod_sample number(5)
, photo blob );
And I try to input some pictures by forms 6i on the button I input this code here :
--
declare
--
photo_filename VARCHAR2(254);
--
begin
--
:System.Message_Level := '25';
--
photo_filename := Get_file_name( null, null, 'Image File (*.GIF; *.JPG)|*.GIF; *.JPG|', null, open_file, true );
--
read_image_file(photo_filename, 'JFIF', 'photo_sample.photo');
--
if not form_success then
msg_alert('format of the invalid image','E',false);
end if;
--
:system.message_level := 0;
--
end;

But some pictures and don't have any problem, and another he don't accept. Look, the pictures he don't accept, come from digital camera ( Sony DSC S75 ). than if I open this picture on any program and salve as.. no need to change nothing, only save the picture than I can come back to the form and input her, will work well.

Why some pictures work and another's don't ?

PS: the pictures don't work have about 60Kb until 100KB

Is there anyway to change a LOB column from "ENABLE STORAGE IN ROW" to "DISABLE STORAGE IN ROW" without re-creating the table?

If a lob is stored in another segment in another tablespace, is the lob deleted from its tablespace when
its "parent" record is deleted in separate tablespace.

How can this be shown using SQL ?

what is the best practice to store images. If the images are stored on the same server as the database is, when oracle uses the pointer to retrieve the image, is the image loaded into buffer cache or in pga? Thanks.

Why are there so many products out there that still store files in the File System ?? Even Business Objects, which stored everything in Oracle, has taken a step backward with its latest version (thanks to its "integration" of Crystal Reports).

Now that we have paid a fortune for these products:
-- we have to stop all applications in order to back up (no open pointers to those files...).
-- high availability? Not available.
-- lose a disk? Have to restore from the last backup and LOSE any updates since then, since the database and the files have to "be in sync".
-- where's the transactional integrity?

If you don't store ALL your data in the database, why have one?

great thread here ... we're also storing everything in our database using intermedia and BLOB/CLOB . . . it's the only way to fly!

Now facing a dilemma about whether using ASM with LOBs still requires separate talespaces; i.e., is there any benefit to putting all my disks in one large ASM diskgroup +DATA and just create 3 tablespaces in +DATA (data/index/lob tablespaces), or make 3 diskgroups +DATA +IDX +LOB and then make tablespaces for data/index/lobs in each separate disk group, respectively? I think the difference will be that the one large +DATA diskgroup can gain from striping over all 25 disks, whereas creating 3 diskgroups +DATA +IDX +LOB would require a smaller set of disks in each group and maybe not benefit as much from the striping.

Any comments or experience from anyone would be welcome

Thanks,LJ

Dear Tom,

I am store picture in my data base(8.1.7) through forms 6i by trigger :

(declare
v_dirname varchar2(255);
v_filename varchar2(255);
begin
v_dirname := 'D:\PICTURE';
v_filename := get_file_name(v_dirname,null,'JPEG Files (*.jpg)|*.jpg|' ||'Bitmap files (*.bmp)|*.bmp|' || 'GIF Files (*.gif)|*.gif|');
if v_filename is not null then
read_image_file(v_filename, 'ANY','EMPINFO.PICT');
null;
end if;
end;)

and my structure is

IDCARD VARCHAR2(30)
NAME_BANGLA VARCHAR2(30)
PICT BLOB

and I am storeing jpg file as picture. But my Dmp file size increase Un-expectly. For test purpose i have store 7 jpg file total size is 229KB. and i have take back of the DMP and comapre with prevous one which I have taken just before the store the picture and found DMP size as below :

Previous - 10936 KB
Current - 12062 KB

It increase the DMP size 5 times more than the original picture size, Please advice how can i stop increasing DMP unexpectly.

Dear Tom,

Boss can write 2/3 lines on above, That will be very help full for us.

DEAR TOM,

Sorry for late respose..............

PLEASE SEE THE BELOW IS STATUS.

SQL> select CARDNO,dbms_lob.getlength(PICT) from empinfo where PICT is not null;

CARDNO    DBMS_LOB.GETLENGTH(PICT)   IMAGE SIZE  TYPE
-------   ------------------------   ----------  ----
10001                       308785     30 KB     JPG
10002                       203125      9 KB     JPG
10003                       203125     17 KB     JPG 
10004                        85725      6 KB     JPG
10005                       132685    157 KB     JPG
10006                       132685      9 KB     JPG
10007                        85725      5 KB     JPG  

7 rows selected.

I AM USEING IMAGE FORMATE AS "BMP" OUT OF CALS/GIG/JFIF/PIVT/RAS/TIFF/TPIC FROM PROPERTY PALETTE OF THE COLUMN PICT. I WANT TO USE MINIMAM SPACE FOR PICTURE IN DATABASE. PLEASE ADVISE ME THE SOLUTIONS FOR THIS

dear tom,

Waiting for your response,,

Dear Tom,

After search a long in Discussion forum of forms, Did not fine any solution to store picture as blob, Are any way which i can apply to store the picture.

THANKS

I was surprised to find that after LOBs are removed from LOB segments, the space cannot be reused! Please see my example below. What's the reason that Oracle chooses not to free up space? What's your recommendation for a system that has gone through a massive delete and would like to be able to reuse the "free space?" Should the space be reclaimed by re-organizing the LOB segment online/offline?

==================================================================================================

CREATE TABLE LOB_Table (
   id       NUMBER PRIMARY KEY,
   LOB_Col  CLOB
);

SELECT segment_name, bytes/1024/1024 MB
FROM   user_segments
WHERE  segment_name IN ('LOB_TABLE');

SEGMENT_NAME                      MB
------------------------- ----------
LOB_TABLE                      .0625

col table_name for a20
col column_name for a25
col tablespace_name for a15

SELECT a.table_name, a.column_name, a.tablespace_name, a.segment_name, b.bytes/1024/1024 MB
FROM   user_lobs a, user_segments b
WHERE  a.segment_name = b.segment_name AND
       a.table_name IN ('LOB_TABLE')
ORDER  BY b.bytes;

TABLE_NAME           COLUMN_NAME               TABLESPACE_NAME SEGMENT_NAME                      MB
-------------------- ------------------------- --------------- ------------------------- ----------
LOB_TABLE            LOB_COL                   USERS           SYS_LOB0000057518C00002$$      .0625


set serveroutput on

-- -------------------------------------------------------------------------------------------
-- Inserting 10,000 records of 5000 bytes each. Records > 4K will go into the LOB segment.
-- -------------------------------------------------------------------------------------------
DECLARE
   l_LOB_Col CLOB := RPAD('*', 5000, '*');

BEGIN
   INSERT INTO LOB_Table
   SELECT rownum, l_LOB_Col
   FROM   dual
   CONNECT BY level <= 10000;
END;
/

commit;


SELECT segment_name, bytes/1024/1024 MB
FROM   user_segments
WHERE  segment_name IN ('LOB_TABLE');

SEGMENT_NAME                      MB
------------------------- ----------
LOB_TABLE                      .6875

-- -------------------------------------------------------------------------------------------
-- LOB segment increased to 160 MB.
-- -------------------------------------------------------------------------------------------
SELECT a.table_name, a.column_name, a.tablespace_name, a.segment_name, b.bytes/1024/1024 MB
FROM   user_lobs a, user_segments b
WHERE  a.segment_name = b.segment_name AND
       a.table_name IN ('LOB_TABLE')
ORDER  BY b.bytes;

TABLE_NAME           COLUMN_NAME               TABLESPACE_NAME SEGMENT_NAME                      MB
-------------------- ------------------------- --------------- ------------------------- ----------
LOB_TABLE            LOB_COL                   USERS           SYS_LOB0000057518C00002$$        160


-- -------------------------------------------------------------------------------------------
-- Now delete all the records from the table. As expected, space did not get deallocated. 
-- However, I expect 160 MB of free space to be used by future inserts.
-- -------------------------------------------------------------------------------------------
DELETE FROM LOB_Table;
COMMIT;


SELECT a.table_name, a.column_name, a.tablespace_name, a.segment_name, b.bytes/1024/1024 MB
FROM   user_lobs a, user_segments b
WHERE  a.segment_name = b.segment_name AND
       a.table_name IN ('LOB_TABLE')
ORDER  BY b.bytes;

TABLE_NAME           COLUMN_NAME               TABLESPACE_NAME SEGMENT_NAME                      MB
-------------------- ------------------------- --------------- ------------------------- ----------
LOB_TABLE            LOB_COL                   USERS           SYS_LOB0000057518C00002$$        160


-- ==========================================================================================
-- ==========================================================================================
-- Now here's the big surprise! 
-- 
-- The deleted space did not get re-used - the LOB segment grew to 320 MB!
-- ==========================================================================================
-- ==========================================================================================

DECLARE
   l_LOB_Col CLOB := RPAD('*', 5000, '*');

BEGIN
   INSERT INTO LOB_Table
   SELECT rownum, l_LOB_Col
   FROM   dual
   CONNECT BY level <= 10000;
END;
/

commit;

SELECT a.table_name, a.column_name, a.tablespace_name, a.segment_name, b.bytes/1024/1024 MB
FROM   user_lobs a, user_segments b
WHERE  a.segment_name = b.segment_name AND
       a.table_name IN ('LOB_TABLE')
ORDER  BY b.bytes;

TABLE_NAME           COLUMN_NAME               TABLESPACE_NAME SEGMENT_NAME                      MB
-------------------- ------------------------- --------------- ------------------------- ----------
LOB_TABLE            LOB_COL                   USERS           SYS_LOB0000057518C00002$$        320

ops$tkyte%ORA10GR2> CREATE TABLE LOB_Table (
  2     id       NUMBER PRIMARY KEY,
  3     LOB_Col  CLOB
  4  );

Table created.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> SELECT segment_name, bytes/1024/1024 MB
  2  FROM   user_segments
  3  WHERE  segment_name IN ('LOB_TABLE');

SEGMENT_NAME                           MB
------------------------------ ----------
LOB_TABLE                           .0625

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> SEGMENT_NAME                      MB
SP2-0734: unknown command beginning "SEGMENT_NA..." - rest of line ignored.
ops$tkyte%ORA10GR2> ------------------------- ----------
ops$tkyte%ORA10GR2> LOB_TABLE                      .0625
SP2-0734: unknown command beginning "LOB_TABLE ..." - rest of line ignored.
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> SELECT a.table_name, a.column_name, a.tablespace_name, a.segment_name,
  2  b.bytes/1024/1024 MB
  3  FROM   user_lobs a, user_segments b
  4  WHERE  a.segment_name = b.segment_name AND
  5         a.table_name IN ('LOB_TABLE')
  6  ORDER  BY b.bytes;

TABLE_NAME
------------------------------
COLUMN_NAME
-------------------------------------------------------------------------------
TABLESPACE_NAME                SEGMENT_NAME                           MB
------------------------------ ------------------------------ ----------
LOB_TABLE
LOB_COL
USERS                          SYS_LOB0000162918C00002$$           .0625


ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> DECLARE
  2     l_LOB_Col CLOB := RPAD('*', 5000, '*');
  3  BEGIN
  4     INSERT INTO LOB_Table
  5     SELECT rownum, l_LOB_Col
  6     FROM   dual
  7     CONNECT BY level <= 10000;
  8  END;
  9  /

PL/SQL procedure successfully completed.

ops$tkyte%ORA10GR2> commit;

Commit complete.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> SELECT segment_name, bytes/1024/1024 MB
  2  FROM   user_segments
  3  WHERE  segment_name IN ('LOB_TABLE');

SEGMENT_NAME                           MB
------------------------------ ----------
LOB_TABLE                            .625

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> SELECT a.table_name, a.column_name, a.tablespace_name, a.segment_name, b.bytes/1024/1024 MB
  2  FROM   user_lobs a, user_segments b
  3  WHERE  a.segment_name = b.segment_name AND
  4         a.table_name IN ('LOB_TABLE')
  5  ORDER  BY b.bytes;

TABLE_NAME
------------------------------
COLUMN_NAME
-------------------------------------------------------------------------------
TABLESPACE_NAME                SEGMENT_NAME                           MB
------------------------------ ------------------------------ ----------
LOB_TABLE
LOB_COL
USERS                          SYS_LOB0000162918C00002$$              80


ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> DELETE FROM LOB_Table;

10000 rows deleted.

ops$tkyte%ORA10GR2> COMMIT;

Commit complete.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> SELECT a.table_name, a.column_name, a.tablespace_name, a.segment_name, b.bytes/1024/1024 MB
  2  FROM   user_lobs a, user_segments b
  3  WHERE  a.segment_name = b.segment_name AND
  4         a.table_name IN ('LOB_TABLE')
  5  ORDER  BY b.bytes;

TABLE_NAME
------------------------------
COLUMN_NAME
-------------------------------------------------------------------------------
TABLESPACE_NAME                SEGMENT_NAME                           MB
------------------------------ ------------------------------ ----------
LOB_TABLE
LOB_COL
USERS                          SYS_LOB0000162918C00002$$              80


ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> DECLARE
  2     l_LOB_Col CLOB := RPAD('*', 5000, '*');
  3
  4  BEGIN
  5     INSERT INTO LOB_Table
  6     SELECT rownum, l_LOB_Col
  7     FROM   dual
  8     CONNECT BY level <= 10000;
  9  END;
 10  /

PL/SQL procedure successfully completed.

ops$tkyte%ORA10GR2> commit;

Commit complete.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> SELECT a.table_name, a.column_name, a.tablespace_name, a.segment_name, b.bytes/1024/1024 MB
  2  FROM   user_lobs a, user_segments b
  3  WHERE  a.segment_name = b.segment_name AND
  4         a.table_name IN ('LOB_TABLE')
  5  ORDER  BY b.bytes;

TABLE_NAME
------------------------------
COLUMN_NAME
-------------------------------------------------------------------------------
TABLESPACE_NAME                SEGMENT_NAME                           MB
------------------------------ ------------------------------ ----------
LOB_TABLE
LOB_COL
USERS                          SYS_LOB0000162918C00002$$              80

I tested using 2 different kinds of tablespaces with the same result.

Metalink note 386341.1 seems to say the same thing about Oracle not being able to reuse deleted space in LOB segments.

SQL> 
SQL> SELECT * FROM v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - Prod
PL/SQL Release 10.2.0.2.0 - Production
CORE 10.2.0.2.0 Production
TNS for 32-bit Windows: Version 10.2.0.2.0 - Production
NLSRTL Version 10.2.0.2.0 - Production

SQL> 
SQL> -- ----------------------------------------------------------------------------------
SQL> -- Use regular tablespace
SQL> -- ----------------------------------------------------------------------------------
SQL> CREATE TABLESPACE lob_tbs
  2  DATAFILE 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\PROTECT\lob_tbs.dbf' SIZE 200M
  3  AUTOEXTEND ON NEXT 50M
  4  EXTENT MANAGEMENT LOCAL AUTOALLOCATE
  5  SEGMENT SPACE MANAGEMENT AUTO;

Tablespace created.

SQL> 
SQL> CREATE TABLE LOB_Table (
  2   id  NUMBER PRIMARY KEY,
  3   LOB_Col  CLOB
  4  )
  5  TABLESPACE lob_tbs
  6  LOB (LOB_Col) STORE AS (TABLESPACE lob_tbs ENABLE STORAGE IN ROW);

Table created.

SQL> 
SQL> 
SQL> SELECT a.table_name, a.column_name, a.tablespace_name, a.segment_name, b.bytes/1024/1024 MB
  2  FROM   user_lobs a, user_segments b
  3  WHERE  a.segment_name = b.segment_name AND
  4       a.table_name IN ('LOB_TABLE')
  5  ORDER  BY b.bytes;

TABLE_NAME
------------------------------
COLUMN_NAME
-----------------------------------------------------------------------------------------------------------------------------------
TABLESPACE_NAME                SEGMENT_NAME                           MB
------------------------------ ------------------------------ ----------
LOB_TABLE
LOB_COL
LOB_TBS                        SYS_LOB0000057554C00002$$           .0625


SQL> 
SQL> 
SQL> DECLARE
  2   l_LOB_Col CLOB := RPAD('*', 5000, '*');
  3  
  4  BEGIN
  5   INSERT INTO LOB_Table
  6   SELECT rownum, l_LOB_Col
  7   FROM   dual
  8   CONNECT BY level <= 10000;
  9  END;
 10  /

PL/SQL procedure successfully completed.

SQL> 
SQL> commit;

Commit complete.

SQL> 
SQL> SELECT a.table_name, a.column_name, a.tablespace_name, a.segment_name, b.bytes/1024/1024 MB
  2  FROM   user_lobs a, user_segments b
  3  WHERE  a.segment_name = b.segment_name AND
  4       a.table_name IN ('LOB_TABLE')
  5  ORDER  BY b.bytes;

TABLE_NAME
------------------------------
COLUMN_NAME
-----------------------------------------------------------------------------------------------------------------------------------
TABLESPACE_NAME                SEGMENT_NAME                           MB
------------------------------ ------------------------------ ----------
LOB_TABLE
LOB_COL
LOB_TBS                        SYS_LOB0000057554C00002$$             160


SQL> 
SQL> 
SQL> DELETE FROM LOB_Table;

10000 rows deleted.

SQL> COMMIT;

Commit complete.

SQL> 
SQL> 
SQL> SELECT a.table_name, a.column_name, a.tablespace_name, a.segment_name, b.bytes/1024/1024 MB
  2  FROM   user_lobs a, user_segments b
  3  WHERE  a.segment_name = b.segment_name AND
  4       a.table_name IN ('LOB_TABLE')
  5  ORDER  BY b.bytes;

TABLE_NAME
------------------------------
COLUMN_NAME
-----------------------------------------------------------------------------------------------------------------------------------
TABLESPACE_NAME                SEGMENT_NAME                           MB
------------------------------ ------------------------------ ----------
LOB_TABLE
LOB_COL
LOB_TBS                        SYS_LOB0000057554C00002$$             160


SQL> 
SQL> DECLARE
  2   l_LOB_Col CLOB := RPAD('*', 5000, '*');
  3  
  4  BEGIN
  5   INSERT INTO LOB_Table
  6   SELECT rownum, l_LOB_Col
  7   FROM   dual
  8   CONNECT BY level <= 10000;
  9  END;
 10  /

PL/SQL procedure successfully completed.

SQL> 
SQL> commit;

Commit complete.

SQL> 
SQL> SELECT a.table_name, a.column_name, a.tablespace_name, a.segment_name, b.bytes/1024/1024 MB
  2  FROM   user_lobs a, user_segments b
  3  WHERE  a.segment_name = b.segment_name AND
  4       a.table_name IN ('LOB_TABLE')
  5  ORDER  BY b.bytes;

TABLE_NAME
------------------------------
COLUMN_NAME
-----------------------------------------------------------------------------------------------------------------------------------
TABLESPACE_NAME                SEGMENT_NAME                           MB
------------------------------ ------------------------------ ----------
LOB_TABLE
LOB_COL
LOB_TBS                        SYS_LOB0000057554C00002$$             319


SQL> 
SQL> 
SQL> -- ----------------------------------------------------------------------------------
SQL> -- Use Oracle Managed File
SQL> -- ----------------------------------------------------------------------------------
SQL> DROP TABLE LOB_Table;

Table dropped.

SQL> DROP TABLESPACE lob_tbs;

Tablespace dropped.

SQL> 
SQL> CREATE TABLESPACE lob_tbs DATAFILE
  2  AUTOEXTEND ON NEXT 200M MAXSIZE 2048M;

Tablespace created.

SQL> 
SQL> CREATE TABLE LOB_Table (
  2   id  NUMBER PRIMARY KEY,
  3   LOB_Col  CLOB
  4  )
  5  TABLESPACE lob_tbs
  6  LOB (LOB_Col) STORE AS (TABLESPACE lob_tbs ENABLE STORAGE IN ROW);

Table created.

SQL> 
SQL> 
SQL> SELECT a.table_name, a.column_name, a.tablespace_name, a.segment_name, b.bytes/1024/1024 MB
  2  FROM   user_lobs a, user_segments b
  3  WHERE  a.segment_name = b.segment_name AND
  4       a.table_name IN ('LOB_TABLE')
  5  ORDER  BY b.bytes;

TABLE_NAME
------------------------------
COLUMN_NAME
-----------------------------------------------------------------------------------------------------------------------------------
TABLESPACE_NAME                SEGMENT_NAME                           MB
------------------------------ ------------------------------ ----------
LOB_TABLE
LOB_COL
LOB_TBS                        SYS_LOB0000057558C00002$$           .0625


SQL> 
SQL> 
SQL> DECLARE
  2   l_LOB_Col CLOB := RPAD('*', 5000, '*');
  3  
  4  BEGIN
  5   INSERT INTO LOB_Table
  6   SELECT rownum, l_LOB_Col
  7   FROM   dual
  8   CONNECT BY level <= 10000;
  9  END;
 10  /

PL/SQL procedure successfully completed.

SQL> 
SQL> commit;

Commit complete.

SQL> 
SQL> SELECT a.table_name, a.column_name, a.tablespace_name, a.segment_name, b.bytes/1024/1024 MB
  2  FROM   user_lobs a, user_segments b
  3  WHERE  a.segment_name = b.segment_name AND
  4       a.table_name IN ('LOB_TABLE')
  5  ORDER  BY b.bytes;

TABLE_NAME
------------------------------
COLUMN_NAME
-----------------------------------------------------------------------------------------------------------------------------------
TABLESPACE_NAME                SEGMENT_NAME                           MB
------------------------------ ------------------------------ ----------
LOB_TABLE
LOB_COL
LOB_TBS                        SYS_LOB0000057558C00002$$             160


SQL> 
SQL> 
SQL> DELETE FROM LOB_Table;

10000 rows deleted.

SQL> COMMIT;

Commit complete.

SQL> 
SQL> 
SQL> SELECT a.table_name, a.column_name, a.tablespace_name, a.segment_name, b.bytes/1024/1024 MB
  2  FROM   user_lobs a, user_segments b
  3  WHERE  a.segment_name = b.segment_name AND
  4       a.table_name IN ('LOB_TABLE')
  5  ORDER  BY b.bytes;

TABLE_NAME
------------------------------
COLUMN_NAME
-----------------------------------------------------------------------------------------------------------------------------------
TABLESPACE_NAME                SEGMENT_NAME                           MB
------------------------------ ------------------------------ ----------
LOB_TABLE
LOB_COL
LOB_TBS                        SYS_LOB0000057558C00002$$             160


SQL> 
SQL> DECLARE
  2   l_LOB_Col CLOB := RPAD('*', 5000, '*');
  3  
  4  BEGIN
  5   INSERT INTO LOB_Table
  6   SELECT rownum, l_LOB_Col
  7   FROM   dual
  8   CONNECT BY level <= 10000;
  9  END;
 10  /

PL/SQL procedure successfully completed.

SQL> 
SQL> commit;

Commit complete.

SQL> 
SQL> SELECT a.table_name, a.column_name, a.tablespace_name, a.segment_name, b.bytes/1024/1024 MB
  2  FROM   user_lobs a, user_segments b
  3  WHERE  a.segment_name = b.segment_name AND
  4       a.table_name IN ('LOB_TABLE')
  5  ORDER  BY b.bytes;

TABLE_NAME
------------------------------
COLUMN_NAME
-----------------------------------------------------------------------------------------------------------------------------------
TABLESPACE_NAME                SEGMENT_NAME                           MB
------------------------------ ------------------------------ ----------
LOB_TABLE
LOB_COL
LOB_TBS                        SYS_LOB0000057558C00002$$             320


SQL> 
SQL> spool off

Tested on 11g with the same behavior.

SQL> 
SQL> SELECT * FROM v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
PL/SQL Release 11.1.0.6.0 - Production
CORE 11.1.0.6.0 Production
TNS for 32-bit Windows: Version 11.1.0.6.0 - Production
NLSRTL Version 11.1.0.6.0 - Production

SQL> CREATE TABLESPACE lob_tbs
  2  DATAFILE 'C:\ORACLE\ORADATA\ORCL\lob_tbs.dbf' SIZE 200M
  3  AUTOEXTEND ON NEXT 50M
  4  EXTENT MANAGEMENT LOCAL AUTOALLOCATE
  5  SEGMENT SPACE MANAGEMENT AUTO;

Tablespace created.

SQL> 
SQL> CREATE TABLE LOB_Table (
  2   id  NUMBER PRIMARY KEY,
  3   LOB_Col  CLOB
  4  )
  5  TABLESPACE lob_tbs
  6  LOB (LOB_Col) STORE AS (TABLESPACE lob_tbs ENABLE STORAGE IN ROW);

Table created.

SQL> 
SQL> 
SQL> SELECT a.table_name, a.column_name, a.tablespace_name, a.segment_name, b.bytes/1024/1024 MB
  2  FROM   user_lobs a, user_segments b
  3  WHERE  a.segment_name = b.segment_name AND
  4       a.table_name IN ('LOB_TABLE')
  5  ORDER  BY b.bytes;

TABLE_NAME
------------------------------
COLUMN_NAME
-----------------------------------------------------------------------------------------------------------------------------------
TABLESPACE_NAME                SEGMENT_NAME                           MB
------------------------------ ------------------------------ ----------
LOB_TABLE
LOB_COL
LOB_TBS                        SYS_LOB0000071422C00002$$           .0625


SQL> 
SQL> 
SQL> DECLARE
  2   l_LOB_Col CLOB := RPAD('*', 5000, '*');
  3  
  4  BEGIN
  5   INSERT INTO LOB_Table
  6   SELECT rownum, l_LOB_Col
  7   FROM   dual
  8   CONNECT BY level <= 10000;
  9  END;
 10  /

PL/SQL procedure successfully completed.

SQL> 
SQL> commit;

Commit complete.

SQL> 
SQL> SELECT a.table_name, a.column_name, a.tablespace_name, a.segment_name, b.bytes/1024/1024 MB
  2  FROM   user_lobs a, user_segments b
  3  WHERE  a.segment_name = b.segment_name AND
  4       a.table_name IN ('LOB_TABLE')
  5  ORDER  BY b.bytes;

TABLE_NAME
------------------------------
COLUMN_NAME
-----------------------------------------------------------------------------------------------------------------------------------
TABLESPACE_NAME                SEGMENT_NAME                           MB
------------------------------ ------------------------------ ----------
LOB_TABLE
LOB_COL
LOB_TBS                        SYS_LOB0000071422C00002$$              80


SQL> 
SQL> 
SQL> DELETE FROM LOB_Table;

10000 rows deleted.

SQL> COMMIT;

Commit complete.

SQL> 
SQL> 
SQL> SELECT a.table_name, a.column_name, a.tablespace_name, a.segment_name, b.bytes/1024/1024 MB
  2  FROM   user_lobs a, user_segments b
  3  WHERE  a.segment_name = b.segment_name AND
  4       a.table_name IN ('LOB_TABLE')
  5  ORDER  BY b.bytes;

TABLE_NAME
------------------------------
COLUMN_NAME
-----------------------------------------------------------------------------------------------------------------------------------
TABLESPACE_NAME                SEGMENT_NAME                           MB
------------------------------ ------------------------------ ----------
LOB_TABLE
LOB_COL
LOB_TBS                        SYS_LOB0000071422C00002$$              80


SQL> 
SQL> DECLARE
  2   l_LOB_Col CLOB := RPAD('*', 5000, '*');
  3  
  4  BEGIN
  5   INSERT INTO LOB_Table
  6   SELECT rownum, l_LOB_Col
  7   FROM   dual
  8   CONNECT BY level <= 10000;
  9  END;
 10  /

PL/SQL procedure successfully completed.

SQL> 
SQL> commit;

Commit complete.

SQL> 
SQL> SELECT a.table_name, a.column_name, a.tablespace_name, a.segment_name, b.bytes/1024/1024 MB
  2  FROM   user_lobs a, user_segments b
  3  WHERE  a.segment_name = b.segment_name AND
  4       a.table_name IN ('LOB_TABLE')
  5  ORDER  BY b.bytes;

TABLE_NAME
------------------------------
COLUMN_NAME
-----------------------------------------------------------------------------------------------------------------------------------
TABLESPACE_NAME                SEGMENT_NAME                           MB
------------------------------ ------------------------------ ----------
LOB_TABLE
LOB_COL
LOB_TBS                        SYS_LOB0000071422C00002$$             160

Wow! You're good!

So if we want to use the automatic undo management and retain the ability to reuse the deleted space immediately, we should set the PCTVERSION for the lob?

In your book, you mentioned that "PCTVERSION controls the percentage of allocated LOB space that should be used for versioning of LOB data." Does this mean that if PCTVERSION is set to 0 there will be no versioning; thus, no UNDO?!

I ran a quick test to compare the storage usage of LOB online vs offline storage. It's surprising to me that they're stored pretty much the same way based on their sizes in the main tables as well as the LOB segments. Am I missing something here?

SQL> CREATE TABLE inrow (
  2     id       NUMBER PRIMARY KEY,
  3     LOB_Col  CLOB
  4  )
  5  LOB (LOB_Col) STORE AS (ENABLE STORAGE IN ROW);

Table created.

SQL> 
SQL> CREATE TABLE outrow (
  2     id       NUMBER PRIMARY KEY,
  3     LOB_Col  CLOB
  4  )
  5  LOB (LOB_Col) STORE AS (DISABLE STORAGE IN ROW);

Table created.

SQL> 
SQL> 
SQL> INSERT INTO inrow
  2  SELECT rownum, RPAD('x', 2000, 'x')
  3  FROM   dual
  4  CONNECT BY level <= 100000;

100000 rows created.

SQL> 
SQL> INSERT INTO outrow
  2  SELECT rownum, RPAD('x', 2000, 'x')
  3  FROM   dual
  4  CONNECT BY level <= 100000;

100000 rows created.

SQL> 
SQL> commit;

Commit complete.

SQL> 
SQL> SELECT table_Name, column_name, in_row
  2  FROM   user_lobs
  3  WHERE  table_name IN ('INROW', 'OUTROW');

TABLE_NAME                     COLUMN_NAME          IN_
------------------------------ -------------------- ---
INROW                          LOB_COL              YES
OUTROW                         LOB_COL              NO

SQL> 
SQL> 
SQL> col segment_name for a20
SQL> col column_name for a20
SQL> 
SQL> SELECT segment_Name, bytes/1024/1024 MB
  2  FROM   user_segments
  3  WHERE  segment_name IN ('INROW', 'OUTROW');

SEGMENT_NAME                 MB
-------------------- ----------
INROW                         6
OUTROW                        4

SQL> 
SQL> SELECT a.table_name, a.column_name, b.bytes/1024/1024 MB
  2  FROM   user_lobs a, user_segments b
  3  WHERE  a.segment_name = b.segment_name AND
  4         a.table_name IN ('INROW', 'OUTROW')
  5  ORDER  BY b.bytes;

TABLE_NAME                     COLUMN_NAME                  MB
------------------------------ -------------------- ----------
OUTROW                         LOB_COL                     788
INROW                          LOB_COL                     791

inrow table  - 264mb    lob segment - 0.1mb
outrow table -   4mb    lob segment - 792mb

Tom:

When we create a table in 9i with CLOB columns we never specify anything on how it is to be stored offline or online or any tablespace.

How does oracle do it in that case, what is the difference between the two and what do you usually recommend specifying something?

I do have a multibyte character set (please see below). Does this mean Oracle will always allocate 4 bytes to each character? In the case of a CLOB column, if the string is longer than 1000 characters, it will always be stored offline?

SQL> select * from v$NLS_PARAMETERS;

PARAMETER                                                        VALUE
---------------------------------------------------------------- ----------------------------
NLS_LANGUAGE                                                     AMERICAN
NLS_TERRITORY                                                    AMERICA
NLS_CURRENCY                                                     $
NLS_ISO_CURRENCY                                                 AMERICA
NLS_NUMERIC_CHARACTERS                                           .,
NLS_CALENDAR                                                     GREGORIAN
NLS_DATE_FORMAT                                                  DD-MON-RR
NLS_DATE_LANGUAGE                                                AMERICAN
NLS_CHARACTERSET                                                 AL32UTF8
NLS_SORT                                                         BINARY
NLS_TIME_FORMAT                                                  HH.MI.SSXFF AM
NLS_TIMESTAMP_FORMAT                                             DD-MON-RR HH.MI.SSXFF AM
NLS_TIME_TZ_FORMAT                                               HH.MI.SSXFF AM TZR
NLS_TIMESTAMP_TZ_FORMAT                                          DD-MON-RR HH.MI.SSXFF AM TZR
NLS_DUAL_CURRENCY                                                $
NLS_NCHAR_CHARACTERSET                                           AL16UTF16
NLS_COMP                                                         BINARY
NLS_LENGTH_SEMANTICS                                             BYTE
NLS_NCHAR_CONV_EXCP                                              FALSE

19 rows selected.

SQL> 
SQL> select * from nls_database_parameters where parameter ='NLS_CHARACTERSET';

PARAMETER                      VALUE
------------------------------ ------------------------------
NLS_CHARACTERSET               AL32UTF8

going off of an earlier reply (above) I have discovered some interesting things regarding Oracle 11g SECUREFILE BLOB storage.
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:14212348049#1172534600346105156

I was making a test that was to test my testing method that I was developing; this method was to test compression-ability of my data; I found a 'bug' in my original method. If I used user_segment.bytes as a data point for comparison, i would have failed with some really odd/bad data. I have seen this number jump to 80MB for 1,000 blocks of usage after I ALTER TABLE T MODIFY LOB() (COMPRESS);. Then, this number would not increase for (at least) another 8,000 blocks. (proof that the blocks are re-used)

Looking at user_extents, it looks like the allocated extents for a SECUREFILE BLOB are not 'deleted', thus the 'user_segment.bytes' will remain the same for that segment.

In order for me to find how much space is actually being used (so that I can compare NOCOMPRESS vs COMPRESS), I searched and found Tom's code for space usage but it didn't work for 11g securefiles. So, I modified it to work for me. (see below)

originating code: http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:231414051079#11326100924186

I still must ask: what are "unexpired blocks"?

thanks,

MK

create or replace procedure show_space
( p_segname in varchar2,
  p_owner   in varchar2 default user,
  p_type    in varchar2 default 'TABLE',
  p_partition in varchar2 default NULL )
-- this procedure uses authid current user so it can query DBA_*
-- views using privileges from a ROLE and so it can be installed
-- once per database, instead of once per user that wanted to use it
authid current_user
as
    l_free_blks                 number;
    l_total_blocks              number;
    l_total_bytes               number;
    l_unused_blocks             number;
    l_unused_bytes              number;
    l_LastUsedExtFileId         number;
    l_LastUsedExtBlockId        number;
    l_LAST_USED_BLOCK           number;
    l_segment_space_mgmt        varchar2(255);
    l_unformatted_blocks number;
    l_unformatted_bytes number;
    l_fs1_blocks number; l_fs1_bytes number;
    l_fs2_blocks number; l_fs2_bytes number;
    l_fs3_blocks number; l_fs3_bytes number;
    l_fs4_blocks number; l_fs4_bytes number;
    l_full_blocks number; l_full_bytes number;
    -- SecureFile stuff
    l_seg_size_blocks number; l_seg_size_bytes number;
    l_used_blocks number; l_used_bytes number;
    l_expired_blocks number; l_expired_bytes number;
    l_unexpired_blocks number; l_unexpired_bytes number;

    -- inline procedure to print out numbers nicely formatted
    -- with a simple label
    procedure p( p_label in varchar2, p_num in number )
    is
    begin
        dbms_output.put_line( rpad(p_label,40,'.') ||
                              to_char(p_num,'999,999,999,999') );
    end;
begin
   -- this query is executed dynamically in order to allow this procedure
   -- to be created by a user who has access to DBA_SEGMENTS/TABLESPACES
   -- via a role as is customary.
   -- NOTE: at runtime, the invoker MUST have access to these two
   -- views!
   -- this query determines if the object is a ASSM object or not
   -- MODIFIED to use 11g dba_segments.segment_subtype
   -- i've only seen these values: ASSM, MSSM, SECUREFILE, null
   begin
      execute immediate
          'select seg.segment_subtype
            from dba_segments seg
            where seg.segment_name      = :p_segname
              and (:p_partition is null or
                  seg.partition_name = :p_partition)
              and seg.owner = :p_owner'
             into l_segment_space_mgmt
            using p_segname, p_partition, p_partition, p_owner;
   exception
       when too_many_rows then
          dbms_output.put_line
          ( 'This must be a partitioned table, use p_partition => ');
          return;
   end;

   -- test to see if person is trying to look at system segments
   -- I have not tried to look at the UNDO segments with this code
   if l_segment_space_mgmt is null
   then
     dbms_output.put_line('I don''t do system segments like UNDO and ROLLBACK');
     return;
   end if;


   -- if the object is in an ASSM tablespace, we must use this API
   -- call to get space information, else we use the FREE_BLOCKS
   -- API for the user managed segments
   -- MODIFIED to match 'ASSM' or 'SECUREFILE'
   if l_segment_space_mgmt = 'ASSM'
   then
     dbms_space.space_usage
     ( p_owner, p_segname, p_type, l_unformatted_blocks,
       l_unformatted_bytes, l_fs1_blocks, l_fs1_bytes,
       l_fs2_blocks, l_fs2_bytes, l_fs3_blocks, l_fs3_bytes,
       l_fs4_blocks, l_fs4_bytes, l_full_blocks, l_full_bytes, p_partition);

     p( 'Unformatted Blocks ', l_unformatted_blocks );
     p( 'FS1 Blocks (0-25)  ', l_fs1_blocks );
     p( 'FS2 Blocks (25-50) ', l_fs2_blocks );
     p( 'FS3 Blocks (50-75) ', l_fs3_blocks );
     p( 'FS4 Blocks (75-100)', l_fs4_blocks );
     p( 'Full Blocks        ', l_full_blocks );
  elsif l_segment_space_mgmt = 'SECUREFILE'
  then
    dbms_space.space_usage
    ( p_owner, p_segname, 'LOB',
      l_seg_size_blocks, l_seg_size_bytes, l_used_blocks, l_used_bytes,
      l_expired_blocks, l_expired_bytes, l_unexpired_blocks, l_unexpired_bytes,
      p_partition);

    p( 'segment size blocks', l_seg_size_blocks );
    p( 'segment size bytes',  l_seg_size_bytes );
    p( 'used blocks', l_used_blocks );
    p( 'used bytes', l_used_bytes );
    p( 'expired blocks', l_expired_blocks );
    p( 'expired bytes', l_expired_bytes );
    p( 'unexpired blocks', l_unexpired_blocks );
    p( 'unexpired bytes', l_unexpired_bytes);

  else
     dbms_space.free_blocks(
       segment_owner     => p_owner,
       segment_name      => p_segname,
       segment_type      => p_type,
       freelist_group_id => 0,
       free_blks         => l_free_blks);

     p( 'Free Blocks', l_free_blks );
  end if;

  -- and then the unused space API call to get the rest of the
  -- information
  if l_segment_space_mgmt != 'SECUREFILE'
  then
  dbms_space.unused_space
  ( segment_owner     => p_owner,
    segment_name      => p_segname,
    segment_type      => p_type,
    partition_name    => p_partition,
    total_blocks      => l_total_blocks,
    total_bytes       => l_total_bytes,
    unused_blocks     => l_unused_blocks,
    unused_bytes      => l_unused_bytes,
    LAST_USED_EXTENT_FILE_ID => l_LastUsedExtFileId,
    LAST_USED_EXTENT_BLOCK_ID => l_LastUsedExtBlockId,
    LAST_USED_BLOCK => l_LAST_USED_BLOCK );

    p( 'Total Blocks', l_total_blocks );
    p( 'Total Bytes', l_total_bytes );
    p( 'Total MBytes', trunc(l_total_bytes/1024/1024) );
    p( 'Unused Blocks', l_unused_blocks );
    p( 'Unused Bytes', l_unused_bytes );
    p( 'Last Used Ext FileId', l_LastUsedExtFileId );
    p( 'Last Used Ext BlockId', l_LastUsedExtBlockId );
    p( 'Last Used Block', l_LAST_USED_BLOCK );
  end if;
end;
/
-- EXAMPLE table to see unexpired blocks/bytes
create table t (
  file_id int primary key,
  file_blob blob
) lob(file_blob) store as securefile t_fblob_seg
        ( disable storage in row
          -- oracle document says PCTVERSION
          -- is ignored for SECUREFILE
          -- PCTVERSION 0
          RETENTION NONE
          decrypt
          nocompress
          keep_duplicates
        )
/

declare
  l_blob blob := utl_raw.copies( utl_raw.cast_to_raw( 'alkjeriujo32i' ), 100 );
begin
  for i in 1 .. 1000
  loop
    insert into t values (i, l_blob);
  end loop;
  commit;
end;
/

SQL> execute show_space('T_FBLOB_SEG');
segment size blocks.....................           2,336
segment size bytes......................      19,136,512
used blocks.............................           1,000
used bytes..............................       8,192,000
expired blocks..........................           1,280
expired bytes...........................      10,485,760
unexpired blocks........................              33
unexpired bytes.........................         270,336

Oracle Database PL/SQL Package and Type References
11g Release 1 (11.1)
Part Number B28419-03
chapter 119 DBMS_SPACE

found here:
http://download.oracle.com/docs/cd/B28359_01/appdev.111/b28419/d_space.htm

SPACE_USAGE Procedures

The first form of the procedure shows the space usage of data blocks under the segment High Water Mark. You can calculate usage for LOBs, LOB PARTITIONS and LOB SUBPARTITIONS. This procedure can only be used on tablespaces that are created with auto segment space management.The bitmap blocks, segment header, and extent map blocks are not accounted for by this procedure.

The second form of the procedure returns information about SECUREFILE LOB space usage. It will return the amount of space in blocks being used by all the SECUREFILE LOBs in the LOB segment. The procedure displays the space actively used by the LOB column, freed space that has retention expired, and freed space that has retention unexpired.

Syntax

DBMS_SPACE.SPACE_USAGE(
segment_owner IN VARCHAR2,
segment_name IN VARCHAR2,
segment_type IN VARCHAR2,
unformatted_blocks OUT NUMBER,
unformatted_bytes OUT NUMBER,
fs1_blocks OUT NUMBER,
fs1_bytes OUT NUMBER,
fs2_blocks OUT NUMBER,
fs2_bytes OUT NUMBER,
fs3_blocks OUT NUMBER,
fs3_bytes OUT NUMBER,
fs4_blocks OUT NUMBER,
fs4_bytes OUT NUMBER,
full_blocks OUT NUMBER,
full_bytes OUT NUMBER,
partition_name IN VARCHAR2 DEFAULT NULL);

DBMS_SPACE.SPACE_USAGE(
segment_owner IN VARCHAR2,
segment_name IN VARCHAR2,
segment_type IN VARCHAR2,
segment_size_blocks OUT NUMBER,
segment_size_bytes OUT NUMBER,
used_blocks OUT NUMBER,
used_bytes OUT NUMBER,
expired_blocks OUT NUMBER,
expired_bytes OUT NUMBER,
unexpired_blocks OUT NUMBER,
unexpired_bytes OUT NUMBER,
partition_name IN VARCHAR2 DEFAULT NULL);

Hi Tom,

The info in this area is useful.

Can you please give me a DDL of a sample partition table
which is having Blob datatype.

Below is the table structure,

Table name: Emp
===============
Empno Number (5)
Ename Varchar2(100)
Deptno Number(2)
photo blob

ops$tkyte%ORA10GR2> CREATE TABLE t
  2  (
  3    dt  date,
  4    x   int,
  5    y   blob
  6  )
  7  PARTITION BY RANGE (dt)
  8  (
  9    PARTITION part1 VALUES LESS THAN (to_date('13-mar-2003','dd-mon-yyyy')) ,
 10    PARTITION part2 VALUES LESS THAN (to_date('14-mar-2003','dd-mon-yyyy')) ,
 11    PARTITION junk VALUES LESS THAN (MAXVALUE)
 12  )
 13  /

Table created.

Hi Tom,

If I get things right, when a BLOB is stored exclusively IN ROW there shouldn't be space used by older versions in the table segment ?

If so, I don't understand why DBMS_SPACE.SPACE_USAGE reports 59.098 MB of full blocks in a 59.161 MB table segment while the used space computed with NUM_ROWS * AVG_ROW_LEN on fresh stats is 29.588 MB (which is consistent with 27.728 MB returned by sum(DBMS_LOB.GETLENGTH()))

How is that possible ? Did I miss something ?

Thanks

ops$tkyte%ORA10GR2> select 59*.8, 59*.7 from dual;

     59*.8      59*.7
---------- ----------
      47.2       41.3

ops$tkyte%ORA10GR2> declare
  2          l_str long := 'create table t1 ( c1 varchar2(2000)';
  3  begin
  4          for i in 2..1000
  5          loop
  6                  l_str := l_str || ', c' || i || ' varchar2(2000)';
  7          end loop;
  8          execute immediate l_str || ') pctfree 0';
  9          execute immediate replace( l_str, 't1', 't2' ) || ') pctfree 0';
 10  end;
 11  /

PL/SQL procedure successfully completed.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> insert into t1 (c1,c2) select 'x', 'x' from all_objects;

50346 rows created.

ops$tkyte%ORA10GR2> insert into t2 (c1,c1000) select 'x', 'x' from all_objects;

50346 rows created.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> exec dbms_stats.gather_table_stats( user, 'T1' );

PL/SQL procedure successfully completed.

ops$tkyte%ORA10GR2> exec dbms_stats.gather_table_stats( user, 'T2' );

PL/SQL procedure successfully completed.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> select table_name, avg_row_len, num_rows from user_tables where table_name in ( 'T1', 'T2' );

TABLE_NAME                     AVG_ROW_LEN   NUM_ROWS
------------------------------ ----------- ----------
T1                                       4      49778
T2                                       4      50223

ops$tkyte%ORA10GR2> exec show_space( 'T1' )
Unformatted Blocks .....................               0
FS1 Blocks (0-25)  .....................               0
FS2 Blocks (25-50) .....................               1
FS3 Blocks (50-75) .....................               0
FS4 Blocks (75-100).....................               4
Full Blocks        .....................              68
Total Blocks............................              80
Total Bytes.............................         655,360
Total MBytes............................               0
Unused Blocks...........................               0
Unused Bytes............................               0
Last Used Ext FileId....................               4
Last Used Ext BlockId...................          45,961
Last Used Block.........................               8

PL/SQL procedure successfully completed.

ops$tkyte%ORA10GR2> exec show_space( 'T2' )
Unformatted Blocks .....................              62
FS1 Blocks (0-25)  .....................               1
FS2 Blocks (25-50) .....................               0
FS3 Blocks (50-75) .....................               0
FS4 Blocks (75-100).....................              21
Full Blocks        .....................           6,712
Total Blocks............................           6,912
Total Bytes.............................      56,623,104
Total MBytes............................              54
Unused Blocks...........................               0
Unused Bytes............................               0
Last Used Ext FileId....................               4
Last Used Ext BlockId...................          52,745
Last Used Block.........................             128

PL/SQL procedure successfully completed.

Hi Tom,

My table has only 7 columns. It seems that 3 extra bytes per column don't account for the gap.

I built a test case with two columns and filled the clob with first 1.000 char 
second 1.800 char (charset is UTF8 -> 2 bytes/char)

In both cases, the clob is stored in table row.

In the first case, I find 333 full blocks so roughly 2.728 bytes per row which is very near avg_row_len (2.106).

In the second case, I find 999 full blocks so roughly 8.192 bytes per row which is more than twice avg_row_len.

It looks like the lob is allocated space in the table row by "chunks" and can therefore use much more space than it's actual size.

I am right ?

Thanks

SQL> create table test(id number(6), text clob) tablespace psaprei;

Table créée.

SQL> insert into test(id, text)
  2  select 1, rpad('x',1000, 'x')
  3  from dual
  4  connect by level <= 1000;

1000 ligne(s) créée(s).

SQL> select segment_name, extents, blocks
  2  from   dba_segments
  3  where  segment_name='TEST'
  4  union
  5  select segment_name, extents, blocks 
  6  from dba_segments
  7  where segment_name in (
  8    select segment_name
  9    from dba_lobs
 10    where table_name='TEST');

SEGMENT_NAME                                                                         EXTENTS     BLOCKS
--------------------------------------------------------------------------------- ---------- ----------
SYS_LOB0000202146C00002$$                                                                  1          8
TEST                                                                                      18        384

SQL> execute dbms_stats.gather_table_stats('SAPREI','TEST')

Procédure PL/SQL terminée avec succès.

SQL> select num_rows, avg_row_len, num_rows * avg_row_len
  2  from   dba_tables
  3  where table_name='TEST';

  NUM_ROWS AVG_ROW_LEN NUM_ROWS*AVG_ROW_LEN
---------- ----------- --------------------
      1000        2106              2106000

SQL> execute show_space('TEST')
Unformatted Blocks .....................               0
FS1 Blocks (0-25) ......................               0
FS2 Blocks (25-50) .....................               0
FS3 Blocks (50-75) .....................               1
FS4 Blocks (75-100).....................              36
Full Blocks    .........................             333
Total Blocks............................             384
Total Bytes.............................       3,145,728
Total MBytes............................               3
Unused Blocks...........................               0
Unused Bytes............................               0
Last Used Ext FileId....................              43
Last Used Ext BlockId...................         520,201
Last Used Block.........................             128

Procédure PL/SQL terminée avec succès.


SQL> create table test(id number(6), text clob) tablespace psaprei;

Table créée.

SQL> insert into test(id, text)
  2  select 1, rpad('x',1800, 'x')
  3  from dual
  4  connect by level <= 1000;

1000 ligne(s) créée(s).

SQL> select segment_name, extents, blocks
  2  from   dba_segments
  3  where  segment_name='TEST'
  4  union
  5  select segment_name, extents, blocks 
  6  from dba_segments
  7  where segment_name in (
  8    select segment_name
  9    from dba_lobs
 10    where table_name='TEST');

SEGMENT_NAME                                                                         EXTENTS     BLOCKS
--------------------------------------------------------------------------------- ---------- ----------
SYS_LOB0000202149C00002$$                                                                  1          8
TEST                                                                                      23       1024

SQL> execute dbms_stats.gather_table_stats('SAPREI','TEST')

Procédure PL/SQL terminée avec succès.

SQL> select num_rows, avg_row_len, num_rows * avg_row_len
  2  from   dba_tables
  3  where table_name='TEST';

  NUM_ROWS AVG_ROW_LEN NUM_ROWS*AVG_ROW_LEN
---------- ----------- --------------------
      1000        3706              3706000

SQL> execute show_space('TEST')
Unformatted Blocks .....................               0
FS1 Blocks (0-25) ......................               0
FS2 Blocks (25-50) .....................               0
FS3 Blocks (50-75) .....................               1
FS4 Blocks (75-100).....................               0
Full Blocks    .........................             999
Total Blocks............................           1,024
Total Bytes.............................       8,388,608
Total MBytes............................               8
Unused Blocks...........................               0
Unused Bytes............................               0
Last Used Ext FileId....................              48
Last Used Ext BlockId...................         970,633
Last Used Block.........................             128

Procédure PL/SQL terminée avec succès.

Hi Tom,

Yes, with ~4.1k of data only one row fits in a block but show_space() should report blocks just over 50% full whereas it reports full blocks.

So where are the remaining 3.9k ? It looks like they were generously allocated to the lob for its future expansion.

Thanks

HI, Tom,

We recently encountered some problem in BLOB segment in one Oracle 9.2.0.4 database, where multiple sessions updating the same BLOB ended up with enqueue HW contention.

To temporary fix the problem, we have manually allocated additional 12GB space into the BLOB segment. However, from DBMS_SPACE.UNUSED_SPACE, we have seen that "unused bytes" is only 2147483648, or 2GB. From the segment extent map, we have seen that the free space above HWM is 2.0625GB. Is this normal? Shouldn't the "unused bytes" be 12GB instead of 2GB?

Appreciate very much for your input.

Hi Tom,

You said "The lob index built on the pages constituting the lob will be stored in yet a 3'rd tablespace lob_index_ts"

But thats not working for me.
Where as you mentioned in this https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1965916633267 that "you have NO control over the lob index, it is an internal structure and goes where it goes. the lob index is considered "part of the lob" itself."

Why the answer in both posts are conflicting.
If the LOB index tablespace was supporting in old version of Oracle which is now deprecated or what.
I am hesitant to ask this question, but to make a decision on the Lob Index tablespace I need your clarity for both the above concerns.