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

From: Rodd Holman <Rodd.Holman_at_gmail.com>
Date: Tue, 25 Jul 2006 13:15:26 -0500
Message-ID: <44C65FBE.10003@gmail.com>

Thanks for the hint.
Here's the solution (actually I can get the full filesystem)

select substr(file_name,1, 22+instr(substr(file_name,23), '/')-1) filesystem from dba_data_files
order by file_name;

FILESYSTEM

---

/oradata/lgarsprd_dmx/archive

Using this and the output of the df command, I can link the two together for analysis.

<geek dba>
I may even work out a cron on the df to a text file and map it as an external table. Then do it all inside the db.
</geek dba>

Saibabu Devabhaktuni wrote:
> 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_at_xxxxxxxxx>
> * To: oracle-l_at_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
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>
>
>
>
> __________________________________________________
> Do You Yahoo!?
> Tired of spam? Yahoo! Mail has the best spam protection around
> http://mail.yahoo.com
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>

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

Received on Tue Jul 25 2006 - 13:15:26 CDT