Selecting a slash '/' from a varchar data field

From: Rodd Holman <Rodd.Holman_at_gmail.com>
Date: Tue, 25 Jul 2006 11:30:08 -0500
Message-ID: <44C64710.7000007@gmail.com>

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

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

Received on Tue Jul 25 2006 - 11:30:08 CDT