EBS Apps DBA Online

OBIA 11g ODI Temporary Table Consume Large Tablespace, How To Purge These Tables ? (Doc ID 1572972.1) To BottomTo Bottom APPLIES TO: Business Intelligence Applications Consumer - Version 11.1.1.7.0 and later Information in this document applies to any platform. GOAL There are some temporary tables consuming huge amounts of space in the DW_DATA tablespace. The table's name listed as below: C$_<Session ID number>.1GB How can we purge these data? and when to purge these data ? SOLUTION These tables are automatically purged once the interface is run successfully. If there are failed sessions, then this table is not dropped immediately. This helps in debugging the failed sessions. At the end of the load plan which  is generated using Load Plan Generator (LPG), we have a step called Finalize Batch Load , under which there is a step called Clean Work and Flow Tables. This automatically drops any such stale tables that are older than 30 days. You can reduce this default of 30 day

 How to Manually Delete AWR Snapshots We have to manually purge the Optimizer Statistics, split the partitions of AWR objects and purge the AWR Snapshots manually. 1.) Run the below script to check the current space usage of Optimizer Statistics Histogram & AWR tables, its relevant indexes in SYSAUX tablespace.      SQL> conn / as sysdba      SQL> @?/rdbms/admin/awrinfo.sql      Output is written to awrinfo.lst in the current working directory 2.) Check the statistics availablility from Optimizer Statistics Histogram table by number of days.     SQL>  select systimestamp - min(savtime) from sys.wri$_optstat_histgrm_history; The query will return a result similar to the followiing: SYSTIMESTAMP-MIN(SAVTIME) ---------------------------- +000000099 22:30:28.418491 3.) Purge the Statistics by altering the number of days. i.e for this case the number of days are 7, it will purge stats more than 7 days old     SQL>  exec dbms_stats.purge_stats(sysdate - 7);     Executing DBMS