How to use SUBSTR and INSTR to find the path used for the files in a tablespace

SELECT SUBSTR(FILE_NAME, 1, 
               INSTR(FILE_NAME, '/', -1, 1)  -- Search for position of the first occurrence of the char '/', start at end of string
              -1) "PATH" -- starting from the rightmost side of the string, search from position found in INSTR above, and work your way to position 1
FROM DBA_DATA_FILES
WHERE TABLESPACE_NAME = 'USERS'

If you want to limit the output the first file found for a tablespace, add

AND ROWNUM < 2;

See also this post for how to use this piece of code in a script using input variables.

Source: "SQL Certified Expert Exam Guide" by Steve O'Hearn, page 220