Search code examples
sqldatabasedatabase-administration

Error in checking highest datafile name while using MAX

I'm looking to get the highest filename present in datafile. query: select max(file_name) from dba_data_files where tablespace_name='XYZ' ;

The above query detects only if the datafiles are from abc[0-9].dbf. the query doesn't show the file abc10.dbf even though its present. it shows abc9.dbf. is there any other approach to get the highest filename of a datafile ?

After analyzing it for a while. I gave order by file_name in my query. this is the output.

/.../data01/odam1.dbf
/.../data01/odam10.dbf
/.../data01/odam2.dbf
....................
/../data01/odam9.dbf

maybe the max function detects the last filename and that's why shows odam9.dbf as output and not odam10.dbf.

Solution

  • For the filenames that you have shown, you can order by the length and the name:

    select file_name
from dba_data_files
where tablespace_name = 'XYZ'
order by len(filename) desc, file_name desc
fetch first 1 row only;

    Note: The function for length might be length(), depending on the database. And some databases spell fetch first 1 row only as limit or even select top.

    Also, this makes the big assumption that the filenames all have the same structure, only differing in the number at the end (as in the examples in your question).