Login or Register to Ask a Question and Join Our Community

Sponsored Content
Full Discussion: SQLplus and Shell script problem
Top Forums Shell Programming and Scripting SQLplus and Shell script problem Post 302494843 by desibabu on Tuesday 8th of February 2011 03:27:24 PM
Old 02-08-2011
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
 

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

running shell script from sqlplus

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)
Discussion started by: dkr123
9 Replies

2. UNIX for Dummies Questions & Answers

Shell Script And SQLPLUS

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)
Discussion started by: maco_home
6 Replies

3. Shell Programming and Scripting

help me in sending parameters from sqlplus script to unix shell script

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)
Discussion started by: Hara
2 Replies

4. Shell Programming and Scripting

Call sqlplus in the shell script

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)
Discussion started by: beaniebear
3 Replies

5. Shell Programming and Scripting

(Urgent):Creating flat file using sql script and sqlplus from UNIX Shell Script

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)
Discussion started by: praka
6 Replies

6. Shell Programming and Scripting

SQLPLUS within shell script

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)
Discussion started by: gaurav_1711
4 Replies

7. Shell Programming and Scripting

Want to learn/use SQLPLUS in shell script

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)
Discussion started by: poweroflinux
1 Replies

8. Shell Programming and Scripting

sqlplus in shell script

Hi When I use sqlplus in shell script, I get sqlplus: command not found. ORACLE_HOME is not set. How to set ORACLE_HOME in unix? Thanks (3 Replies)
Discussion started by: vinoth_kumar
3 Replies

9. Shell Programming and Scripting

Passing a parameter from a shell script to sqlplus

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)
Discussion started by: Mahomed
4 Replies

10. Shell Programming and Scripting

Sqlplus in shell script

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)
Discussion started by: pvmanikandan
7 Replies

LEARN ABOUT DEBIAN

graphics::primitive::insets

Graphics::Primitive::Insets(3pm)			User Contributed Perl Documentation			  Graphics::Primitive::Insets(3pm)

NAME

       Graphics::Primitive::Insets - Space between things

DESCRIPTION

       Graphics::Primitive::Insets represents the amount of space that surrounds something.  This object can be used to represent either padding
       or margins (in the CSS sense, one being inside the bounding box, the other being outside)

SYNOPSIS

	 use Graphics::Primitive::Insets;

	 my $insets = Graphics::Primitive::Insets->new({
	   top	   => 5,
	   bottom  => 5,
	   left    => 5,
	   right   => 5
	 });

METHODS

   Constructor
       new Creates a new Graphics::Primitive::Insets.

   Instance Methods
       as_array
	   Return these insets as an array in the form of top, right, bottom and left.

       bottom
	   Set/Get the inset from the bottom.

       equal_to
	   Determine if these Insets are equal to another.

       left
	   Set/Get the inset from the left.

       right
	   Set/Get the inset from the right.

       top Set/Get the inset from the top.

       zero
	   Sets all the insets (top, left, bottom, right) to 0.

AUTHOR

       Cory Watson, "<gphat@cpan.org>"

SEE ALSO

       perl(1)

COPYRIGHT &; LICENSE
       Copyright 2008-2010 by Cory G Watson.

       This program is free software; you can redistribute it and/or modify it under the same terms as Perl itself.

perl v5.12.3							    2010-08-21					  Graphics::Primitive::Insets(3pm)
All times are GMT -4. The time now is 04:01 PM.
Unix & Linux Forums Content Copyright 1993-2022. All Rights Reserved.
Privacy Policy