OUA - Dropping Old DWSTAGE.ERR_1% Tables (Doc ID 2709979.1)

Last updated on SEPTEMBER 14, 2020

Applies to:

Goal

On : 2.7.0.0.10 version, Install / Upgrade

OUA - dropping old DWSTAGE.ERR_1% tables

OUA ODI application has retained a large number of temporary tables over time, over 1 million at the moment and the majority are ERR_ tables and the majority have 0 rows. User would like to drop these tables where they are over 6 months old. User would also like to drop tables that have rows that are ERR_ and are older than 6 months. User using the dba_objects table to check for the date/time when the table was created. User have been given an SQL to drop these by their team. As a precaution, User wanted the DBAs to backup the DWSTAGE schema in case the dropping of tables results in ODI errors, however, they were not able to complete the backup despite ETLs beings stopped. They tried for 5 hours and we then abandoned the change.

Is there a risk with dropping ERR_ tables older than 6 months? If so, what are some things that can do to minimize risk other than the export of the schema? Will these tables ever be required by ODI? Can drop safely them to reduce the large number of tables that have been retained and no longer required to be processed?

These are the SQLs that user running to check create table date/time and to create drop statements. User would like to drop all ERR_1% tables as they are older than one year ago. Please advise if these tables can be dropped safely and if ODI is likely to need them at any point:
 
  SELECT *
  FROM dba_objects d
 WHERE (object_name like 'ERR_1%')
  AND OWNER = 'DWSTAGE'
  AND object_type = 'TABLE'
  --and created < 'DD/MM/YYYY HH:MM:SS'
  order by created desc; --326088 Rows
  
select 'drop table DWSTAGE.'||table_name||';' from all_tables where OWNER = 'DWSTAGE' and table_name like 'ERR_1%' and num_rows =0 ; --XXXXXX Rows

Solution

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.