3 Replies Latest reply: Jul 15, 2010 9:57 AM by 778702 RSS

    finding size of os filesystem

    748152
      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
          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
            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
              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