Home » Infrastructure » Unix » Problem with shell unxi paramters (HP UX 11 oRACLE 9.2.0.6)
icon3.gif  Problem with shell unxi paramters [message #386077] Thu, 12 February 2009 09:14 Go to next message
Cesco
Messages: 1
Registered: February 2009
Location: Italy
Junior Member
Excuse me,
i have a problem with shell unix .
I write this script :
set -x
. /application/oracle/cfg/env_reporter.sh

ORACLE_SID=$1
TABLESPACE=$2
PATH_WORK=/export/home/oracle/script
PATH_WORK_LOG=/export/home/oracle/script/log
export ORACLE_SID CONNESSIONE_SYSTEM USER PWSD

$ORACLE_HOME/bin/sqlplus -s $USER/$PSWD@$1 << EOF
set head off
spool $PATH_WORK_LOG/rebuild_$1_$2.log
@$PATH_WORK/select_index.sql $2
exit
EOF

cat $PATH_WORK_LOG/rebuild_$1_$2.log |grep ORA-
echo $?
ls -l $PATH_WORK_LOG/rebuild_$1_$2.log
echo $?
for file in `cat $PATH_WORK_LOG/rebuild_$1_$2.log`
do
   $ORACLE_HOME/bin/sqlplus -s $USER/$PSWD@$1  << EOF
  *  $PATH_WORK/rebuild.sql `$file` *
    EOF
done

I have a problem with the "for" cicle because the shell doesn't give a value at variable "$file" .
The script rebuild.sql is :

set head off
set time off
set timing off
set feedback off
set verify off
alter index &file rebuild;
spool off;
exit

In the $PATH_WORK_LOG/rebuild_$1_$2.log
there are all the list of indexes that i want to rebuild .
Help me this a problem for me.
Thank you
Best regards
Francesco

Edit: Mahesh Rajendran
Added CODE tags

[Updated on: Thu, 12 February 2009 09:43] by Moderator

Report message to a moderator

Re: Problem with shell unxi paramters [message #386081 is a reply to message #386077] Thu, 12 February 2009 09:24 Go to previous messageGo to next message
BlackSwan
Messages: 22924
Registered: January 2009
Senior Member
1) Why are you rebuilding all the indexes?
2) It does not work because substitution does not work as you hope it does.
3) Why not use EXECUTE IMMEDIATE with PL/SQL procedure & eliminate shell script?

You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/
So we can help you


This can be done by only using SQL (no PL/SQL).
write SQL to create new SQL file which then gets invoked.

[Updated on: Thu, 12 February 2009 09:36]

Report message to a moderator

Re: Problem with shell unxi paramters [message #386083 is a reply to message #386077] Thu, 12 February 2009 09:42 Go to previous message
Mahesh Rajendran
Messages: 10629
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
As already said,
First make a compelling case to rebuild the indexes as a job.
Not useful in ***most*** cases.

But to answer your question,
A simple while loop should do.
This sample will make a sql*plus session for every loop which is bad. Please improvise the script on your own.
Instead just create your file with index rebuild sql (create sql from sql) and call that f1.sql within OEF.
f1.sql should contain all index rebuild entries. THis way you make only one sql*plus call.

And please format your posting with CODE tags.

Kaapi:ora magvivek$ cat f1
emp
dept 

Kaapi:ora magvivek$ cat somescript
cat f1 | while read fname
do
echo "working on "$fname
sqlplus -s scott/tiger@chum <<EOF
select count(*) from $fname;
exit;
EOF
done
Kaapi:ora magvivek$ ./somescript
working on emp

  COUNT(*)
----------
	14

1 row selected.

working on dept

  COUNT(*)
----------
	 4

1 row selected.


Previous Topic: Oracle patch for 9.2.0.8
Next Topic: Problems with dbca
Goto Forum:
  


Current Time: Thu Oct 30 07:58:27 CDT 2014

Total time taken to generate the page: 0.07949 seconds