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

From: Rodd Holman <Rodd.Holman_at_gmail.com>
Date: Tue, 25 Jul 2006 13:54:05 -0500
Message-ID: <44C668CD.1050209@gmail.com>

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

Received on Tue Jul 25 2006 - 13:54:05 CDT