How to change Segment Space Management of a tablespace from MANUAL to AUTO?

That's a good question? If we find out solution. we'll find:

- Backup the tablespace
- Export the tablespace data
- Drop and re-allocate the tablespace
- Import the tablespace

Old Tablespace:

SQL> select tablespace_name, segment_space_management from dba_tablespaces where tablespace_name='TBS_TEST';

TABLESPACE_NAME SEGMEN
------------------------------ ------
TBS_TEST MANUAL

SQL> select * from dba_ts_quotas where tablespace_name='TBS_TEST';

TABLESPACE_NAME USERNAME BYTES MAX_BYTES BLOCKS MAX_BLOCKS DRO
------------------------------ ------------------------------ ---------- ---------- ---------- ---------- ---
TBS_TEST SURACHART 9437184 -1 1152 -1 NO

SQL> select owner, table_name, tablespace_name, num_rows from all_tables where tablespace_name='TBS_TEST';

OWNER TABLE_NAME TABLESPACE_NAME NUM_ROWS
------------------------------ ------------------------------ ------------------------------ ----------
SURACHART TB_TEST TBS_TEST 73189

Solution:
- Backup the tablespace

RMAN> backup tablespace TBS_TEST;

Starting backup at 15-AUG-10
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=64 device type=DISK
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00008 name=/oracle/oradata/ORCL/datafile/o1_mf_tbs_test_66hmvb4p_.dbf
channel ORA_DISK_1: starting piece 1 at 15-AUG-10
channel ORA_DISK_1: finished piece 1 at 15-AUG-10
piece handle=/oracle/oradata/flashback_area/ORCL/backupset/2010_08_15/o1_mf_nnndf_TAG20100815T183444_66hn6nld_.bkp tag=TAG20100815T183444 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 15-AUG-10

- Export the tablespace data

$ expdp TABLESPACES=TBS_TEST DIRECTORY=DATA_PUMP_DIR DUMPFILE=tbs_test.dmp LOGFILE=tbs_test.log
Export: Release 11.2.0.1.0 - Production on Sun Aug 15 18:39:42 2010

Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.

Username: / as sysdba

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SYS"."SYS_EXPORT_TABLESPACE_01": /******** AS SYSDBA TABLESPACES=TBS_TEST DIRECTORY=DATA_PUMP_DIR DUMPFILE=tbs_test.dmp LOGFILE=tbs_test.log
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 9 MB
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "SURACHART"."TB_TEST" 7.074 MB 73189 rows
Master table "SYS"."SYS_EXPORT_TABLESPACE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.SYS_EXPORT_TABLESPACE_01 is:
/oracle/admin/orcl/dpdump/tbs_test.dmp
Job "SYS"."SYS_EXPORT_TABLESPACE_01" successfully completed at 18:40:12

- Drop and re-allocate the tablespace

SQL> drop tablespace tbs_test including contents and datafiles;

Tablespace dropped.

SQL> select tablespace_name, segment_space_management from dba_tablespaces where tablespace_name='TBS_TEST';

no rows selected

SQL> create tablespace tbs_test datafile size 10M autoextend on next 1M segment space management auto;

Tablespace created.

SQL> select tablespace_name, segment_space_management from dba_tablespaces where tablespace_name='TBS_TEST';

TABLESPACE_NAME SEGMEN
------------------------------ ------
TBS_TEST AUTO

SQL> select owner, table_name, tablespace_name, num_rows from all_tables where tablespace_name='TBS_TEST';

no rows selected

- Import the tablespace

$ impdp TABLESPACES=TBS_TEST DIRECTORY=DATA_PUMP_DIR DUMPFILE=tbs_test.dmp LOGFILE=tbs_test.log

Import: Release 11.2.0.1.0 - Production on Sun Aug 15 18:43:14 2010

Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.

Username: / as sysdba

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "SYS"."SYS_IMPORT_TABLESPACE_01" successfully loaded/unloaded
Starting "SYS"."SYS_IMPORT_TABLESPACE_01": /******** AS SYSDBA TABLESPACES=TBS_TEST DIRECTORY=DATA_PUMP_DIR DUMPFILE=tbs_test.dmp LOGFILE=tbs_test.log
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "SURACHART"."TB_TEST" 7.074 MB 73189 rows
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Job "SYS"."SYS_IMPORT_TABLESPACE_01" successfully completed at 18:43:23

Check:

SQL> select tablespace_name, segment_space_management from dba_tablespaces where tablespace_name='TBS_TEST';

TABLESPACE_NAME SEGMEN
------------------------------ ------
TBS_TEST AUTO

SQL> select * from dba_ts_quotas where tablespace_name='TBS_TEST';

TABLESPACE_NAME USERNAME BYTES MAX_BYTES BLOCKS MAX_BLOCKS DRO
------------------------------ ------------------------------ ---------- ---------- ---------- ---------- ---
TBS_TEST SURACHART 9437184 -1 1152 -1 NO

SQL> select owner, table_name, tablespace_name, num_rows from all_tables where tablespace_name='TBS_TEST';

OWNER TABLE_NAME TABLESPACE_NAME NUM_ROWS
------------------------------ ------------------------------ ------------------------------ ----------
SURACHART TB_TEST TBS_TEST 73189

This is just an example. However... change from UNIFORM SIZE to AUTOALLOCATE, we can use this solution.