Toad immediately hangs when going to DBA | Tablespaces. CPU time and Memory Usage goes up as well.
WORKAROUND
Go to Schema Browser > Tablespaces instead, however Space Manager functionality will not be available.
STATUS
This is an Oracle Bug that slows down the DBA_EXTENTS view because of the addition of Locally Managed Tablespaces. Please see Oracle BUG:954191, Doc ID: 316647.999, and Doc ID: 35272.1 in Oracle Metalink. Some information on these documents are also listed below.
=============================================================================
From: Oracle, Reem M 01-Feb-02 18:58
Subject: Re : Monitor SQL in 8i never completes
The bug, 954191, reports a slow down in the DBA_EXTENTS view because of the addition of locally managed tablespaces. This bug is currently open, development is working on improving the performance of this and other space views.
I am trying to get it published. I will update the thread if I am able to so you can see it's information.
At this time, you may have gotten the best out of that query with the addition of the RULE hint.
Reem Munakash
Electronic Support
=============================================================================
From: David B 22-Jul-02 01:54
Subject: DBA_EXTENTS very slow performance
We had the same problem with DBA_EXTENTS being extremely slow on 8.1.7.3 with dictionary managed tablespaces. We removed the hint below and it works normally now.
create or replace view DBA_EXTENTS
(OWNER, SEGMENT_NAME, PARTITION_NAME, SEGMENT_TYPE, TABLESPACE_NAME,
EXTENT_ID, FILE_ID, BLOCK_ID,
BYTES, BLOCKS, RELATIVE_FNO)
as
select ds.owner, ds.segment_name, ds.partition_name, ds.segment_type,
ds.tablespace_name,
e.ext#, f.file#, e.block#, e.length * ds.blocksize, e.length, e.file#
from sys.uet$ e, sys.sys_dba_segs ds, sys.file$ f
where e.segfile# = ds.relative_fno
and e.segblock# = ds.header_block
and e.ts# = ds.tablespace_id
and e.ts# = f.ts#
and e.file# = f.relfile#
and bitand(NVL(ds.segment_flags,0), 1) = 0
union all
select
--removed this hint and re-created the view
--select /*+ ordered use_nl(e) use_nl(f) */
ds.owner, ds.segment_name, ds.partition_name, ds.segment_type,
ds.tablespace_name,
e.ktfbueextno, f.file#, e.ktfbuebno,
e.ktfbueblks * ds.blocksize, e.ktfbueblks, e.ktfbuefno
from sys.sys_dba_segs ds, sys.x$ktfbue e, sys.file$ f
where e.ktfbuesegfno = ds.relative_fno
and e.ktfbuesegbno = ds.header_block
and e.ktfbuesegtsn = ds.tablespace_id
and e.ktfbuesegtsn = f.ts#
and e.ktfbuefno = f.relfile#
and bitand(NVL(ds.segment_flags, 0), 1) = 1
/
=============================================================================
© 2025 Quest Software Inc. ALL RIGHTS RESERVED. Terms of Use Privacy Cookie Preference Center