Hi Nancy,
>> some table spaces manual and others automatic, i just want to know what's the recommended one, to change it to the best way
Good question!
Remember "automatic" and "Manual" refer to the ways Oracle manages the list of free blocks:
- manual - one-ways linked lists
- automatic - bitmaps - ASSM is often called nitmap freelists
The decision is based upon whether you have buffer busy waits
http://dba-oracle.com/googlesearchsite_pro...ealth_check.htm********************************************************
1 -With low DML rates (it makes no difference what you use).
2 - If you don't see buffer busy waits, either is fine as-is.
3 - If you are automatic and see Buffer busy waits, you need to move to manual and add extra freelists to the target table and index.
********************************************************
See details here:
http://dba-oracle.com/googlesearchsite_pro...uffer+busy+assm ******************************************
>> How to change Segment Space Management of a tablespace from MANUAL to AUTO? in oracle 10g R2
You must reorg the table (using CTAS) or dbms_redefinition.
******************************************************
>> the main difference between Manaul and Automatic Segment Space Management
bitmap vs. linked list:
http://www.dba-oracle.com/t_assm_freelists_pctfree.htm