If not Svrmgrl then what ?
Yogesh, April 09, 2002 - 10:40 pm UTC
If svrmgrl is not there in 9i, then how to manage db operations like shutdown .. etc. I havn't used 9i till now so don't know much about it.
April 10, 2002 - 8:31 am UTC
umm -- sqlplus?
look at the scripts -- i'm connecting "/ as sysdba" -- that is the "internal" account of 9i (been there for years actually).
You don't need svrmgrl in 8i -- you can startup/shutdown whatever with sqlplus.
re: 9i cron
Amit Tandon, April 10, 2002 - 4:22 am UTC
Dear Tom ,
Thanx a lot for this info. I was also trying for such a script for starting my 9i database using cron job script.
regards
Amit Tandon
Need Help
Abdul-Taofiq,Adekeye, April 10, 2002 - 11:14 am UTC
I am currently learning Oracle and I would appreciate if you could help me with some books.
though I got some from my friend but I thought I should mail you may be you could be of help.
I would appreciate if you could send me any book on oracle DBA.
Thanking you,
Abdul
April 10, 2002 - 11:28 am UTC
Great answer!!!
Alla, April 10, 2002 - 7:39 pm UTC
Thank you Tom this solution is realy helpful.
Do you know how to do that in NT/2K?
Ken, April 10, 2002 - 9:17 pm UTC
I've used similar scripts in Unix, but have not been able to do it in windows. Do you know how?
e.g. the windows equivalent of:
sqlplus "/ as sysdba" <<EOF
spool something
select * from v\$loghist
where rownum < 5 ;
spool off;
exit
EOF
Thanks for all of you help!
April 10, 2002 - 9:52 pm UTC
I usually start with
C:\> fdisk
--> install linux or some other unix variant ;)
that works well.
seriously, i do this:
echo xxxx > x.sql
echo yyyyy >> x.sql
echo zzzz >> x.sql
sqlplus "/ as sysdba" @x
erase x.sql
it is a pain but windows doesn't do that pipeing thing so nice.
pass table value to shell variable
Sean, September 02, 2004 - 9:15 pm UTC
Hi Tom,
I can easily passed the value of shell variable to the table like this:
----------------------------
#!/bin/ksh
DB_NAME=`echo $ORACLE_SID`
sqlplus -s scott/tiger@cbadmin<<EOF
# single quota is needed.
insert into t1(c1)
values('$DB_NAME');
commit;
exit
EoF
---------------------------
But how do I pass the value from the table to shell without using spool file, something like:
select job into shell_variable from emp where empno = 1;
Thanks so much for your help.
September 03, 2004 - 9:50 am UTC
well, here the shell is invoking sqlplus so "passing into" is easy. it is easy for sqlplus, when invoking a shell to "passing into" as well
ops$tkyte@ORA9IR2> host export X=Y; echo $X
Y
However what you probably mean is "how can sqlplus return something to the shell".
For that, I usually use grep and sed:
[tkyte@xtkyte-pc tkyte]$ cat test.sh
#!/bin/bash
sqlplus / <<EOF | grep '^lookForMe' | sed 's/^lookForMe//'
select 'lookForMe' || user from dual;
EOF
X=`sqlplus / <<EOF | grep '^lookForMe' | sed 's/^lookForMe//'
select 'lookForMe' || user from dual;
EOF`
echo X is $X
[tkyte@xtkyte-pc tkyte]$ ./test.sh
OPS$TKYTE
X is OPS$TKYTE
[tkyte@xtkyte-pc tkyte]$
passing value from sqlplus to shell
Sean, September 03, 2004 - 12:15 pm UTC
Thanks so much. Sorry to bother you again.
I have almost two identical code. One works, ond didn't. But if I use other tables such as emp, it works fine for both methods.
Thanks so much for your help.
------------------------------------------------------------
/export/home/oracle/sean: test12
261
/export/home/oracle/sean: test13
X is
/export/home/oracle/sean: cat test12
# Test12
#!/bin/ksh
sqlplus perfstat/perfstat@cbprod2 <<EOF \
| grep '^lookForMe' | sed 's/^lookForMe//'
select 'lookForMe' || snap_id from stats\$snapshot
where snap_time between
to_date('1-SEP-2004 23:30:00', 'DD-MM-YYYY HH24:MI:SS')
and to_date('2-SEP-2004 00:30:00', 'DD-MM-YYYY HH24:MI:SS');
EOF
/export/home/oracle/sean: cat test13
# Test13
#!/bin/ksh
X=`sqlplus perfstat/perfstat@cbprod2 <<EOF \
| grep '^lookForMe' | sed 's/^lookForMe//'
select 'lookForMe' || snap_id from stats\$snapshot
where snap_time between
to_date('1-SEP-2004 23:30:00', 'DD-MM-YYYY HH24:MI:SS')
and to_date('2-SEP-2004 00:30:00', 'DD-MM-YYYY HH24:MI:SS');
EOF`
echo X is $X
-----------------------------------------------------------------
September 03, 2004 - 1:38 pm UTC
[tkyte@xtkyte-pc tkyte]$ cat test.sh
#!/bin/ksh
X=`sqlplus / <<EOF | grep '^lookForMe' | sed 's/^lookForMe//'
select 'lookForMe' || user from dual;
EOF`
echo X is $X
[tkyte@xtkyte-pc tkyte]$ ./test.sh
X is OPS$TKYTE
[tkyte@xtkyte-pc tkyte]$
You'll have to ask a "ksh scripter" on your team to diagnose this on your OS I guess.
passing value from sqlplus to shell
Sean, September 03, 2004 - 2:38 pm UTC
Hi Tom,
I create veiw vwsnap instead of using stats$snapshot. It works. But I don't know why. Thanks.
September 03, 2004 - 3:23 pm UTC
must be something to do with $ on your OS, with your ksh release and the way \$ is interpreted inside of `` as opposed to not being in ``
ksh version
Sean, September 03, 2004 - 7:44 pm UTC
Here is the os version:
/export/home/oracle: uname -a
SunOS bass 5.9 Generic_112233-11 sun4u sparc SUNW,Sun-Fire-V210
I don't think our SA would put other version of ksh.
Thanks so much for your help.
September 04, 2004 - 10:49 am UTC
as stated, the ksh is doing something "wacky" that the shells I use (csh or bash) are not.
My observations are:
a) \$ isn't working as expected in the ` `
b) when I correct for that - the ksh is not preserving lines in the output either -- it is one big string
sooooo, you can either
a) find a better ksh scripter to help you
b) use csh, sh, bash or something else
Hans, September 07, 2004 - 9:38 am UTC
I had the same problem with ksh on AIX and found it had to
do with the way the ksh treats the \$; your script will probably work if you add an extra line like this :
$cat test.sh
export snapview=stats\$snapshot
X=`sqlplus perfstat/perfstat@nl_prd <<EOF \
| grep '^lookForMe' | sed 's/^lookForMe//'
select 'lookForMe' || snap_id from $snapview
where snap_time between
to_date('1-SEP-2004 23:30:00', 'DD-MM-YYYY HH24:MI:SS')
and to_date('2-SEP-2004 00:30:00', 'DD-MM-YYYY HH24:MI:SS');
EOF`
echo "X is $X"
$test.sh
1207
Here document in the function
Sean, September 07, 2004 - 12:41 pm UTC
Hi Tom,
My here document does not work in the function. Thanks so much for your help.
------------------------------------------------------
/exports/database/test: cat test
#!/bin/ksh
function pop_table
{
sqlplus -s scott/tiger@dbtest <<EOF
insert into emp_temp select * from emp;
exit;
EOF
}
###########################
pop_table
exports/database/test: ./test
./test[2]: syntax error at line 4 : `<<' unmatched
------------------------------------------------------
By the way, Han, thanks so much for the solution, it is better than creating view in the schema.
September 07, 2004 - 1:06 pm UTC
EOF in column 1 most likely.
Here document in function
Sean, September 07, 2004 - 2:23 pm UTC
Thanks so much Tom.
I missed your point. The same code works without function, but not in the function. Is it true that here document is not allowed in the function?
Thanks so much for your help.
September 07, 2004 - 2:49 pm UTC
it (the ksh -- nothing oracle here, this is ksh programming) wants the EOF in column 1 in the function.
why? because the makers of ksh said so.
#!/bin/ksh
function pop_table
{
sqlplus /<<EOF
select * from dual;
EOF
}
pop_table
Sqlplus "/@db as sysdba"
PM, November 15, 2004 - 4:25 pm UTC
Hello tom,
I have tried so many things for this. like we have so many databases and i need to execute a script against all the dbs and that required sys rights.
so i want to connect like sqlplus "/@db as sysdba". bt it is nt working . pls suggest the correct thing or some other workaround.
Thanks a lot !
November 15, 2004 - 9:11 pm UTC
that would open a security hole the size of a space shuttle or bigger in general. (hey, i'll just drop my laptop into your network, create a user account named after you and wah-lah......)
Sqlplus "/@db as sysdba"
PM, November 16, 2004 - 12:24 pm UTC
I cudnt understand ur reply. I think this is nt gonna work. anyways how can i execute the same script for multiple databases connected as sysdba.
Thanks
November 16, 2004 - 1:04 pm UTC
I cannot understand your typing at all. It is missing extremely important letters used in communication -- namely VOWELS.
This seems to be a recurring problem -- not sure what causes it, maybe a virus, maybe a failing keyboard. In any case, you might want to look into it -- your text is virtually unreadable as it stands!
My reply said "you do NOT want to do OS AUTHENTICATION over a network, it would open you to a slightly large HOLE".
Have you looked into job scheduling tools, such as Enterprise Manager, that can "vault" your credentials and allow you to accomplish this in a secure fashion?
Sqlplus "/@db as sysdba"
pm, November 16, 2004 - 8:43 pm UTC
Sorry for that, actually i wrote "nt = not" like shortcuts.
Again i am sorry about my previous communication.
Now i am starting my question.
Suppose i have multiple databases and i need to run one script against all the databases. So what can i do.
that is why i tried sqlplus "/ @db as sysdba ". I can understand your point it is security threat. but if i am connected as oracle user who is software owner and database too.
Waiting for your reply .
Thanks
November 16, 2004 - 11:44 pm UTC
the security thread is that if you allow remote os authentication, I can drop my LAPTOP into your NETWORK and become YOU. scary? or not?
like I said, have you looked at the tools that do this for you? you already own one!
Can pass any varible from pl/sql to shell
glenn sun, November 22, 2004 - 3:25 am UTC
this article is really useful for me, thx for anyone who left their wisdom at this website.
i have learned how to pass shell varibles to pl/sql by sql*plus. by the way, how can i pass out the varibles inside the pl/sql to shell?
November 22, 2004 - 7:47 am UTC
you cannot really, best you can do is have plsql create a small file with the "set" commands (a small shell script) and have the invoking shell script "source" it afterwards.
eg: you can use spool and dbms_output in sqlplus/plsql and then have the shell script source the resulting script with the environment variable settings.
passing value from sqlplus to shell
Santhosh, December 08, 2004 - 3:26 pm UTC
It worked for me, this is really nice, I should be thankful to TOM for posting answer on the web
Alternative if these do not work for ksh on AIX
Tony, April 05, 2006 - 12:06 am UTC
Gidday,
I have used the alternatives posted, along with a few others but all consistantly fail on AIX 5.3 in the ksh with:
0403-057 Syntax error at line nn : `<' is not matched.
This is returned when using <<EOF, << EOF, <<-EOF, <<! etc. and all of the permutations of syntax that preceed/follow.
If you are also finding this is a problem, I have managed to get around this by wrapping the sql in a process to returning a value e.g.
ORA_RC=`$ORACLE_HOME/bin/sqlplus /nolog <<EOF
connect /as sysdba
................
EOF`
Hope this has been of some help to someone as this little problemette had me going for a while.
Tony
April 05, 2006 - 5:37 pm UTC
this is very very standard stuff - I'd be very surprised if ksh is "broke" in this fashion.
hows about a cut and paste of the actually (tiny) ksh script you are trying.