Sunday, August 16, 2009

Reorg Normal Table

- 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_NAME rebuild;


- Check for  UNUSABLE Index and rebuild them

select 'alter  INDEX ' ||owner||'.'||index_name|| ' rebuild;'  from dba_indexes where status='UNUSABLE';



No comments: