Reorg Database - General action plan

1:-List of Fragmented Tables/Interface tables(Size more than 1 GB)

Owner Table_name
----- ----------
APPLSYS WF_ITEM_ACTIVITY_STATUSES
APPLSYS FND_LOG_MESSAGES
AR RA_CUSTOMER_TRX_LINES_ALL
AR RA_CUST_TRX_LINE_GL_DIST_ALL
INV MTL_TRANSACTION_LOT_NUMBERS
INV MTL_TXN_REQUEST_LINES
INV MTL_TRANSACTIONS_INTERFACE
MRP MRP_ATP_SCHEDULE_TEMP
ONT OE_PRICE_ADJUSTMENTS
ONT OE_ORDER_LINES_ALL
ONT OE_ORDER_LINES_HISTORY
WSH WSH_DELIVERY_DETAILS
WSH WSH_EXCEPTIONS


How to Re-Org Fragmented and Interface Tables

1. Create a new tablespace
create tablespace add datafile size xxx MB;
2. Grant resouce access to the new tablespace
alter user quota unlimited on ;
3. Move the tables in question to the new tablespace by doing the following:
alter table table_name move ;
4. Move the tables back to the original tablespace
alter table move tablespace ;
5. Re-build indexes (need to provide the index list)

sql>spool index_rebuild.sql

select 'ALTER INDEX '||owner||'.'||INDEX_NAME||' rebuild; ' from dba_indexes
where table_name in ('WF_ITEM_ACTIVITY_STATUSES','FND_LOG_MESSAGES','RA_CUSTOMER_TRX_LINES_ALL','RA_CUST_TRX_LINE_GL_DIST_ALL',
'MTL_TRANSACTION_LOT_NUMBERS','MTL_TXN_REQUEST_LINES','MTL_TRANSACTIONS_INTERFACE','MRP_ATP_SCHEDULE_TEMP','OE_PRICE_ADJUSTMENTS',
'OE_PRICE_ADJUSTMENTS','OE_ORDER_LINES_ALL','OE_ORDER_LINES_HISTORY','WSH_DELIVERY_DETAILS','WSH_EXCEPTIONS')
and owner in ('APPLSYS','AR','INV','MRP','ONT','WSH')
order by table_name,index_name
/

sql>spool off
sql>spool index_rebld.lst
sql>@ index_rebuild.sql
sql>spool off
6. Drop the new tablespace

drop tablespace including contents and datafiles;
Note: This action has to be done when there are no users logged on to applications.


2:-Fixing Intermedia Tables

A) SELECT 'exec ctx_ddl.sync_index(''' || u.username || '.' || i.idx_name || '''); 'from
ctxsys.dr$index i, dba_users u where u.user_id=i.idx_owner# and idx_id in (select pnd_cid from ctxsys.dr$pending);
(list of indexes for cross reference)
exec ctx_ddl.sync_index('AR.HZ_CUST_ACCT_SITES_ALL_T1');
exec ctx_ddl.sync_index('IBC.IBC_ATTRIBUTE_BUNDLES_CTX');
exec ctx_ddl.sync_index('ICX.ICX_QUES_CTX');
exec ctx_ddl.sync_index('JTF.JTF_AMV_ITEMS_TEXT_CTX');
exec ctx_ddl.sync_index('JTF.JTF_AMV_ITEMS_NAME_CTX');
exec ctx_ddl.sync_index('JTF.JTF_AMV_ITEMS_DESC_CTX');
exec ctx_ddl.sync_index('APPLSYS.FND_LOBS_CTX');

Synchronize the indexes manually

a)Login as ctxsys/ctxsys
b)Execute the scripts of the output of #A

Remove any rows remaining in DR$PENDING and DR$WAITING by executing the following.

a)delete from ctxsys.DR$PENDING;
b)delete from ctxsys.DR$WAITING;


3:-Schdule the Below given Concurrent programs.

a:-Name of the Concurrent / DBMS job: Gather Schema Statistics
Executable Name/short Name: FNDGSCST
Schedule / Submit Interval: Every Saturday, Start at 1:00 AM, From start of prior run.
Parameter Reference:
Schema Name=ALL
Estimate Percent=25
Degree=3
Backup Flag=NoBackup
History Mode=None
Gather Option=Gather
Keep other parameters as defaulted.




b:-Name of the Concurrent / DBMS job: Purge Obsolete Workflow Runtime Data
Executable Name/short Name: FNDWFPR
Schedule / Submit Interval: Every 12 hours, Off peak hours. From start of prior run.
Parameter Reference:
Age=30
Persistence Type=Temporary
Keep other parameters as defaulted.



c:-Name of the Concurrent / DBMS job: Purge Signon Audit data
Executable Name/short Name: FNDSCPRG
Schedule / Submit Interval: Every Day, Off Peak hours, From start of prior run. Select the Check Box for Increment Date parameter each run.
Parameter Reference:
Audit Date: give date 31 days prior to current date