Category Archives: Discoverer

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

Map EUL5 result tables with username

The below query will help to map between EUL5 result tables, report name, and the user who ran it.

***

WITH APPS_SEG_INFO AS
(
SELECT /*+ PARALLEL(8) */ SEGMENT_NAME, SUM(BLOCKS)*8/1024 BQT_TABLE_SIZE_MB FROM dba_segments
where OWNER = ‘APPS’ AND SEGMENT_NAME like ‘%EUL5_B%R%’ GROUP BY SEGMENT_NAME
)
select
BQT.BQT_TABLE_NAME “TABLE_NAME(BQT)”, ASI.BQT_TABLE_SIZE_MB, BQT.BQT_CREATED_DATE “TABLE_CREATED_DATE(BQT)”,
BRR.BRR_CREATED_DATE “TABLE_CREATED_DATE(BRR)”, BRR.BRR_RUN_DATE “SCHEDULE_RUN_DATE(BRR)”,
BR.BR_NEXT_RUN_DATE, BR.BR_AUTO_REFRESH, BR.BR_EXPIRY, BR.BR_NAME, BR.BR_WORKBOOK_NAME, FU.USER_NAME
–BQT.BQT_ID, BRR.BRR_ID, BR.BR_ID, BRR.BRR_CREATED_DATE, BRR.BRR_RUN_DATE, BRR.BRR_STATE,
–BQT.BQT_TABLE_NAME, BQT.BQT_CREATED_DATE, BR.BR_NAME, BR.BR_WORKBOOK_NAME, BR.BR_EXPIRY, BR.BR_CREATED_BY, BR.BR_EU_ID,
–EU.EU_USERNAME, FU.USER_NAME, ASI.BQT_TABLE_SIZE_MB
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
JOIN APPS_SEG_INFO ASI ON BQT.BQT_TABLE_NAME = ASI.SEGMENT_NAME
ORDER BY ASI.BQT_TABLE_SIZE_MB DESC
;

***