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


Comments

Popular posts from this blog

Agent Installation on Windows Server. SQL Server (Failover Cluster) target addition in OEM 12c

Oracle 10g/11g Linux:SMS the alert logs ORA- errors generated in timestamp

Oracle 11g: Install Instant Client 11.2.0.3.0 on Linux x86_64 Server.