sql_rows=`sqlplus -s / <<EOF
set heading off
set pagesize 1000
set tab off
set linesize 120 wrap off
column "Path" format a15
--column "No_Of_files" format a10
select tablespace_name, substr(file_name,1,instr(file_name,'/',1,2)) as "Path" , count(*) as "No_Of_files" from dba_data_files
group by rollup ( tablespace_name, substr(file_name,1,instr(file_name,'/',1,2))) order by 1 asc
/
exit
EOF`
for sql_records in $sql_rows
do
if [[ $sql_records != +(#) ]] # ignore lines which contain one or more of the hash characters
then
if [[ $(echo $sql_records | grep -c '/') = 0 ]] # proceed if it's a mount point only
then
echo ${sql_records}
else
mp=`echo ${sql_records}|awk '{print $1}'`
space=`df -kh ${mp}`
f_space=`echo ${space}|awk '{print " " $2 " " $4 " " $9 " " $11}'`
echo ${sql_records} ${f_space}
fi
fi
done
#
sql_records = SYSAUX
SYSAUX
sql_records = /db1/
/db1/ size avail 135G 91G
sql_records = 1
1
sql_records = SYSAUX
SYSAUX
sql_records = 1
1
sql_records = SYSTEM
SYSTEM
sql_records = /db1/
/db1/ size avail 135G 91G
sql_records = 1
1
sql_records = SYSTEM
SYSTEM
sql_records = 1
1
sql_records = UNDOTBS1
UNDOTBS1
sql_records = /db1/
/db1/ size avail 135G 91G
sql_records = 1
1
sql_records = UNDOTBS1
UNDOTBS1
sql_records = 1
1
sql_records = USERS
USERS
sql_records = /db1/
/db1/ size avail 135G 91G
sql_records = 1
1
sql_records = USERS
USERS
sql_records = 1
1
sql_records = 4
4
sql_records = 4
4
sql_records = rows
rows
sql_records = selected.
selected.
When I run this script for some reason the read is returning the records/results from the sql as three distinct rows - i.e. each of the columns are being as one record resulting in a messy output.
You clearly understand SQL but have a problem processing the results of the query in unix Shell.
The problem is:
All the output from the SQL session is going into one environment variable called $sql_rows. There is no record structure and no record delimiters - just a very long list of fields. When you do "for sql_records in $sql_rows" it is reading each field one-by-one (left-to-right on a very wide row).
I would recommend using a SQL "spool filename" statement before the query and a "spool off" statement after the query in order to output the results of the query to a disc file (not a Shell environment variable). With judicious use of SQL commands early in the script like "set feedback off" we can reduce the amount of rubbish appearing in the file. I notice that you have "-s" (silent) on the command line - it all helps.
The file output by "spool filename" will have a proper record structure (one line per hit on the database).
If you post the contents of the file (in code tags) the scripting wizards on this board should be able to fish out the filesystem name, run the appropriate "df" and present the data in an organised manner.
The script is finding out where the database segments reside from Oracle system tables and checking the related disc space. I like the concept of this script and I think that a good clean solution should go into the FAQ on this board.
I have written a huge commercial quality script to monitor multiple Oracle databases and alert if new segments will be required in the forseeable future. It has certain commonality with your script but my SQL side goes a lot further into finding the "high water" mark of the database within multiple fixed size segments. In the commercial world we would never let an Oracle database extend dynamically.
Ps. Whenever you post Oracle questions, please mention the word "Oracle" and state:
Operating System and version.
Oracle version.
What Shell you use.
Last edited by methyl; 02-07-2011 at 07:39 PM..
Reason: minor typos
Thanks a lot for the excellent hint and feedback. Spooling sql results to a file and then reading it in was an option, however, I was leaving that to plan "B". In any case, I ended up going to that plan B (your argument convinced me as well).
So, I got around the field/record delimitor issue. I still have the formatting issue. The spool fil is nicely formatted.
I have also typed the final output what I want to achieve - basically for all records in the spooled file, I want to reproduce all the records as is except add the "Size" and the "Free" space for the mount point on the record.
For some reason I am getting messy output even when I am not manipultaing any input at all (explicitly at least). I have not added the code to output the column titles in my script.
Any suggestion how I can get the formatted look? That's the missing piece now.
Code:
> cat ts_df.sh
#!/bin/ksh
sql_rows=`sqlplus -s / <<EOF
spool /tmp/tmp_${0};
set heading on
set pagesize 1000
set tab on
set linesize 120 wrap off
column "Path" format a15
--column "No_Of_files" format a10
select tablespace_name, substr(file_name,1,instr(file_name,'/',1,2)) as "Path" , count(*) as "No_Of_files" from dba_data_files
group by rollup ( tablespace_name, substr(file_name,1,instr(file_name,'/',1,2))) order by 1 asc;
spool off;
--exit
EOF`
while read sql_rec
do
if [[ $sql_rec != +(#) ]]
then
if [[ $(echo $sql_rec | grep -c '/') = 0 ]]
then
echo $sql_rec
else
mp="$(echo $sql_rec|awk '{print $2}')"
space="$(df -kh $mp)"
f_space="$(echo $space|awk '{print $9 " " $11}')"
echo $sql_rec $f_space
fi
fi
done < /tmp/tmp_${0}
#
> ts_df.sh
TABLESPACE_NAME Path No_Of_files
------------------------------ --------------- -----------
SYSAUX /db1/ 1 135G 91G
SYSAUX 1
SYSTEM /db1/ 1 135G 91G
SYSTEM 1
UNDOTBS1 /db1/ 1 135G 91G
UNDOTBS1 1
USERS /db1/ 1 135G 91G
USERS 1
4
> cat /tmp/tmp_ts_df.sh
TABLESPACE_NAME Path No_Of_files
------------------------------ --------------- -----------
SYSAUX /db1/ 1
SYSAUX 1
SYSTEM /db1/ 1
SYSTEM 1
UNDOTBS1 /db1/ 1
UNDOTBS1 1
USERS /db1/ 1
USERS 1
4
Ideally:
TABLESPACE_NAME Path No_Of_files Size Free
------------------------------ --------------- ----------- ------- --------
SYSAUX /db1/ 1 135G 91G
SYSAUX 1
SYSTEM /db1/ 1 135G 91G
SYSTEM 1
UNDOTBS1 /db1/ 1 135G 91G
UNDOTBS1 1
USERS /db1/ 1 135G 91G
USERS 1
4
Oracle database: 10g (10.2.0.4)
Solaris platform
Korn Shell.
Last edited by desibabu; 02-08-2011 at 04:36 PM..
Reason: Missing information
I'm working tonight and unable to spend time on your post.
Anybody help me out? It probably needs the echo of the database results data lines to have no line terminator (is it "echo -n" in Solaris?) then the echo of the disc space will be on the same line. With careful use of "printf" we should be able to get the columns to line up with the headings. The total line containing just "4" is a special case.
Running df to find out how much space is used, or free, on a filesystem doesn't say much about the space used, or free, in a tablespace or datafile.
It's not so surprising that the "Size", "Free" values in the "Ideal" output are the same each time. The datafiles are all located on the same filesystem.
If it's simply a question of formatting, set "heading off" in your SQL, and use printf to nicely display it as you'd like.
Otherwise you can use the oracle data dictionary to get the free / used space in a single SQL statement, with all the information you need
I understand where the O/P is going. The O/P wants to display the unix disc space free in the filesystems where there are Oracle files.
The unix free space only really becomes a day-to-day issue if the Oracle files are allowed to extend dynamically. The free space in pre-allocated Oracle segments is more important because the database will stall if the database free space runs out.
I appreciate that, but there are anomalies. Such as what the number of datafiles has to do with that, what the size of the filesystem has to do with that and what, if that's the case, the tablespace names have to do with that (tablespaces can have any number of datafiles on any number of filesystems).
Hi All,
Please let me know what i am missing in the following code (part of my script)
Schemas=(AWQM WFCONTROLLER PROVCO PRISM)
for s in "${Schemas}"
do
sch="${s}_$tol"
if || ;then
echo "This is AD or TD region"
sqlplus -s $sch/$tpwd@$ttns <<EOF... (7 Replies)
Hi All,
I'm new to Linux and scripting, apologies in advance for 'stupid' questions.
Please help... Im writing a script that calls a sqlplus script but the sqlplus requires inputs and i cant seem to get this to work.
here is my code.
#!/bin/sh
TERM=vt100
export TERM... (4 Replies)
Hi All,
How i will use sqlplus in shell script?
Can any one provide sample code which explain following:
1. Connect to oracle DB
2. Exceute select * from tablename
3. Release connection to the DB
4. Append output in file everytime when query executes.
Thanks in advance (1 Reply)
Hi
I want to connect to the Oracle database using a username/password and get back the query result(a numeric value) in a variable, which I can then compare using a conditional.
Can anybody help me with this.
Thanks
Gaurav (4 Replies)
Hi,
I need help urgently for following issue. Pls help me to resolve this issue.
I am calling sql script file(file1.sql) from UNIX Shell Script(script1.ksh) using sql plus and trying to create flat file that contains all records returned from SQL query in SQL script(file1.sql)
I given... (6 Replies)
Hi,
I am writing a script to test database connection. If the first try fails, it will wait for 1 minutes and then try again. The script is as following:
........
for i in $ORACLE_SID
do
$ORACLE_HOME/bin/sqlplus $username/$password@$i <<! >/dev/null
select * from tab;
exit
if ; then... (3 Replies)
Can anybody help me out in sending parameters from sql*plus script to unix shell script without using flat files..
Initially in a shell script i will call sql*plus and after getting some value from some tables, i want that variable value in unix shell script. How can i do this?
Please tell me... (2 Replies)
i'm having real problems retrieving the returncode of my sqlplus-call. I found a lot of informations on the net, but havn't been able to get it running so far, so now i ask for some help ;)
I do start the sqlplus out of my shell script with the parameters stored in the proc_clips.sql, which is... (6 Replies)
I have a script which connects to different database servers using sqlplus. Is there a way by which I can run a shell command on that host from sqlplus?
I know about 'host' command but it runs script on the local machine where the original script is running. Is there a way to run command on the... (9 Replies)
02-07-2011
