Change Tablespace on Oracle

It might be necessary to move the tablespace of tables and indexes to a specific new tablespace.

The example below generates the Alter Statements for all tables and indexes of a specific schema, in this example of the Owner datawh:

SELECT 'ALTER TABLE ' \|\| TABLE_NAME \|\| ' MOVE TABLESPACE datawh;' FROM sys.ALL_TABLES WHERE OWNER LIKE 'DATAWH';
SELECT 'ALTER INDEX ' \|\| INDEX_NAME \|\| ' REBUILD TABLESPACE datawh;' FROM sys.ALL_INDEXES WHERE OWNER LIKE 'DATAWH';

You can now take the generated SQL statements and build a SQL from it to regenerate the tablespaces.