SELECT f.tablespace_name, max(b.checkpoint_time) last_backup_time
FROM dba_data_files f
,v$backup_datafile b
,dba_tablespaces t
WHERE b.file#(+) = f.file_id
AND f.tablespace_name = t.tablespace_name
AND t.status = 'ONLINE'
AND t.contents != 'TEMPORARY'
GROUP BY f.tablespace_name;
TABLESPACE_NAME | LAST_BACKUP_TIME |
---|---|
SYSAUX | 2019-07-12 20:03:01 |
UNDOTBS1 | 2019-07-12 20:03:01 |
USERS | 2019-07-12 20:03:01 |
TESTTBS2 | |
SYSTEM | 2019-07-12 20:03:01 |
TESTTBS | 2019-07-12 20:03:01 |