Skip to Main Content

Catalog Query hangs - control file sequential read

HupsiNov 10 2020

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;

This post has been answered by user9540031 on Nov 12 2020
Jump to Answer
Comments
Post Details
Added on Nov 10 2020
3 comments
58 views