RE: Selecting a slash '/' from a varchar data field
- From: "Ken Naim" <kennaim@xxxxxxxxx>
- To: <Rodd.Holman@xxxxxxxxx>, <oracle-l@xxxxxxxxxxxxx>
- Date: Tue, 25 Jul 2006 13:19:40 -0500
If I am understanding the question just make it substr(file_name,22)
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]
On Behalf Of Rodd Holman
Sent: Tuesday, July 25, 2006 11:30 AM
To: oracle-l@xxxxxxxxxxxxx
Subject: Selecting a slash '/' from a varchar data field
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
--
//www.freelists.org/webpage/oracle-l
Other related posts:
