Move tables, indexes and lobs to another tablespace

On some occasions, I have been using a script from Tom Kyte to move all objects from one tablespace to another. This script also gets all the indexes of the tables that are being moved. After moving the table, you should rebuild the index.
The script has been slightly adapted by me, but the main code is by Tom.

We do need to purge the recyclebin, otherwise you will end up with moving BIN$ objects in your script. What remains sometimes, are the lob segments. These can be moved with:

alter table owner.table move lob(columnname) store as (tablespace newtablespace);

This has been added in a second part of the query. So the total scripting is like below. You may call it with @moveall USERS USERS2 if you want everything to move to the USERS2 tablespace. Note that it only creates a script, the actual moving has to be done and can take quite a while.

set echo off
prompt Enter a value for the OLD tablespace:
define old_tablespace=&1
prompt Enter a value for the NEW tablespace:
define new_tablespace=&2


column order_col1 noprint
column order_col2 noprint
set heading off verify off feedback off echo off
set pages 0 lines 9999 trimspool on
purge dba_recyclebin;

spool tmp.sql

select decode( segment_type, 'TABLE', segment_name, table_name ) order_col1,
decode( segment_type, 'TABLE', 1, 2 ) order_col2,
'alter ' || segment_type || ' ' || owner || '.' || segment_name || decode( segment_type, 'TABLE', ' move ', ' rebuild ' ) ||
' tablespace &&new_tablespace ' || ';'
from dba_segments,
(select table_name, index_name from user_indexes )
where segment_type in ( 'TABLE', 'INDEX' )
and segment_name = index_name (+)
and tablespace_name = '&&old_tablespace'
order by owner, order_col1, order_col2
/


select 'alter table ' || owner || '.' || table_name ||
' move lob(' || column_name || ') store as (tablespace &&new_tablespace);'
from dba_lobs where TABLESPACE_NAME='&&old_tablespace'
/


spool off


set heading on verify on feedback on echo on

REM Now you can:
REM 1. Backup your database
REM 2. Start it restricted if possible
REM 3. Run the generated script


Use it with care, make sure you have a database backup and start up the database in restricted mode if possible.

After the move, check for unusable indexes and invalid objects.