Query to find out EUL5 tables that should have been dropped!

Whenever we schedule any DISCO report, there is a default expiry for report results. When such scheduled reports run, the results are stored in EUL5_B result tables.

As per the process, these result tables should be dropped after the expiry. However, in few unknown cases, these results tables are still available in the database after expiry. The below query will help us identify such tables.

select ‘drop table ‘||BQT_TABLE_NAME||’ cascade constriants;’
from (
select to_date(substr(LTRIM(BQT.BQT_TABLE_NAME,’EUL5_B’),3,2)||’/’|| substr(LTRIM(BQT.BQT_TABLE_NAME,’EUL5_B’),5,2)||’/’||’20’|| substr(LTRIM(BQT.BQT_TABLE_NAME, ‘EUL5_B’),1,2),’mm/dd/yyyy’) results_date
,trunc(sysdate-br_expiry) results_expired_date
,BQT.BQT_TABLE_NAME,FU.USER_NAME, BQT.BQT_CREATED_DATE,
BRR.BRR_CREATED_DATE, BRR.BRR_RUN_DATE,
BR.BR_NEXT_RUN_DATE, BR.BR_AUTO_REFRESH, BR.BR_EXPIRY, BR.BR_NAME, BR.BR_WORKBOOK_NAME
FROM
disceul_us.eul5_bq_tables BQT
JOIN DISCEUL_US.EUL5_BR_RUNS BRR ON BQT.BQT_BRR_ID = BRR.BRR_ID
JOIN DISCEUL_US.EUL5_BATCH_REPORTS BR ON BRR.BRR_BR_ID = BR.BR_ID
JOIN DISCEUL_US.EUL5_EUL_USERS EU ON BR.BR_EU_ID = EU.EU_ID
LEFT JOIN APPS.FND_USER FU ON REPLACE(EU.EU_USERNAME,’#’,”) = FU.USER_ID
);

If any quotes alignment issues from copying above, alternatively you can download query from below link:

EUL5_drop_tables_after_expiry

 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: