0

I am using the below command to get a single row output and my requirement is to have a first column as DB NAME followed by the selected columns below. How do I achieve it?

select SESSION_KEY, INPUT_TYPE, STATUS,
  to_char(START_TIME,'mm/dd/yy hh24:mi') start_time,
  to_char(END_TIME,'mm/dd/yy hh24:mi') end_time,
  TIME_TAKEN_DISPLAY
from V$RMAN_BACKUP_JOB_DETAILS
where INPUT_TYPE like '%DB%'
and start_time = (select max(start_time) from V$RMAN_BACKUP_JOB_DETAILS);
Alex Poole
  • 183,384
  • 11
  • 179
  • 318
Arun N
  • 1

2 Answers2

1

You can get the database name by joining to v$database, e.g.:

select d.name, rbjd.session_key, rbjd.input_type, status,
  to_char(rbjd.start_time,'mm/dd/yy hh24:mi') start_time,
  to_char(rbjd.end_time,'mm/dd/yy hh24:mi') end_time,
  rbjd.time_taken_display
from v$database d
cross join v$rman_backup_job_details rbjd
where rbjd.input_type like '%DB%'
and rbjd.start_time = (select max(start_time) from v$rman_backup_job_details);

Or with a subquery if you don't like the cross join, but I'm not a big fan of subqueries in the select list. And there are other ways to get the most recent row.

Alex Poole
  • 183,384
  • 11
  • 179
  • 318
0

This also can be used, As always V$RMAN_BACKUP_JOB_DETAILS output will be single row (as per the query given above).

select name,SESSION_KEY, INPUT_TYPE, STATUS,
  to_char(START_TIME,'mm/dd/yy hh24:mi') start_time,
  to_char(END_TIME,'mm/dd/yy hh24:mi') end_time,
  TIME_TAKEN_DISPLAY
from V$RMAN_BACKUP_JOB_DETAILS,v$database
where INPUT_TYPE like '%DB%'
and start_time = (select max(start_time) from V$RMAN_BACKUP_JOB_DETAILS)
Sandeep kazipeta
  • 1