parsing output from SQL querry

Shell Programming and Scripting
1

Hi all

I have this output in a variable called ...yes $OUTPUT :slight_smile: original...huh
these are tablespaces in an Oracle db
how do I get this into another variable in two columns
so I can do a check on the numbers (space left)

SYSTEM 290; USERS 19; UNDOTBS1 1863; DATA 5982; SYSTEM 290; USERS 19; UNDOTBS1 1863; DATA 5982;

I hope you guys can help ..

Thanks Tom

2 
s="SYSTEM 290; USERS 19; UNDOTBS1 1863; DATA 5982; SYSTEM 290; USERS 19; UNDOTBS1 1863; DATA 5982;"
echo "$s" | awk  'BEGIN{FS=";";OFS="\n"}{$1=$1;print}' | while read col1 col2; do....done
3

I�m not really sure what your string is doing and I cant get i running
could you please have a look at the script..
Tom

#!/bin/ksh

ORACLE_HOME=/pack/oracle/product/10.2.0
export ORACLE_HOME

ORACLE_SID=INAPI2
export ORACLE_SID

ORACLE_BASE=/pack/oracle
export ORACLE_BASE

OUTPUT=`sqlplus -s '/ as sysdba' <<-EOF
set heading off feedback off serveroutput on trimout on pagesize 2
select tablespace_name,trunc(sum(bytes)/1024/1024)||';' MB_free from dba_free_sp
ace group by tablespace_name;
/
EOF`

echo "$OUTPUT" | awk 'BEGIN{FS=";";OFS="\n"} { $1=$1;print}' | while read col1 col2; do echo col1

#for i in $res
#do echo $res
#echo $i |tr -d ' '| awk '{ FS = " " ; print $1}'
#| awk '{ FS = " " ; print $1}'
#echo $i
#echo $OUTPUT | while read output;
#do
#TABLESPACE=`echo $OUTPUT | grep ';'`
done

4

Why don't you tell us what exactly you want to check?

5

do u want to print as follows,
table space SYSTEM has 290 MB space left.
table space USERS has 19 MB space left.
table space UNDOTBS1 has 1863 MB space left.
table space DATA has 5982 MB space left.
table space SYSTEM has 290 MB space left.
table space USERS has 19 MB space left.
table space UNDOTBS1 has 1863 MB space left.
table space DATA has 5982 MB space left.

6

hi
thats excatly what i want,
but piped to a file if there is less than 50 MB freespace

Br Tom

7

Then just change the query:

select tablespace_name,trunc(sum(bytes)/1024/1024)||';' MB_free
from dba_free_space
group by tablespace_name
having trunc(sum(bytes)/1024/1024)<50;
8

great it works just fine..
thanks a lot
Tom