Move tables, indexes and lobs to another tablespace

On many 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. 

Drop tablespace?
If you think the old tablespace is empty, you can use drop tablespace without error. If it's not fully empty, it will reply:

*
ERROR at line 1:

ORA-01549: tablespace not empty, use INCLUDING CONTENTS option

So in my opinion, it's better to use

select FILE_NAME from dba_data_files 
where TABLESPACE_NAME='';

drop tablespace ;

and delete the files afterwards.


Addendum.
The following query I recently used for checking empty tablespaces. It works pretty well, and also makes sure that empty, but yet defined, tables will not be dropped.

select t.tablespace_name
from dba_tablespaces t
where contents='PERMANENT'
minus
select distinct tablespace_name from (
select tablespace_name from dba_segments
union select tablespace_name from dba_tables
union select tablespace_name from dba_tab_partitions
union select tablespace_name from dba_tab_subpartitions
union select tablespace_name from dba_indexes
union select tablespace_name from dba_ind_partitions
union select tablespace_name from dba_ind_subpartitions
union select tablespace_name from dba_lobs
union select tablespace_name from dba_lob_partitions
union select tablespace_name from dba_lob_subpartitions
)
;

Final note
Please use this post carefully. You are totally responsible for dropping things that should not be dropped. Make sure you proofread the scripts, create backups and think for yourself.