Re: Selecting a slash '/' from a varchar data field

No,
That would only get me to the second level directory and
not to the actual filesystem level directory.  Those have
variable names archive index1, table1, undo, etc...

It was the issue of substringing the variable length
to include the filesystem level directory, not just
the base /oradata/sid directory.

I needed the filesystem to match up with the output of the df command.

This is what I was looking for:

select substr(file_name,1, 22+instr(substr(file_name,23), '/')-1)
filesystem,
       tablespace_name,
           round(sum(bytes)/1024) kbytes,
           round(sum(maxbytes)/1024) maxkbytes  
from dba_data_files
group by substr(file_name,1, 22+instr(substr(file_name,23), '/')-1),
tablespace_name
order by filesystem, tablespace_name;

FILESYSTEM                    TABLESPACE_NAME KBYTES    MAXKBYTES
============================= =============== ========= =========
/oradata/lgarsprd_dmx/archive LGARS_ARCH      222459904 239616000
/oradata/lgarsprd_dmx/index1  BI_I1024        4966400   32768000
/oradata/lgarsprd_dmx/index1  BI_I1024_PERF   2785280   5767168
...

I can then take that and do a direct compare with
the output of the df command.


Ken Naim wrote:
> If I am understanding the question just make it substr(file_name,22)
> 

--
http://www.freelists.org/webpage/oracle-l


Other related posts: