This discussion is archived
3 Replies Latest reply: Jul 15, 2010 7:57 AM by 778702 RSS

finding size of os filesystem

748152 Newbie
Currently Being Moderated
my Oracle 11g on a UNIX and on a UNIX prompt I would run df -g /myfilesystem to find GB blocks and free.

i like to get the idea on writting a Oracle job, so, i can insert those into a table, such as.

create table filestat
(
create_ts timestamp default sysdate
, fileSystem_name varchar2(30)
, GB_blocks float
, Free_GB float
)

insert filestat( fileSystem_name, GB_blocks , Free_GB )
values ('/oracle' , @GB_blocks , @Free_GB)

thanks for your idea.
  • 1. Re: finding size of os filesystem
    778702 Explorer
    Currently Being Moderated
    I think, in your case is better to run a unix command that will insert those rows in oracle, connecting to oracle.

    Another ideea is to ouput the result of df command to a file (with -T parameter i think) and read that file from oracle job.

    Reading is something like :
    declare
    f utl_file.file_type;
    s varchar2(200);
    begin
    f := utl_file.fopen('SAMPLEDATA','sample1.txt','R');
    utl_file.get_line(f,s);
    utl_file.fclose(f);
    dbms_output.put_line(s);
    end;

    but you can find more on this forum.

    Regards
  • 2. Re: finding size of os filesystem
    748152 Newbie
    Currently Being Moderated
    I've got

    anonymous block completed

    I wonder how to write the content to a table from the utl_file.get_line(f,s);
  • 3. Re: finding size of os filesystem
    778702 Explorer
    Currently Being Moderated
    In s varchar you will have the line from the file.
    If the content is a comma separated value as: 'field1, field2, field3,..etc' you can use a split function to take the parts (are examples on web), or, if they are only few, even play with subtr/instr functions.

    this code will give you the first and second item for example:

    select
    substr(notParsedText,0,instr(notParsedText,',')-1) as firstitem,
    substr(notParsedText,instr(notParsedText,',',1)+1,instr(notParsedText,',',2)-1) as seconditem

    from
    (select '1,2,3,4,5' as notParsedText from dual)


    you can have an isert into your table with select like the one above.

    As an example

    insert into test
    select
    substr(s,0,instr(s,',')-1) as firstitem,
    substr(s,instr(s,',',1)+1,instr(s,',',2)-1) as seconditem
    from dual;
    end;


    Regards

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points