Wednesday, May 23, 2012

Oracle 10g:Change Tablespace to all objects



1.Use this script to change Index's Tablespace.

SQL>spool C:\Change_Index_tablespace.sql

SQL>select 'ALTER INDEX '||index_name||' REBUILD TABLESPACE BHAVESH_INDX    ;' from user_indexes;
/

ALTER INDEX SYS_C007391 REBUILD TABLESPACE BHAVESH_INDX    ;
ALTER INDEX TLMYIELDFORECAS_IDX$$_28180009 REBUILD TABLESPACE BHAVESH_INDX;
ALTER INDEX TLMSEGMENTFLIGHTMASTER_PK REBUILD TABLESPACE BHAVESH_INDX;
-----
-------
----------

SQL>Spool off


SQL>@C:\Change_Index_tablespace.sql

2.Use this script to change table's tablespace

SQL>spool C:\Change_Index_tablespace.sql

SQL>select 'ALTER TABLE '||table_name||' MOVE TABLESPACE tablespace_name;'  from user_tables;
/
ALTER TABLE MLOG$_MVDCPRLOADYIELD MOVE TABLESPACE tablespace_name;
ALTER TABLE MLOG$_MVFCSTFLIGHTMAPPINGD MOVE TABLESPACE tablespace_name;
ALTER TABLE MLOG$_MVFLIGHTSCHEDULE MOVE TABLESPACE tablespace_name;
ALTER TABLE MLOG$_MVIEW_REFRESH_LOG MOVE TABLESPACE tablespace_name;

------------------
----------------------
------------------------

SQL>Spool off


No comments:

Post a Comment

Dear User,

Thank you very much for your kind response