Changing Segment Space Management from MANUAL to AUTO Using Enterprise Manager

You can reorganise the Tablespace online or offline to change Segment Space Management from MANUAL to AUTO

Assuming you are using Oracle 10g, the best way to do this is to Use Enterprise Manager.

1. From Enterprise Manager, select Tablespaces.
2. Select the Tablespace you want to change for Manual to Auto.
3. From the Actions drop down, select Reorganize and click GO.
4. From the next screen, click on Set Attributes.
5. Under Segment Space Management change the Manual to Auto and Click OK
6. Click Next and change the required settings for Method (Online or Offline rebuild)
7. Click Next to generate the reorganize script and sh0w recommendation
8. Click Next Schedule. To Review the script
9 Click Submit job to reorganize

One of the important things to note is that if you want to use Online reorganise, tables in the Tablespace must meet certain requirements like having integrity constraints defined on them (Primary Keys etc). If they do not meet this, it will warn you than it cannot be reorganised online. Therefore the best in this case is to use Offline reorganize.

Remember to read more on this and only do it if you know what you are doing.