Space Tuning / Reclaim space from tablespace / Freeup unused space

First of all identified most wasted space tables after this we will move the table into another tablespace. For this create a new tablespace or we can move table in a preexisting tablespace with the help of  "alter table move" command.

alter table table_name move tablespace tablespace_name;

after executing this command now we need to rebuild index (with or without tablespace clause)

alter index index_name rebuild tablespace tablespace_name;

*note table should not contain Full text index or domain index
If it contains then the scenerio is :

1. Collect create index script only for domain indexes 
2. Save it at some safe place
3. Drop domain index
4. Move table with "alter table move" command as above.
5. Rebuild indexes as above
6. And Rebuild domain indexes

If you forgot to drop domain index , No problem u might got some error as below:
ORA-02327: cannot create index on expression with datatype LOB
ORA-30967: operation directly on the Path Table is disallowed
 

SQL> alter table DR$BLB_01$I move tablespace try;
alter table DR$BLB_01$I move tablespace try
            *
ERROR at line 1:
ORA-30967: operation directly on the Path Table is disallowed


SQL> alter table DR$BLB_01$K move tablespace try;

Table altered.

SQL> alter table DR$BLB_01$N move tablespace try;

Table altered.

SQL> alter table DR$BLB_01$R move tablespace try;
alter table DR$BLB_01$R move tablespace try
            *
ERROR at line 1:
ORA-30967: operation directly on the Path Table is disallowed


SQL> alter table EMP move tablespace try;

Table altered.

and indexes
SQL> alter index SYS_IL0000074265C00002$$ rebuild tablespace try;
alter index SYS_IL0000074265C00002$$ rebuild tablespace try
*
ERROR at line 1:
ORA-02327: cannot create index on expression with datatype LOB

solution to this

alter table table_name move lob(column_name_having_blob_clob_datatype) store as a tablespace tablespace_name;


Read also::
short and brief description on "alter table move"