OraFAQ Forum: Linux » df command insert into sqlplus and table databas

Home » Infrastructure » Linux » df command insert into sqlplus and table databas (Linux)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

df command insert into sqlplus and table databas [message #662469]

Tue, 02 May 2017 21:16

diomahardhika
Messages: 18
Registered: March 2017

Junior Member

#!/bin/bash
TEMP=/home/dbpro/dfinsert.tmp

sql=$(df /Aplikasi | awk '{if (NR!=1){print}}' | sed -e 's/\S\+/:/G' > ${TEMP}
while IFS == read -r col1 col2 col3 col4 col5 col6;
 do
        MOUNT=$(printf '%s\n' $col6 )
        PCT=$(printf '%s\n' $col5)
        AVAILABLE=$(printf '%s\n' $col4)
        USED=$(printf '%s\n' $col3)
        MEMORY_CAPACITY=$(printf '%s\n' $col2))

        sqlplus / "as sysdba" << EOF
        insert into df_test
        values ('$MOUNT','$PCT','$AVAILABLE','$USED','$MEMORY_CAPACITY');
        commit;
        exit;
        EOF

done

when i executed the program it was running but it was not insert to the database table.
did I missing something with that code? please help, thank you.

Attachment: table.PNG
(Size: 10.63KB, Downloaded 3166 times)

Report message to a moderator

Re: df command insert into sqlplus and table databas [message #662470 is a reply to message #662469]

Tue, 02 May 2017 22:22

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

Please post DDL for DF_TEST table.

It is Bad Thing to use SYS schema for anything other than database maintenance.

Try doing as below instead & let me know the results


        sqlplus / "as sysdba" << EOF
        insert into df_test
        values ('$MOUNT','$PCT','$AVAILABLE','$USED','$MEMORY_CAPACITY');
        commit;
        exit;
EOF

Report message to a moderator

Re: df command insert into sqlplus and table databas [message #662471 is a reply to message #662470]

Tue, 02 May 2017 22:30

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

[oracle@vbgeneric oracle]$ ./script1.sh
./script1.sh: command substitution: line 16: syntax error near unexpected token `)'
./script1.sh: command substitution: line 16: `        MEMORY_CAPACITY=$(printf '%s\n' $col2))'

SQL*Plus: Release 12.1.0.2.0 Production on Tue May 2 23:28:44 2017

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

Last Successful login time: Tue May 02 2017 23:26:08 -04:00

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL>   2  
1 row created.

SQL> 
Commit complete.

SQL> Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
./script1.sh: line 17: syntax error near unexpected token `done'
./script1.sh: line 17: `done'
[oracle@vbgeneric oracle]$ sqlplus scott/tiger

SQL*Plus: Release 12.1.0.2.0 Production on Tue May 2 23:29:05 2017

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

Last Successful login time: Tue May 02 2017 23:28:44 -04:00

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> select * from df_test;

MOUNT
--------------------------------------------------------------------------------
       PCT	AVAIL	    USED    MEM_CAP
---------- ---------- ---------- ----------




SQL>

Report message to a moderator

Re: df command insert into sqlplus and table databas [message #662472 is a reply to message #662471]

Tue, 02 May 2017 23:00

diomahardhika
Messages: 18
Registered: March 2017

Junior Member

the results are same,the shell scripts was running but the table still null and it wont to insert into the table.
I thing that any something wrong with the code but I dont know what.

#!/bin/bash
TEMP=/home/dbpro/dfinsert.tmp

sql=$(df /Aplikasi | awk '{if (NR!=1){print}}' | sed -e 's/\S\+/:/G' > ${TEMP}
while IFS == read -r col1 col2 col3 col4 col5 col6;
 do
        MOUNT=$(printf '%s\n' $col6 )
        PCT=$(printf '%s\n' $col5)
        AVAILABLE=$(printf '%s\n' $col4)
        USED=$(printf '%s\n' $col3)
        MEMORY_CAPACITY=$(printf '%s\n' $col2))

        sqlplus / "as sysdba" << EOF
        insert into df_test
        values ('$MOUNT','$PCT','$AVAILABLE','$USED','$MEMORY_CAPACITY');
        commit;
        exit;
        EOF

Im already remove 'done' but still same

Attachment: table.PNG
(Size: 13.26KB, Downloaded 3245 times)

Report message to a moderator

Re: df command insert into sqlplus and table databas [message #662479 is a reply to message #662472]

Wed, 03 May 2017 06:56

EdStevens
Messages: 1376
Registered: September 2013

Senior Member

diomahardhika wrote on Tue, 02 May 2017 23:00

the results are same,the shell scripts was running but the table still null and it wont to insert into the table.
I thing that any something wrong with the code but I dont know what.

#!/bin/bash
TEMP=/home/dbpro/dfinsert.tmp

sql=$(df /Aplikasi | awk '{if (NR!=1){print}}' | sed -e 's/\S\+/:/G' > ${TEMP}
while IFS == read -r col1 col2 col3 col4 col5 col6;
 do
        MOUNT=$(printf '%s\n' $col6 )
        PCT=$(printf '%s\n' $col5)
        AVAILABLE=$(printf '%s\n' $col4)
        USED=$(printf '%s\n' $col3)
        MEMORY_CAPACITY=$(printf '%s\n' $col2))

        sqlplus / "as sysdba" << EOF
        insert into df_test
        values ('$MOUNT','$PCT','$AVAILABLE','$USED','$MEMORY_CAPACITY');
        commit;
        exit;
        EOF

Im already remove 'done' but still same

The terminating EOF needs to be at the very beginning of the line. No leading spaces allowed.

Report message to a moderator

Re: df command insert into sqlplus and table databas [message #662487 is a reply to message #662472]

Wed, 03 May 2017 11:20

Mahesh Rajendran
Messages: 10708
Registered: March 2002
Location: oracleDocoVille

Senior Member
Account Moderator

Guess you are trying to load the df output into a database table.
Something like this would do.
Depending on your Linux distro, this can get fancier and simpler.
You can shuffle around the columns (depending on your distro).

oracle@kapi#./someScript
generated sql :
insert into dfinsert values ('/dev/mapper/rhel_kapi-root','29G','8.0G','21G','29%','/');

1 row created.

oracle@kapi#cat someScript
df -h /tmp | grep -v Filesystem | tr -s " " "," | awk -F"," -v quote="'" -v OFS="','" '$1=$1 {print "insert into dfinsert values ("quote $0 quote");"}' > /tmp/dfinsert.sql
echo "generated sql :"
cat /tmp/dfinsert.sql
sqlplus -s $DBA/$DBP <<EOF
@/tmp/dfinsert.sql
exit;
EOF

Edit:
This is how you shuffle the columns, depending on your db definition

oracle@kapi#df -h /tmp
Filesystem                  Size  Used Avail Use% Mounted on
/dev/mapper/rhel_kapi-root   29G  8.0G   21G  29% /
oracle@kapi#df -h /tmp | grep -v Filesystem | tr -s " " "," | awk -F"," -v quote="'" -v OFS="','" '$1=$1 {print "insert into dfinsert values ("quote $1,$6,$3,$5 quote");"}'
insert into dfinsert values ('/dev/mapper/rhel_kapi-root','/','8.0G','29%');

[Updated on: Wed, 03 May 2017 11:30]

Report message to a moderator

Re: df command insert into sqlplus and table databas [message #665476 is a reply to message #662469]

Wed, 06 September 2017 19:12

diomahardhika
Messages: 18
Registered: March 2017

Junior Member

I can inserted data from the df command but i can't split the column, so this what I got.
can somebody help me to fixed it?

here's my code :

#!/bin/bash
TEMP=/home/dbpro/insertdf.tmp

df -Bm /tmp /ebs/report /ebs/form /Database /Aplikasi /Oradata1 /Oradata2 /Oradata3 | awk ' {print $1, $2, $3, $4, $5, $6}' | tail -n 8 > ${TEMP}

while IFS== read -r col1 col2 col3 col4 col5 col6
 do

FILESYSTEM=$(printf '%b\n' "${col1}")
BLOCKS=$(printf '%b\n' "${col2}")
USED=$(printf '%b\n' "${col3}")
AVAILABLE=$(printf '%b\n' "${col4}")
USE=$(printf '%b\n' "${col5}")
MOUNTED_ON=$(printf '%b\n' "${col6}")

sqlplus -s apps/APPSdbclone99 << EOF
insert into testing_2 (filesystem, blocks, used, available, use, mounted_on) values
        ('$FILESYSTEM', '$BLOCKS', '$USED', '$AVAILABLE', '$USE', '$MOUNTED_ON');
commit;
EOF

done < ${TEMP}

Attachment: 4.PNG
(Size: 19.23KB, Downloaded 3149 times)

[Updated on: Wed, 06 September 2017 19:13]

Report message to a moderator

Re: df command insert into sqlplus and table databas [message #665490 is a reply to message #665476]

Thu, 07 September 2017 06:30

EdStevens
Messages: 1376
Registered: September 2013

Senior Member

What do you mean by "can't split the column"?

Report message to a moderator

Re: df command insert into sqlplus and table databas [message #665557 is a reply to message #665490]

Sun, 10 September 2017 19:18

diomahardhika
Messages: 18
Registered: March 2017

Junior Member

you should saw my attachment, that was not in the right column. All the result of df command being in one column I want to separated them, can you help me with that?

[Updated on: Sun, 10 September 2017 19:19]

Report message to a moderator

Previous Topic:	oracle load testing
Next Topic:	sudo sqlplus -v returns "Error 6 initializing SQL*Plus"

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Mon Mar 03 09:14:05 CST 2025