Changing Segment Space Management to Auto from Manual

Change segment space management to AUTO from MANUAL for USERS tablespace:


Step 1. Create a new tablespace users1 

SQL> create tablespace users1
  2  datafile '/u01/app/oracle/oradata/disk3/db1/users1.dbf'
  3  size 500M autoextend on
  4  extent management local
  5  segment space management auto;

Tablespace created.

SQL> select * from dba_tablespaces where tablespace_name='USERS1';

TABLESPACE_NAME                BLOCK_SIZE INITIAL_EXTENT NEXT_EXTENT MIN_EXTENTS
------------------------------ ---------- -------------- ----------- -----------
MAX_EXTENTS PCT_INCREASE MIN_EXTLEN STATUS    CONTENTS  LOGGING   FOR EXTENT_MAN
----------- ------------ ---------- --------- --------- --------- --- ----------
ALLOCATIO PLU SEGMEN DEF_TAB_ RETENTION   BIG
--------- --- ------ -------- ----------- ---
USERS1                               8192          65536                       1
 2147483645                   65536 ONLINE    PERMANENT LOGGING   NO  LOCAL
SYSTEM    NO  AUTO   DISABLED NOT APPLY   NO

Step 2. Identify the objects belonging to USERS tablespace:


SQL> select owner,segment_name from dba_segments where tablespace_name='USERS';

OWNER                          SEGMENT_NAME
------------------------------ ------------------------------
SYS                            REPAIR_TABLE
SCOTT                          PK_DEPT
SCOTT                          PK_EMP
SCOTT                          BONUS
SCOTT                          SALGRADE
ROHAN                          T1
SCOTT                          T1
SYED                           T1

8 rows selected.

Step 3. Move objects from USERS tablespace to USERS1 tablespace
(we can also generate a single script and execute to move the objects in one go, but 
since we have very few objects we can move objects one by one):

SQL> alter table scott.dept move tablespace users1;

Table altered.

SQL> alter table scott.emp move tablespace users1;

Table altered.

SQL> alter table scott.bonus move tablespace users1;

Table altered.

SQL> alter table scott.t1 move tablespace users1;

Table altered.

SQL> alter table syed.t1 move tablespace users1;

Table altered.

SQL> alter table rohan.t1 move tablespace users1;

Table altered.

SQL> alter table scott.salgrade move tablespace users1;

Table altered.

SQL> alter table sys.repair_table move tablespace users1;

Table altered.

Step 4. Identify index segment on USERS tablespace and rebuild on USERS1 tablespace:

SQL> select owner,segment_type,segment_name from dba_segments
  2  where segment_type='INDEX' and tablespace_name='USERS';

OWNER                          SEGMENT_TYPE       SEGMENT_NAME
------------------------------ ------------------ ------------------------------
SCOTT                          INDEX              PK_DEPT
SCOTT                          INDEX              PK_EMP

SQL> alter index scott.pk_dept rebuild tablespace users1;

Index altered.

SQL> alter index scott.pk_emp rebuild tablespace users1;

Index altered.

Step 5. Verify all objects have been moved to USERS1 tablespace:

SQL> select count(*) from dba_segments where tablespace_name='USERS';

  COUNT(*)
----------
         0

Step 6. Drop tablespace USERS :

SQL> drop tablespace users including contents;
drop tablespace users including contents
*
ERROR at line 1:
ORA-12919: Can not drop the default permanent tablespace

Since USERS tablespace is the default tablespace, we cannot drop it.

First we have to make USERS1 as default tablespace:

SQL> alter database default tablespace users1;

Database altered.

Now, we can drop USESR tablespace:

SQL> drop tablespace users including contents;

Tablespace dropped.

Step 7. Rename USERS1 tablespace to USERS:

SQL> alter tablespace users1 rename to users;

Tablespace altered.

Now, verify tablespace has been renamed:

SQL> select name from v$tablespace;

NAME
------------------------------
SYSTEM
UNDOTBS01
SYSAUX
TEMP
RCAT_TBS
USERS

6 rows selected.