Script to find all the files

I have built this over the years.. stolen pieces, added pieces, removed pieces. I am sure the baseline came from some public source. If you can identify it let me know. This is a script I run every 24 hours on every one of my databases and store the results.

SELECT
A.*
FROM
(
SELECT
DECODE(D.TABLESPACE_NAME,NULL,DECODE(FILES.TYPE,'CONTROL','CONTROL_FILE','LOG','REDO_LOG_FILE'
,'ARCHIVE_LOG_DEST','ARCHIVE_LOG_DEST','DUMP_DEST '
,'DUMP_DEST','TEMP','TRUE_TEMP'),D.TABLESPACe_NAME) "TABLESPACE_NAME"
,SUBSTR(FILES.FILE_NAME,0,INSTR(SUBSTR(FILES.FILE_NAME
,INSTR(FILES.FILE_NAME,'/')+1,LENGTH(FILES.FILE_NAME)),'/') )"MOUNT_POINT"
,FILES.FILE_NAME "FILE_NAME_PATH"
,tO_CHAR(FILES.BYTES / 1024/1024,'999,999,999.99') "MEG"
,COUNT "BUFFER_WAITS"
, TIME "SECONDS_WAITED"
,D.AUTOEXTENSIBLE
,D.MAXBYTES/1024/1024 "MAX_MEG"
,tO_CHAR(FILE_FREE.FREE_BYTES / 1024/1024,'999,999,999.99') "FILE_FREE_MEG"
,HIGH.HIGHWATERM
FROM
(
SELECT FILE_NAME,BYTES "BYTES",'DATA' "TYPE",FILE_ID FROM DBA_DATA_FILES UNION ALL SELECT MEMBER "FILE_NAME",BYTES "BYTES",'LOG' "DATA",0 "FILE_ID" FROM V$LOGFILE F, V$LOG L WHERE F.GROUP#=L.GROUP#
UNION ALL SELECT NAME "FILE_NAME",SUM(RECORD_SIZE) "BYTES",'CONTROL' "TYPE",0 FILE_ID FROM V$CONTROLFILE,V$CONTROLFILE_RECORD_SECTION GROUP BY NAME
union all select REPLACE(VALUE,'location=','') "FILE_NAME",0 "BYTES",'ARCHIVE_LOG_DEST' "TYPE",0 FILE_ID FROM V$PARAMETER VP WHERE 1=1 and vp.name like 'log_archive_dest%' and vp.value is not null and vp.isdefault='FALSE'
UNION ALL SELECT VALUE "FILE_NAME",0 "BYTES",'DUMP_DEST' "TYPE",0 FILE_ID FROM V$PARAMETER VP WHERE 1=1 AND UPPER(NAME) LIKE '%DUMP%DEST%'
UNION ALL SELECT NAME "FILE_NAME",BYTES,'TEMP' "TYPE",0 FILE_ID FROM V$TEMPFILE
) FILES
,V$INSTANCE V
,DBA_DATA_FILES D
,(SELECT * FROM v$datafile df,x$kcbfwait fw WHERE 1=1 AND FW.INDX+1 = DF.FILE#) WAITS
,(SELECT FILE_ID,SUM(BYTES) "FREE_BYTES" FROM DBA_FREE_SPACE GROUP BY FILE_ID) FILE_FREE
,(SELECT A.TABLESPACE_NAME ,A.FILE_NAME ,A.FILE_ID,
ROUND(((B.MAXIMUM+C.BLOCKS-1)*D.DB_BLOCK_SIZE)/1024/1024) HIGHWATERM
FROM DBA_DATA_FILES A ,(SELECT FILE_ID,MAX(BLOCK_ID) MAXIMUM FROM DBA_EXTENTS GROUP BY FILE_ID) B
,DBA_EXTENTS C ,(SELECT VALUE DB_BLOCK_SIZE FROM V$PARAMETER WHERE UPPER(NAME)='DB_BLOCK_SIZE') D
WHERE 1=1 AND A.FILE_ID = B.FILE_ID AND C.FILE_ID = B.FILE_ID AND C.BLOCK_ID = B.MAXIMUM ORDER BY
A.TABLESPACE_NAME
,A.FILE_NAME
) HIGH
WHERE
1=1
AND D.FILE_NAME (+)=FILES.FILE_NAME
AND D.FILE_ID= WAITS.FILE# (+)
AND FILE_FREE.FILE_ID(+) = FILES.FILE_ID
AND HIGH.FILE_ID (+) = FILES.FILE_ID
ORDER BY SUBSTR(FILES.FILE_NAME,0,INSTR(SUBSTR(FILES.FILE_NAME,INSTR(FILES.FILE_NAME,'/')+1,LENGTH(FILES.FILE_NAME)),'/') ),REPLACE(REPLACE(FILES.FILE_NAME,SUBSTR(FILES.FILE_NAME,0,INSTR(SUBSTR(FILES.FILE_NAME,INSTR(FILES.FILE_NAME,'/')+1,LENGTH(FILES.FILE_NAME)),'/') )'/',NULL),'oracle/'V.INSTANCE_NAME'/',NULL)
) a
WHERE
1=1
ORDER BY 1,2,3,4
;Certainly isn't the fastest or the prettiest script out there. But the information it provides is beneficial. You will need to modify the mount point parsing to match your environment. I couldn't find the one I built for windows which takes that into account.