Adding Freelists to LOB Segment

I am trying to add freelists to a table and lob segment. I added freelists for that table with:

alter table storage (freelists 4);

But I don't know the syntax to add freelists to a lob segment. The lob segment is in its own tablespace if that makes a difference. I have tried:

alter table modify lob() (freelists 4);

But it did not work. Somewhere I read that lobs don't use freelists. But I know I can set the number of freelists for the lob when creating the table. I have not been able to find anything after hours of searching. Thanks for any help!

LOB Segment does not have FREELISTS parameter, however, in a RAC environemnt you can set FREEPOOLS parameter which is equivalent to FREELIST GROUPS.

Tamil

I am trying to get 1 freelist group with 2 or 4 freelists total. Would setting the FREEPOOLS parameter to 2 be equal to having 2 freelist within 1 freelist group? The reason I am asking is becuase my table currently has 3 freelist groups and 8 freelists for both the table and lob segment. There seems to be alot of unused space below the high water mark. I am trying to reduce it to one freelist group and 2 or 4 free lists. I can do this for the table, but not the lob. If freelists are not used for lobs, why can I query user_segments and get the freelists and groups for the lob segment? I'm not disagreeing with you, I'm just trying to understand better. Right now the size of the lob tablespace is 5 times the size of the total size of all the Thanks!

Sorry forgot some of my last sentence:

Right now the size of the lob tablespace is 5 times the size of the total size of all the lob in the tablespace. I found this by using dbms_lob.getlength.

See my article on freelists and freelist groups.

http://www.beaconinfotechcorp.com/art_004.html

You have to be very careful when adding freelist groups because of space wastage.

Tamil

Very nice article tamilselvan. Right now I have a 2 node RAC setup. On all of my segments, they have 3 freelist groups and 8 freelists. I think I only need 2 freelist groups (maybe one depending on the # of concurrent transactions). Could all the extra space being wasted be caused by the 3rd freelist group. As far as I understand, each instance in a RAC environment will be connected to their own freelist group. Does this mean the 3rd group is never used? So when deletes are done, will freed blocks also go to the 3rd group? Also, is there a range of the # of concurrent transactions I should have before implementing 2 freelist groups compared to one? Thanks for all the help!

If you have 2 nodes RAC environment, then you should use 2 FREELIST GROUPS. Never use more than needed.

Note:
Manually setting of FREELISTS and FREELIST GROUPS for the database objects should be avoided as much as possible unless the client needs the best performance (measured in < 5 seconds).
To over come this manual setting, Oracle introduced ASSM. With the ASSM you can also get good performance (not the best) on RAC environment. If disk space is not an issue, I would suggest to use ASSM.

Tamil