- Create tablespace "TEST" for reorg the below tables: CREATE TABLESPACE TEST NOLOGGING DATAFILE '/test/oradata/data02/test01.dbf' SIZE 100M AUTOEXTEND ON NEXT 100M MAXSIZE 5000M EXTENT MANAGEMENT LOCAL UNIFORM SEGMENT SPACE MANAGEMENT MANUAL; ALTER TABLESPACE TEST ADD DATAFILE '/test/oradata/data02/test02.dbf' SIZE 100M AUTOEXTEND ON NEXT 100M MAXSIZE 5000M; (Note: Please add more space if needed accordingly) - grant unlimited access alter user APPLSYS quota unlimited on TEST; alter user WSH quota unlimited on TEST; alter user ASO quota unlimited on TEST; alter user ECX quota unlimited on TEST; - move tables to TEST tablespace and back to original tablespace alter table ECX.ECX_DOCLOGS move tablespace TEST; alter table ECX.ECX_DOCLOGS move Tablespace APPS_TS_TX_DATA; alter table APPLSYS.WF_ITEM_ACTIVITY_STATUSES move tablespace TEST; alter table APPLSYS.WF_ITEM_ACTIVITY_STATUSES move Tablespace APPS_TS_TX_DATA; alter table APPLSYS.FND_LOG_MESSAGES move tablespace TEST; alter table APPLSYS.FND_LOG_MESSAGES move Tablespace APPS_TS_TX_DATA; alter table WSH.WSH_EXCEPTIONS move tablespace TEST; alter table WSH.WSH_EXCEPTIONS move Tablespace APPS_TS_TX_DATA; alter table APPLSYS.WF_ITEM_ACTIVITY_STATUSES_H move tablespace TEST; alter table APPLSYS.WF_ITEM_ACTIVITY_STATUSES_H move Tablespace APPS_TS_TX_DATA; alter table ASO.ASO_ORDER_FEEDBACK_T move tablespace TEST; alter table ASO.ASO_ORDER_FEEDBACK_T move Tablespace APPS_TS_QUEUES; - select count(*) from dba_segments where tablespace_name='TEST'; output should be 0 - Re-build indexes (need to provide the index list) alter INDEX OWNER.INDEX_NAMErebuild; - Check for UNUSABLE Index and rebuild them select 'alter INDEX ' ||owner||'.'||index_name|| ' rebuild;' from dba_indexes where status='UNUSABLE';
Sunday, August 16, 2009
Reorg Normal Table
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment