Re: Selecting a slash '/' from a varchar data field
- From: Rodd Holman <Rodd.Holman@xxxxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Tue, 25 Jul 2006 13:54:05 -0500
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)
>
--
//www.freelists.org/webpage/oracle-l
Other related posts:
