Home » Infrastructure » Linux » df command insert into sqlplus and table databas (Linux)
df command insert into sqlplus and table databas [message #662469] Tue, 02 May 2017 21:16 Go to next message
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 2620 times)
Re: df command insert into sqlplus and table databas [message #662470 is a reply to message #662469] Tue, 02 May 2017 22:22 Go to previous messageGo to next message
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
Re: df command insert into sqlplus and table databas [message #662471 is a reply to message #662470] Tue, 02 May 2017 22:30 Go to previous messageGo to next message
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> 

Re: df command insert into sqlplus and table databas [message #662472 is a reply to message #662471] Tue, 02 May 2017 23:00 Go to previous messageGo to next message
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 2652 times)
Re: df command insert into sqlplus and table databas [message #662479 is a reply to message #662472] Wed, 03 May 2017 06:56 Go to previous messageGo to next message
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.
Re: df command insert into sqlplus and table databas [message #662487 is a reply to message #662472] Wed, 03 May 2017 11:20 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10707
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 Go to previous messageGo to next message
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 2563 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 Go to previous messageGo to next message
EdStevens
Messages: 1376
Registered: September 2013
Senior Member
What do you mean by "can't split the column"?
Re: df command insert into sqlplus and table databas [message #665557 is a reply to message #665490] Sun, 10 September 2017 19:18 Go to previous message
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:
  


Current Time: Thu Mar 28 16:37:36 CDT 2024