Oracle: list free extends with DBA_FREE_SPACE
February 06, 2010 00:38:29 Last update: February 06, 2010 00:39:01
From Oracle doc:
Note that if a datafile (or entire tablespace) is offline in a locally managed tablespace, you will not see any extent information. If an object has extents in an online file of the tablespace, you will see extent information about the offline datafile. However, if the object is entirely in the offline file, a query of this view will not return any records.
DBA_FREE_SPACE describes the free extents in all tablespaces in the database.
Note that if a datafile (or entire tablespace) is offline in a locally managed tablespace, you will not see any extent information. If an object has extents in an online file of the tablespace, you will see extent information about the offline datafile. However, if the object is entirely in the offline file, a query of this view will not return any records.
SQL> select tablespace_name, count(*) free_extends, sum(blocks) total_blocks 2 from dba_free_space 3 group by tablespace_name; TABLESPACE_NAME FREE_EXTENDS TOTAL_BLOCKS ------------------------------ ------------ ------------ SYSAUX 84 5616 USERS 1 12376 SYSTEM 2 904 UNDO 13 14016 SQL>
Easy email testing with http://www.ximailstop.com
