My Oracle Support Banner

Script to report the list of files stored in ASM and CURRENTLY NOT OPENED (Doc ID 552082.1)

Last updated on AUGUST 03, 2023

Applies to:

Oracle Database Exadata Express Cloud Service - Version N/A and later
Gen 1 Exadata Cloud at Customer (Oracle Exadata Database Cloud Machine) - Version N/A and later
Oracle Cloud Infrastructure - Database Service - Version N/A and later
Oracle Database Backup Service - Version N/A and later
Oracle Database Cloud Exadata Service - Version N/A and later
Information in this document applies to any platform.


One of the features of ASM is databases consolidation.  A single diskgroup will be the repository for multiple databases. The result,  having a large number of datafiles.  If the files were created using OMF (Oracle Managed Files)  format, when the datafile is deleted on the database side, it is also deleted on the ASM side.  There may be situations when the datafiles are not deleted on the ASM side, particularly when not using OMF format.

With large diskgroups holding large number of datafiles it is necessary to make good usage of the storage and reclaim space allocated for files not used by the databases.

The following script will provide the list of files stored in ASM and CURRENTLY NOT OPENED  by any database client of the diskgroups.   It is very important to validate the results of the query, particularly for files that could have been marked temporary offline.   It does not apply to READ ONLY tablespaces, because once the file is opened by the database, it will be returned by the query.

Note:  The parameter file (spfile) will be reported always as not opened.   Always double check the file before deleting it.


To view full details, sign in with your My Oracle Support account.

Don't have a My Oracle Support account? Click to get started!

In this Document
 Sample Output 

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.