Re: Selecting a slash '/' from a varchar data field
- From: Saibabu Devabhaktuni <saibabu_d@xxxxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Tue, 25 Jul 2006 10:00:25 -0700 (PDT)
Are you looking for something like this:
select substr(name, instr(name, '/', -2)+1,
(instr(name, '/', -1) - instr(name, '/', -2))-1)
from v$datafile
HTH,
Sai
http://sai-oracle.blogspot.com
* From: Rodd Holman <Rodd.Holman@xxxxxxxxx>
* To: oracle-l@xxxxxxxxxxxxx
* Date: Tue, 25 Jul 2006 11:30:08 -0500
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
__________________________________________________
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around
http://mail.yahoo.com
--
//www.freelists.org/webpage/oracle-l
Other related posts:
