Thanks for the hint. Here's the solution (actually I can get the full filesystem) select substr(file_name,1, 22+instr(substr(file_name,23), '/')-1) filesystem from dba_data_files order by file_name; FILESYSTEM ============================= /oradata/lgarsprd_dmx/archive Using this and the output of the df command, I can link the two together for analysis. <geek dba> I may even work out a cron on the df to a text file and map it as an external table. Then do it all inside the db. </geek dba> Saibabu Devabhaktuni wrote: > Are you looking for something like this: > > select substr(name, instr(name, '/', -2)+1, > (instr(name, '/', -1) - instr(name, '/', -2))-1) > from v$datafile > > HTH, > Sai > http://sai-oracle.blogspot.com > > * From: Rodd Holman <Rodd.Holman@xxxxxxxxx> > * To: oracle-l@xxxxxxxxxxxxx > * Date: Tue, 25 Jul 2006 11:30:08 -0500 > > DB: 9.2.0.5, OS: HP-UX 11.11 > > I'm working on some size monitoring scripts to > integrate with OS disk > usage on HP_UX. The problem I'm running into is that > I want to do a > group by filesystem. Oracle doesn't record this > discrete of information. > > My goal is to be able to spool a df and sql output > into a file that > I can then pull into OpenOffice Calc and verify to > make sure that my > files are properly adjusted to not exceed 100% of > filesystem when > extended (maxbytes). It's also my ammo to get SA's to > fork over more > disk when I need it. > > The file_name in dba_data_files contains the > filesystem inclusive. > > Here's what I'm doing and what I want to parse: > > select * from dba_data_files order by file_name; > > FILE_NAME > =============================================== > /oradata/lgarsprd_dmx/archive/lgars_arch_01.dbf > ... > > the key identifier here for me is 'archive' > > select rtrim(substr(file_name, 23),'/') from > dba_data_files order by > file_name; > > RTRIM(SUBSTR(FILE_NAME,23),'/') > =============================== > archive/lgars_arch_01.dbf > ... > > Running this on my db will give me every file. I only > want to limit it > to the 'archive', 'index', etc. So I can do a sum of > the bytes, > maxbytes and group on the filesystem identifier. > > Is there any way to get Oracle to let me select the > slash? Escape key > sequence or regular expression? > > > Thanks in advance > > Rodd > -- > //www.freelists.org/webpage/oracle-l > > > > > > > __________________________________________________ > Do You Yahoo!? > Tired of spam? Yahoo! Mail has the best spam protection around > http://mail.yahoo.com > -- > //www.freelists.org/webpage/oracle-l > > > -- //www.freelists.org/webpage/oracle-l