PCTFREE/PCTUSED for table with updates to LOBs?

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

So now, I'm thoroughly confused-Am I misunderstanding the issue, or does the game change once LOBs are worked into the equation? Received on Tue May 13 2008 - 13:11:20 CDT