Hopefully I'm giving enough detail here. I have a table, letter_bin,
which contains two CLOBS and a VARCHAR2(4000). On insert, the two
clobs are NULL, the VARCHAR2 may or may not be populated.

SQL> desc letter_bin
Name Null? Type
----------------------------------------- --------
----------------------------
LTR_BIN_BARCODE_ID NOT NULL NUMBER(18)
LETTER_DETAIL VARCHAR2(4000)
CONTENT CLOB
LTR_XML CLOB

AVG(LENGTH(LETTER_DETAIL)) COUNT(*) MAX(LENGTH(LETTER_DETAIL))
-------------------------- ---------- --------------------------
325.608595 1606180 1374


AVG(LENGTH(CONTENT)) COUNT(*) MAX(LENGTH(CONTENT))
-------------------- ---------- --------------------
364.742673 82638 37248


AVG(LENGTH(LTR_XML)) COUNT(*) MAX(LENGTH(LTR_XML))
-------------------- ---------- --------------------
2131.86929 1308347 5265

(handful of number/date columns omitted for simplicity)

TABLE_NAME AVG_ROW_LEN PCT_FREE PCT_USED NUM_ROWS FULL_SIZE
ACTUAL_SIZE DIFF WASTED
----------- ----------- ---------- ---------- ---------- ----------
----------- ---------- ---------
LETTER_BIN 1639 40 2168152 5577326592
3553601128 2023725464 809490186

It appears that this table is a good candidate for reorg. Since it
has the LOBs, its not a valid candidate for shrink space.
Fortunately, I'm going to me moving this schema to another instance; I
will get to expdp/impdp over the weekend. I also have about 250k
chained rows.

I'm sure that setting the pct_free much higher will take care of the
chained rows but what about the wasted space part? I have a few days
to tune this table's storage parameters before the move; I'd like to
right any wrongs so this doesn't become an issue again.

Not all of the CLOBS are populated so its hard to pin down a one-size-
fits-all solution for this table. Here are some details on the CLOB
fields, and the varchar2. I thought 40% pct_free would be OK since
not all of the LOBs are populated but I guess I'm off, considering my
250k chained rows.

AVG(LENGTH(LETTER_DETAIL)) COUNT(*) MAX(LENGTH(LETTER_DETAIL))
-------------------------- ---------- --------------------------
325.608595 1606180 1374

AVG(LENGTH(CONTENT)) COUNT(*) MAX(LENGTH(CONTENT))
-------------------- ---------- --------------------
364.742673 82638 37248

AVG(LENGTH(LTR_XML)) COUNT(*) MAX(LENGTH(LTR_XML))
-------------------- ---------- --------------------
2131.86929 1308347 5265

Finally, here is the result of dbms_space.space_usage, which is even
more confusing-It says I have only a (relatively)few blocks that are
candidates for reorg.

FS1 Blocks = 0 Bytes = 0
FS2 Blocks = 1 Bytes = 8192
FS3 Blocks = 3 Bytes = 24576
FS4 Blocks = 45 Bytes = 368640
Full Blocks = 680777 Bytes = 5576925184

So now, I'm thoroughly confused-Am I misunderstanding the issue, or
does the game change once LOBs are worked into the equation?