The following query does not end. In the view v$session_wait I found the 'control file sequential read' event.
This happens with Oracle 19 and a large number of tablespaces and datafiles (more than 4000). The same query with Oracle 12.1.0.2 and this number of tablespaces works. And the same query with Oracle 19 immediatley after installation (< 10 tablespaces) also works.
Any ideas? Thanks in advance!
select distinct
a.file#
, b.name
, nvl(d.db_unique_name, 'UNKNOWN') as Database
, 0 as con_id
from v$datafile a
, v$tablespace b
, (select tablespace_name,status from dba_tablespaces) c
, v$database d
where a.ts# = b.ts#
and b.name = c.tablespace_name
order by b.name asc;