Moving Tables, Indexes to another Tablespace

We can move oracle database segments (tables, indexes) from one tablespace to another tablespace easily, following are some simple example. Logon to database and find create sql script for moving tables and index if multiple object, In SQL statement mention tablespace name, object owner and tables/indexes.

Moving Singe Table/Index:

TABLE employees MOVE TABLESPACE hr;
ALTER index.red_id_pk REBUILD TABLESPACE hrindex;

Moging Multipuls Tables:

spool c:\sqlscripts\movetbls.sql;

SELECT 'ALTER TABLE ' OBJECT_NAME ' MOVE TABLESPACE '' [TABLESPACE_NAME] ;'
FROM ALL_OBJECTS
WHERE OWNER = '[USER_NAME']
AND OBJECT_TYPE = 'TABLE';

spool off;

Moging Multipuls Indexes:

spool c:\sqlscripts\moveindx.sql;

SELECT 'ALTER INDEX 'INDEX_NAME' REBUILD TABLESPACE [TABLESPACE_NAME] ;'
FROM ALL_INDEXES
WHERE OWNER = 'USER_NAME'

spool off;

Now, just execute created scripts (movetbls.sql, moveindx.sql)

SQL> @c:\sqlscripts\movetbls.sql
SQL> @c:\sqlscripts\moveindx.sql

We also move multiple tables/indexes to another tablespace using PL/SQL block:

DECLARE
vquery varchar2(300);
cursor indx is SELECT ' ALTER INDEX 'INDEX_NAME' REBULD TABLESPACE hrindex ;' AS cindex
FROM ALL_INDEXES WHERE OWNER='SCOTT';
BEGIN
FOR a IN indx LOOP
EXIT WHEN indx%NOTFOUND IS NULL;
EXECUTE IMMEDIATE indx.cinds;
END LOOP;
END;