Oracle query table space usage timeout

Environment: Oracle11g Problem description: run the following statement in the database to view the tablespace utilization: selectd. tp_name tablespace name, e. contents type, e. sta

Environment: Oracle 11g Problem description: execute the following statement in the database to view the tablespace utilization: select d. tp_name tablespace name, e. contents type, e. sta

Environment: Oracle 11g

Problem description: execute the following statement in the database to view the tablespace utilization:

Select d. tp_name tablespace name, e. contents type, e. status, e. extent_management,

B. bytes size m, B. bytes-d.bytes used m, (B. bytes-d.bytes) * 100/B. bytes usage

From (select c. tablespace_name tp_name, sum (c. bytes)/1024/1024 bytes

From dba_free_space c

Group by c. tablespace_name) d,

(Select a. tablespace_name tp_name, sum (a. bytes)/1024/1024 bytes

From dba_data_files

Group by a. tablespace_name) B,

(Select tablespace_name tp_name, contents, status, extent_management

From dba_tablespaces) e

Where d. tp_name = B. tp_name and d. tp_name = e. tp_name

Order by 7;

The execution will end with a timeout after one and a half hours. So I want to see the view used by the statement, execute select count (*) from dba_free_space, and find that the statement is not responded for a long time.

Cause: consulting oracle engineers concluded: because the data volume in DBA_RECYCLE is large, the data volume in dba_free_space is over million. To clear junk data in DBA_RECYCLE, switch to sys user and execute purge DBA_RECYCLE; after the execution is completed for one day and two nights, the tablespace in the database is queried again, and the conclusion is quickly changed.