STATS Collection Oracle SQL Query

Here is the Sample Oracle SQL Query for the STATS collection.

select t.last_analyzed, nvl(num_rows,0) num_rows, t.* from all_tables t where last_analyzed is not null and owner in (<Oracle_Schema_name>)

order by 1 desc;

SQL Query to find the time taken in hours, minutes and seconds.

Here is the oracle sql query that can be used to find out the time taken in hours, minutes and second.

This sql query converts the date time format and calculates accordingly.

This can be modified accordingly for the requirements and can be tested.

select bb.start_time,bb.end_time,
mod(trunc( 24 * (to_date(bb.end_time,'DD-MON-YYYY HH24:MI:SS') - to_date(bb.start_time,'DD-MON-YYYY HH24:MI:SS'))),60) AS BATCH_TOTAL_TIME_HOURS,
mod(trunc( 24 * 60 * (to_date(bb.end_time,'DD-MON-YYYY HH24:MI:SS') - to_date(bb.start_time,'DD-MON-YYYY HH24:MI:SS'))),60) AS BATCH_TOTAL_TIME_MINS,
mod(trunc( 24 * 60 * 60 *(to_date(bb.end_time,'DD-MON-YYYY HH24:MI:SS') - to_date(bb.start_time,'DD-MON-YYYY HH24:MI:SS'))),60) AS BATCH_TOTAL_TIME_SECONDS
from
XX_CUSTOM_BATCH bb;

SQL Query to validate Start_Date and End_Date for each consecutive records

Assume you have requirement to validate the start date and end date for each record provided and your validation or check should be that every consecutive record should have start_date + 1 from the previous record end date :

Here is the sample data :

EMP_PRICING DATA :

ID     NAME   START_DATE    END_DATE
1.      P1           1-JAN-2019        31-MAR-2019
2.      P1           1-APRIL-2019    30-JUN-2019

Here is sample SQL Query to validate this data in the sql query. Please note this can be modified accordingly to your requirements.


SQL Query.


select * from (select * from EMP_PRICING_DATA order by start_dt) a
where exists (select 1 from EMP_PRICING_DATA b
                      where b.name = a.name
                        and b.start_dt <= a.end_dt
                        and b.start_dt <> a.end_dt + 1
                        and b.end_dt >= a.start_dt
                        and b.rowid < a.rowid
                      );

How to refresh Materialized view

Here is the sample script to refresh the materialized view.


DECLARE
BEGIN
  DBMS_MVIEW.refresh('EMP_MV');    --'EMP_MV is the materialized view name.
END;