Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

Execute Oracle gather stats via shell script

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Execute Oracle gather stats via shell script
# 1  
Old 02-25-2015
Execute Oracle gather stats via shell script
Hi ,

I am trying to automate a gather stats in shell script

Code:
#!/usr/bin/ksh

export ORACLE_HOME=/orcl/app/oracle/product/11.2.0.1/db_1
export PATH="$PATH:$ORACLE_HOME/bin"
export LD_LIBRARY_PATH="$LD_LIBRARY_PATH:$ORACLE_HOME/lib32"
export TNS_ADMIN=/opt/netprobe/config
username=username
password=password

partname=$(
set pagesize 0 feedback off verify off heading off echo off;
sqlplus -s $username/$password@database<<pname
select A.PARTITION_NAME from DBA_TAB_STATISTICS  A where  table_name='TABLE_NAME_1' AND  STALE_STATS='YES';
pname
)

sqlplus -s $username/$password@database<<gather
exec dbms_stats.gather_table_stats(ownname=>'XYZ', tabname =>'TABLE_NAME_1', partname=>'"$partname"',granularity=>'PARTITION');
gather

But I am getting this error
Code:
ERROR:
ORA-01756: quoted string not properly terminated

How do I reference the variable partname's value in the here doc?

Thanks!
Last edited by rbatte1; 02-25-2015 at 10:34 AM.. Reason: Added CODE tags for error
# 2  
Old 02-26-2015
Quote:
Originally Posted by neil.k
Code:
partname=>'"$partname"'

How do I reference the variable partname's value in the here doc?
I have quoted the relevant part of your script. You seem to think that a single-quoted string is somehow "stronger quoted" than a double-quoted one and that differently quoted strings can be nested. This is a - common - misconception.

Code:
wrong concept:
'........"............................"........'
 <------------- single-quoted ---------------->
          <- single+double-quoted---->"
'<----->    single-quoted only areas   <------>

In fact it is like this: the shell maintains a flag "inside string y/n", which is flipped when a (fitting) quote is encountered. The first time the shell "sees" a quote char (single or double) it flips the flag to "y" and then waits until it gets the next fitting quote-char in the input. When getting this it flips the flag back. Everything read in the meantime is considered to be inside this string.

Code:
correct concept:
start reading, flag="outside string"               right quote, flip flag to
|                                                  |  "outside string" again
|   flag="inside (single-quoted) string"           |
|   |                                              |
|   |        |wrong quotes, flag unchanged|        v
v   v        v                            v
....'........"............................"........'....>
     <------------- single-quoted ---------------->

For your problem this means to do it like this:

Code:
partname=>"\'${partname}\'"

Notice the usage of escape-characters: they will preserve the single quotes for one pass of shell interpretation so that the resulting string will be a singly-quoted string.

Btw.: this
Code:
partname=$(
set pagesize 0 feedback off verify off heading off echo off;
sqlplus -s $username/$password@database<<pname
select A.PARTITION_NAME from DBA_TAB_STATISTICS  A where  table_name='TABLE_NAME_1' AND  STALE_STATS='YES';
pname
)

may work but probably not in the way you intend it to do so. Consider changing the line order:

Code:
partname=$(
sqlplus -s $username/$password@database<<pname
set pagesize 0 feedback off verify off heading off echo off;
select A.PARTITION_NAME from DBA_TAB_STATISTICS  A where  table_name='TABLE_NAME_1' AND  STALE_STATS='YES';
pname
)


I hope this helps.

bakunin
Last edited by bakunin; 02-26-2015 at 06:07 AM..
These 2 Users Gave Thanks to bakunin For This Post:
neil.k rbatte1
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. UNIX for Beginners Questions & Answers

Shell script to execute Oracle procedure and trigerring email on success and failure

Here is the shell script which need to trigger a stored procedure and when the record count is 0 then it should capture in the log that 0 duplicate records are found and if it's more than 0 then log with no of records. Also if there is any sqlerror then it should write the same in the log file and... (17 Replies)
Discussion started by: senmng
17 Replies

2. UNIX for Beginners Questions & Answers

Create a shell script to gather user account information and displays the result to administrator

I want to create a shell script to gather user account information and displays the result to administrator. I have created a script but its showing all the information when i search for username like: amit@mx:~$ ./uinfo.sh amit Username : amit User Info ... (2 Replies)
Discussion started by: amit1986
2 Replies

3. Shell Programming and Scripting

Need to execute Oracle relocation command in shell script

Hello, I need to execute below command in shell script srvctl relocate service -d $database -s $service -i $avail -t $pref -f and also need to get the errors ,if any,in another file. What's the right way to execute such commands in shell script? Best regards, Vishal (1 Reply)
Discussion started by: Vishal_dba
1 Replies

4. Shell Programming and Scripting

Script to load daily average I/O stats from a .ksh file into Oracle db

Hi can anyone help me with a script to load output of the .ksh file into an Oracle database. I have attached sample output of the information that i need to load to the database (2 Replies)
Discussion started by: LucyYani
2 Replies

5. Shell Programming and Scripting

Shell Script to execute Oracle query taking input from a file to form query

Hi, I need to query Oracle database for 100 users. I have these 100 users in a file. I need a shell script which would read this User file (one user at a time) & query database. For instance: USER CITY --------- ---------- A CITY_A B CITY_B C ... (2 Replies)
Discussion started by: DevendraG
2 Replies

6. Shell Programming and Scripting

Execute a shell script from Oracle procedure

Hi Gurus, Want to execute a shell script from a oracle procedure and get the status of the same, any assistance in this regard will be appreciated. proc_data.sh is script name which I want to execute from oracle procedure It should work something like below procedure test begin... (1 Reply)
Discussion started by: palanisvr
1 Replies

7. Shell Programming and Scripting

gather, modify stats, email to self

I have a range of log files from rsync on an Ubuntu 10.04 server. At the bottom of these files, I have "--stats" -- a rsync switch -- which produces interesting information/statistics. Steps the script will take: take the the lines that contain the words "sent, total size, bytes" put... (3 Replies)
Discussion started by: ScannerDarkly
3 Replies

8. Shell Programming and Scripting

How to execute an Oracle procedure using shell

Hi , i have created an .sh file that has the following code: #!/bin/ksh sqlplus -s p1istuat/p1istuat@CWS_IST6 @Procedure_Execute.sql & sqlplus -s p1istuat/p1istuat@CWS_IST6 << EOF exit EOF The mentioned Procedure_Execute.sql file inside has the following code: exec TEST; ... (5 Replies)
Discussion started by: vins_san
5 Replies

9. Shell Programming and Scripting

script to gather weblogic jvm heap size stats

Hello, has anyone written something that will monitor/gather weblogic heap info ? I need to gather size, high/low stats to a file that I can upload to a speadsheet thanks for your help! (2 Replies)
Discussion started by: galenw
2 Replies

10. Shell Programming and Scripting

Execute an Oracle stored procedure from a shell scrip

Here is a snippet of my code: if then echo "\n Deleting all reports older than 24 hours. \n" >> $logfile ls -l $FileName >> $logfile ... (1 Reply)
Discussion started by: mh53j_fe
1 Replies
Login or Register to Ask a Question