0

So, I am trying to build a query in RMAN Catalogue ( using RC_RMAN_BACKUP_JOB_DETAILS) to compare the most recent backup duration (TIME_TAKEN_DISPLAY) for each database (DB_NAME) with its historical average AVG backup duration (TIME_TAKEN_DISPLAY).

How do I convert TIME_TAKEN_DISPLAY(timestamp; HH:MM:SS), i.e. in VARCHAR2 Format to a minute format, i.e number only, so as to run the query against the entire RC_RMAN_BACKUP_JOB_DETAILS to compare AVG time taken in past with time takes for last backup for each DB.

One thing that may work is converting String(Time_taken_display)->To_TIME(Time_taken_display in Time format)->TO_NUM(Time_taken_display in minutes in number format), but this will be so highly inefficient.

shubhammakharia
  • 147
  • 1
  • 2
  • 10

1 Answers1

0

The solution can be pretty simple and complex depending on the requirements:

  1. One simple solution is:

    select avg(substr(TIME_TAKEN_DISPLAY, 0,2)*60 + substr(TIME_TAKEN_DISPLAY, 4,2) + substr(TIME_TAKEN_DISPLAY, 7,2)/60) from RC_RMAN_BACKUP_JOB_DETAILS;

  2. Using Type Casting Functions:

Cast TIME_TAKEN_DISPLAY into time format using TO_TIMESTAMP and then cast to TO_NUMBER, but I did not want to take this approach as I plan to run my scripts against all databases logged in the view, and multiple casting will leave the performance highly inefficient.

But as per @alex Poole comment, I will be using ENLAPSED_SECONDS field as it is readily available in seconds and number data type.

shubhammakharia
  • 147
  • 1
  • 2
  • 10