Scribd
Upload
339 views1 page

Oracle SQL Script Execution Guide

The document outlines steps to create a shell script that accepts a SQL file and executes it against a database to generate statistics gathering scripts. It details setting environment variables, running the shell script with a SQL file argument to generate a gather.sql script, and adding a crontab entry to automatically run the gather.sql script on a schedule.

Uploaded by

Rrohit Sawney
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as TXT, PDF, TXT or read online on Scribd
339 views1 page

Oracle SQL Script Execution Guide

The document outlines steps to create a shell script that accepts a SQL file and executes it against a database to generate statistics gathering scripts. It details setting environment variables, running the shell script with a SQL file argument to generate a gather.sql script, and adding a crontab entry to automatically run the gather.sql script on a schedule.

Uploaded by

Rrohit Sawney
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as TXT, PDF, TXT or read online on Scribd
You are on page 1/ 1

Step 1: Create a shell script to accept a sql file and execute against a particular

database. Make necessary changes to the ORACLE_SID, ORACLE_BASE, ORACLE_HOME


parameters.

$ vi sql.sh
export ORACLE_SID=transprd
export ORACLE_BASE=/u00/app/oracle
export ORACLE_HOME=/u00/app/oracle/product/11.2.0.3/db_1
export
PATH=/u00/app/oracle/product/11.2.0.3/db_1/bin:/usr/kerberos/bin:/usr/local/bin:/bi
n:/usr/bin:/home/oracle/bin:/u00/app/oracle/product/11.2.0.3/db_1/bin:.
sqlplus sthomas/voyage123 << EOF > /home/oracle/st/gath.log
@$1
exit
EOF

Step 2: Change the permission for the shell script


$ chmod 744 sql.sh
Step 3: Create a sql script file to generate the scripts for gather statistics
command. Make necessary changes to the table names mentioned in red color below.
$ cat cr_gather.sql
set pages 100
set lines 250
set echo off feedback off heading off
spool gather.sql
select 'SET ECHO ON FEEDBACK ON TIMING ON' FROM DUAL;
select 'exec dbms_stats.gather_table_stats (ownname => ''' || owner ||''', tabname
=> '''||table_name||''', estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE, cascade
=> TRUE,method_opt => '''||' FOR ALL COLUMNS SIZE AUTO'''||', degree => 8);' from
dba_tables
where table_name in ('EMP', 'DEPT');
spool off
Step 4: Run the shell script with argument as the sql script file (cr_gather.sql).
This will create a gather.sql script file in the same directory.
$ sql.sh cr_gather.sql
Step 5: Add a crontab entry using crontab �e command with required execution
timings.
01 23 * * * /home/oracle/st/sql.sh /home/oracle/st/gather.sql >
/home/oracle/st/cron.log

You might also like