Another area to help identify tables that can be purged is for high number of similar tables. The query below will identify tables with similar names, note that you may need to adjust the numbers to better identify such tables. Some examples of cases like this from Oracle's E-Business Suite are listed out below.
The query to use:
select substr(table_name, 1, 15), count(1)
from dba_tables
group by substr(table_name, 1, 15) having count(1) > 30
order by 2 desc;
In Oracle E-Business Suite there are several cases where interim and temp tables are not being cleaned out by the application. Below are a few cases that depend on the module used and the current release of the applications.
There are a couple of groups of temporary tables that have not been cleaned out by Oracle. These tables can be manually removed, but review notes indicated below to check that. The tables may not be causing any significant issues, they are just consuming space on the system. These objects can also slow down gathering object level statistics if there are a large number of them.
A. FA_JOURNALS_INTERIM_xxx
Review note FA_JOURNALS_INTERIM Tables are Not Dropped after Successful Create Journal Entry and Journal Import (Doc ID 757666.1)
B. GL_CONS_INTERFACE_xxxx
Review note Many Interim Tables and Indexes are filling up the GL and GL_NDX tablespaces (Doc ID 287534.1)
C. GL_POSTING_INTERIM_xxx
Review note GL_POSTING_INTERIM_xxxx Tables are Not Being Dropped after Successful Posting (Doc ID 364040.1)
D. XLA_GLT_xxx tables not being cleaned up by oracle
Review note XLA_GLT_XXXX Temporary Tables For Purge Program 'Purge Temporary Journal Import Interface Tables' Not Dropped (Doc ID 1158934.1)
