Inside a Shell script

  • One line command:
1# Set the SID 
2ORAENV_ASK=NO
3export ORACLE_SID=HANA
4. oraenv
5
6# Trigger oneline command
7echo -e "select inst_id, instance_name, host_name, database_status from gv\$instance;" | sqlplus -S / as sysdba
  • In bash script:
 1su - oracle -c '
 2export SQLPLUS="sqlplus -S / as sysdba"
 3export ORAENV_ASK=NO;
 4export ORACLE_SID='${SID}';
 5. oraenv | grep -v "remains";
 6
 7${SQLPLUS} <<EOF2
 8set lines 200 pages 2000;
 9select inst_id, instance_name, host_name, database_status from gv\$instance;
10exit;
11EOF2
12
13unset ORAENV_ASK;
14'

Inside SQL Prompt

1-- with an absolute path 
2@C:\Users\Matthieu\test.sql 
3
4-- or trigger from director on which sqlplus was launched
5@test.sql
6
7-- START syntax possible as well
8START test.sql

Variables usages

1-- User variable (if not define, oracle will prompt)
2SELECT * FROM &my_table;
3
4-- Prompt user to set a variable
5ACCEPT my_table PROMPT "Which table would you like to interrogate ? "
6SELECT * FROM $my_table;

Some Examples

  • Example of Shell script to launch sqlplus command:
 1export ORACLE_SID=SQM2DWH3
 2
 3echo "connect ODS/ODS
 4BEGIN
 5ODS.PURGE_ODS.PURGE_LOG();
 6ODS.PURGE_ODS.PURGE_DATA();
 7END;
 8/" | sqlplus /nolog
 9
10echo "connect DSA/DSA
11BEGIN
12DSA.PURGE_DSA.PURGE_LOG();
13DSA.PURGE_DSA.PURGE_DATA();
14END;
15/" | sqlplus /nolog
  • Example of script to check tablespaces.sh
 1#!/bin/ksh
 2
 3sqlplus -s system/manager <<!
 4SET HEADING off;
 5SET PAGESIZE 0;
 6SET TERMOUT OFF;
 7SET FEEDBACK OFF;
 8SELECT df.tablespace_name||','||
 9       df.bytes / (1024 * 1024)||','||
10       SUM(fs.bytes) / (1024 * 1024)||','||
11       Nvl(Round(SUM(fs.bytes) * 100 / df.bytes),1)||','||
12       Round((df.bytes - SUM(fs.bytes)) * 100 / df.bytes)
13  FROM dba_free_space fs,
14       (SELECT tablespace_name,SUM(bytes) bytes FROM dba_data_files GROUP BY tablespace_name) df
15 WHERE fs.tablespace_name (+)  = df.tablespace_name
16 GROUP BY df.tablespace_name,df.bytes
17 ORDER BY 1 ASC;
18quit
19!
20
21exit 0

 1#!/bin/ksh
 2
 3sqlplus -s system/manager <<!
 4
 5set pagesize 60 linesize 132 verify off
 6break on file_id skip 1
 7
 8column file_id heading "File|Id"
 9column tablespace_name for a15
10column object          for a15
11column owner           for a15
12column MBytes          for 999,999
13
14select tablespace_name,
15'free space' owner, /*"owner" of free space */
16' ' object,         /*blank object name */
17file_id, /*file id for the extent header*/
18block_id, /*block id for the extent header*/
19CEIL(blocks*4/1024) MBytes /*length of the extent, in Mega Bytes*/
20from dba_free_space
21where tablespace_name like '%TEMP%'
22union
23select tablespace_name,
24substr(owner, 1, 20), /*owner name (first 20 chars)*/
25substr(segment_name, 1, 32), /*segment name */
26file_id, /*file id for extent header */
27block_id, /*block id for extent header */
28CEIL(blocks*4/1024) MBytes /*length of the extent, in Mega Bytes*/
29from dba_extents
30where tablespace_name like '%TEMP%'
31order by 1, 4, 5
32/
33
34quit
35!
36
37exit 0

SPOOL to write on system

  • from sqlplus:
1SQL> SET TRIMSPOOL on
2SQL> SET LINESIZE 1000
3SQL> SPOOL /root/output.txt
4SQL> select RULEID as RuleID, RULENAME as ruleName,to_char(DBMS_LOB.SUBSTR(EPLRULESTATEMENT,4000,1() as ruleStmt from gep_rules;
5SQL> SPOOL OFF
  • from script.sql:
1SET TRIMSPOOL on
2SET LINESIZE 10000
3SPOOL resultat.txt
4ACCEPT var PROMPT "Which table do you want to get ? "
5SELECT * FROM &var;
6SPOOL OFF

Generate DATA

  • Duplicate table to fill up tablespace or generate fake data:
1SQL> Create table emp as select * from employees; 
2SQL> UPDATE emp SET LAST_NAME='ABC';
3SQL> commit;