Whenever I open a "Datafile Definition" window for a tablespace that has lots of free space and click the "Minimize Size?" button, Toad hangs.
Performance issues with dba_extents view (Oracle bug 5029334 and bug 5259025)
Upgrade Oracle Database to v10.2.0.4. If issue still occurs, please consult an Oracle DBA or Oracle Support for assistance.
The following queries are ran during the "Minimize Size?" execution:
Select bytes, file_id, bytes / blocks
from dba_data_files
where file_name = :fname;
Select sum(bytes)
from dba_free_space
where tablespace_name = :tsn
and file_id = :fid
and block_id >= nvl((Select (block_id + (bytes/8192))
from dba_extents
where block_id = (Select max(block_id)
from dba_extents
where file_id = :fid
and tablespace_name = :tsn)
and file_id = :fid
and tablespace_name = :tsn), 0);
* For the 1st query, replace ":fname" to the location of the datafile in question. The data filename can be found by going to the tablespace section in the Schema Browser under the "Data Filename" column.
* For the 2nd query, replace ":tsn" for the name of the tablespace in question. Replace ":fid" for the file number indicated for the tablespace. The file number can be found by going to the tablespace section in the Schema Browser under the "File#" column.
NOTE: ":fid" is a number, no tick marks are required, however, the rest of the parameters require surrounding tick marks.
© 2021 Quest Software Inc. ALL RIGHTS RESERVED. Feedback Terms of Use Privacy