How I resolved ORA-1653 on APPS_TS_TX_DATA tablespace

Hunter Holley

Kanda-Oracle

 Hello Saad Shujaat,

Let me take this opportunity to thank you for participating in the Oracle Support Community. Your contributions are valued.

Thanks for sharing the details. I am sure this helps others.

I would like to add few point . You may defragement those tables that have been purged, to remove High water mark and reduce and reclaim the  space back to Database.

How to defragment
===================

1) alter table <owner>.<table_name> move;
2) Note that, some indexes might become unusable after table is moved, check the index status from dba_indexes for the table moved and rebuild them too as explained in next bullet.
select owner, index_name, status from dba_indexes
where table_owner = upper('&OWNER') and
table_name = upper('&SEGMENT_NAME');

Note:  Pls ensure the tablespace in which the object is currently existing, has got sufficient space before you move/defragment . Ensure to take neccessary backup of the tables before moving the data. Its suggested to do it on test instance and test it before doing it on Production.

3) You will need to collect the statistics for the tables.

For example :-

----------------------
exec fnd_stats.gather_table_stats ('APPLSYS','FND_CONCURRENT_REQUESTS',PERCENT=>99);

Thanks and Best Regards

N Kandasamy