Showing posts with label Oracle Database Tuning. Show all posts
Showing posts with label Oracle Database Tuning. Show all posts

Friday, May 9, 2008

Change Tablespace of All Tables

SQL>spool tables.out
SQL> SELECT 'ALTER TABLE ' || TABLE_NAME || ' MOVE TABLESPACE NEWTABLESPACE; ' FROM USER_TABLES;
SQL>spool off
SQL>@tables.out

Change Tablespace of All the Indexes

SQL>spool indexes.out
SQL> SELECT 'ALTER INDEX ' || INDEX_NAME || ' REBUILD TABLESPACE NEWTABLESPACE;' FROM USER_INDEXES;
SQL>spool off
SQL>@indexes.out
Note: Here Newtablespace refers to the tablespace where you want to move your indexes. Preferables is you should create another tablespace to keep indexes.