- Community Home
- >
- Servers and Operating Systems
- >
- Operating Systems
- >
- Operating System - HP-UX
- >
- Re: UNIX Scripting (KSH) and Oracle
Categories
Company
Local Language
Forums
Discussions
Forums
- Data Protection and Retention
- Entry Storage Systems
- Legacy
- Midrange and Enterprise Storage
- Storage Networking
- HPE Nimble Storage
Discussions
Discussions
Discussions
Forums
Forums
Discussions
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
- BladeSystem Infrastructure and Application Solutions
- Appliance Servers
- Alpha Servers
- BackOffice Products
- Internet Products
- HPE 9000 and HPE e3000 Servers
- Networking
- Netservers
- Secure OS Software for Linux
- Server Management (Insight Manager 7)
- Windows Server 2003
- Operating System - Tru64 Unix
- ProLiant Deployment and Provisioning
- Linux-Based Community / Regional
- Microsoft System Center Integration
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Community
Resources
Forums
Blogs
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО02-11-2009 08:30 AM
тАО02-11-2009 08:30 AM
I haven't worked on oracle much but I have a situation where I have to do df in all the servers and insert that information into oracle table. I have already created table which have 7 columns, I can insert manually but I dont know how to insert that using Korn shell.
SERVER_ID NOT NULL NUMBER(6)
FS_LOCAL_NAME NOT NULL VARCHAR2(200)
LOCAL_MOUNT NOT NULL CHAR(1)
TOTAL_SPACE NOT NULL NUMBER(10)
USED_SPACE NUMBER(10)
SPACE_AVAILABLE NUMBER(10)
PERCENTAGE_USED NUMBER(3,2)
This is just a example I have done manually and its inserting data
#!/bin/ksh
sqlplus pareshan/ac6e94aac5b1b979902c0b0b1f42621761c@let01a<
EXIT
EOF
But the thing I need is I have to run df command and the outuput I have to insert into respective column in the oracle table.
Plz Help,
Thanks In Advance
Solved! Go to Solution.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО02-11-2009 10:42 AM
тАО02-11-2009 10:42 AM
Re: UNIX Scripting (KSH) and Oracle
There are a lot of ways to do this, with varying degrees of efficiency and complexity.
The simplest, and most appealing approach for SA's is probably by parsing df output and generating insert statements.
Here is an example of just that, using PERL as a driver. This works on my laptop in c cygwin session against a local Oracle XE database:
open SQL, "| sqlplus hein/password";
foreach (qx(df)) {
my ( $fs, $total, $user, $avail, $pct, $mnt ) = split;
next if /^File/ ; # header line
$pct =~ s/%//; # don't need no percent sign.
print SQL q(insert into unix_servers values ) .
qq( \( 1, '$fs', 'Y', $total, $user, $avail, $pct\);\n);
}
print SQL qq(commit;\n);
close SQL;
Input:
$ df
Filesystem 1K-blocks Used Available Use% Mounted on
C:\cygwin\bin 58605088 49106180 9498908 84% /usr/bin
C:\cygwin\lib 58605088 49106180 9498908 84% /usr/lib
C:\cygwin 58605088 49106180 9498908 84% /
c: 58605088 49106180 9498908 84% /cygdrive/c
Output:
SQL> select * from unix_servers;
SERVER_ID FS_LOCAL_NAME L TOTAL_SPACE USED_SPACE SPACE_AVAILABLE PERCENTAGE_USED
---------- -------------------- - ----------- ---------- --------------- ---------------
1 C:\cygwin\bin Y 58605088 49106180 9498908 84
1 C:\cygwin\lib Y 58605088 49106180 9498908 84
1 C:\cygwin Y 58605088 49106180 9498908 84
1 c: Y 58605088 49106180 9498908 84
This is just a sample. A lot of details need to be addressed of course. You probably want a DATE column there. And you probably do not want echoes. And I change the percentage column to a simple number(5).
For Oracle it would be nicer to use BIND VARIABLES, but hey, for once or twice a day the above will work fine this will work fine.
If I had to do this for real I might also look at 'EXTERNAL TABLES' to solve this.
This is where you define a table on a flat file, with separators and line terminators of your choice. Place it from teh servers, and uses with with basic oracle commands.
hth,
Hein van den Heuvel
HvdH Performance Consulting
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО02-11-2009 11:32 AM
тАО02-11-2009 11:32 AM
Re: UNIX Scripting (KSH) and Oracle
I am thinking about using sql loader but I am having problem. Any idea?
If i use sql loader in the control file can i use the table which is already created ? if can then how coz I have error here
this is controlfile
control_file
LOAD DATA
INFILE '/home/gc1488/FST/input'
INTO TABLE unix_servers
FIELDS TERMINATED BY ','
(SERVER_ID,
SERVER_NAME,
MARKET,
SERVER_TYPE,
PROD_IND,
OWNER)
already have input file in that location. One thing the table im going to use should already exist or doesnt matter because I have to insert into the table which is already there. so I am using unix_servers which is already there but I have an error here.
SQL*Loader-601: For INSERT option, table must be empty. Error on table UNIX_SERVERS
Is that mean I cannot use the existing table? because the table is empty and still it says table must be empty.
thank you very much
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО02-11-2009 11:33 AM
тАО02-11-2009 11:33 AM
Re: UNIX Scripting (KSH) and Oracle
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО02-11-2009 12:39 PM
тАО02-11-2009 12:39 PM
Re: UNIX Scripting (KSH) and Oracle
Just specify APPEND in the control file.
From the (9.2) Oracle Utilities Ref Man:
SQL*Loader Control File Reference 5-33
APPEND If data already exists in the table, SQL*Loader appends the new rows to it.
data does not already exist, the new rows are simply loaded. You must have
SELECT privilege to use the APPEND option. Case Study 3: Loading a Delimited,
Free-Format File on page 10-11 provides an example.
On the perl versus Ksh.... IMHO... if you do not know how to map that perl script onto KSH actions, then you have no business using KSH as an implementation language. Just an opinion.
Cheers!
Hein.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО02-13-2009 08:59 AM
тАО02-13-2009 08:59 AM
Re: UNIX Scripting (KSH) and Oracle
my bdf command gives me a bunch of output I think if I print it here it will be a long list but I am sure you are aware of that. It includes local file sytem + all the mounted file systems and of course I cant put that in one row of a table so I need to calcuate that using script and find out ( total, used, free and percentage used) and only that information I can enter in the table.
I tried to write korn shell script for that but Its not that easy but I am still trying.
Anybody have some script already or have some idea about it.
I hope I made it clear this time. if any queries please let me know. I will appreciate your help
thaks alot
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО02-13-2009 09:47 AM
тАО02-13-2009 09:47 AM
Re: UNIX Scripting (KSH) and Oracle
In my earlier perl example it would just be a few lines early in the df data loop, making it accumulate variables as it recognizes them and calling for 'next' until all values for a sql insert are available.
While a full BDF output may be too much and show details you do not care to share, some reader migt be able to help you better if you provided say 2 or 3 example sections and the 2 or 3 rows of data from there that you woud want to push into Oracle. Yeah I know, it sounds like we should be able to figure out what you want, but why not make it as easy as possible for folks who are willing to help.
If you chose to show an example, then please provide an attachment with a verbatim .TXT file in addition to a simple cut & paste to make sure we see spaces and line wraps as they really are.
cheers,
Hein.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО02-13-2009 04:34 PM
тАО02-13-2009 04:34 PM
Re: UNIX Scripting (KSH) and Oracle
Well, the first thing to do is to drop the mounted filesystems. Use: bdf -l
However, if you need properly formatted bdf listing, use the attached bdfmegs script. It will never split a line and has a script-friendly -q to drop the header. You can also run it with -V VGname to select only filesystems from a specific volume group. It is very useful to find almost full filesystems -- use -P with a percentage like 95 or 98 to see only those filesystems. And the units of measure are megabytes, not Kbytes. You can also specify -g to see filesystems in gigabytes.
Bill Hassell, sysadmin
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО02-16-2009 07:27 AM
тАО02-16-2009 07:27 AM
Re: UNIX Scripting (KSH) and Oracle
Another thing I found one old thread which is about more or less same and so many replied and sent the code which is attached but I am not able to view that attach file so anyone who have that code could you help me plz. this is the thread im talking about.
http://forums13.itrc.hp.com/service/forums/questionanswer.do?admit=109447627+1234794464067+28353475&threadId=732203
Steven E, protter, Sanjay and Sridhar Bhaskarla, If anyone of you are reading this please can you help me coz you guys replied me in that thread and gave solutions which I think will help me also.
And Bill Hassell, I tried to use the code you have given but I dont know why its not working and its too long so I am not able to understand properly also, do you have any short version plz.
Note: my problem is just i want to format the bdf output with the values separated by space and put that into text file so that i can fed that into oracle table. I use perl also to format but its not working for bdf properly and same script works for every other file which have random spaces or format.
plz help guys
thanks in advance
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО02-16-2009 07:46 AM
тАО02-16-2009 07:46 AM
Re: UNIX Scripting (KSH) and Oracle
Here is something I had floating around for that purpose:
#!/usr/bin/perl
use strict;
use warnings;
my $header = <>; # Eat header line
while (<>) {
if (!/% /) { # glue on next line, if no percentage on this line
chomp; # clean of the new-line
$_ .= <>; # add wrapped part
}
next if /^\w+:/; #skip NFS mount with ":" in first word
chomp;
s/% //; # strip percentage sign
s/\s+/ /g; # replace all tabs and spaces with just 1 space
my ($fs, $kbytes, $used, $avail, $pct_used, $mount) = split; # now have each column in a variable
# do anything you like with the columns (like preparing an oracle insert)
print $_, "\n" ; # just to prove new glued line
}
Hope that helps some,
Hein.