Oracle-related commands commonly used in [Shell programming] programming

This article mainly describes the Oracle-related commands commonly used in shell programming.

1. Sqlplus-l/-s Parameters

Sqlplus-l user/-S User/password #-s to set silent mode, commonly used

2. Set related commands

set timing on #显示SQL语句的运行时间. The default value is off. Can be used for performance analysis of SQL execution efficiency. Set autotrace on #允许对执行的SQL进行分析set trimout on # Remove trailing spaces per line of standard output, default to offset Trimspool on #去除重定向 (SPOOL) output trailing spaces per line, default to offset EchoOn #不显示START启动的脚本中的每个SQL命令, the default is onset feedback on #设置显示"xx row has been selected", which shows the number of records processed by this SQL command, the default is onset COLSEP'|'#输出列之间的分隔符. Set heading off #输出域标题, default is onset pagesize0#输出每页行数, the default is 24, in order to avoid paging, can be set to 0. Set Linesize #设置每行显示字符数, default is 80, the maximum value is 999set numwidth A#输出NUMBER类型域长度, when the default is 10set termout off #常用SPOOL xxx, the display of the report on the screen is turned off to save time, and the default value is onset serveroutput on #设置允许显示输出类似DBMS_ Outputset Verify of #可以关闭和打开提示确认信息old 1 and new 1 display.
Show All #显示当前所有参数情况

3. Save SQL Execution Results

Method One: Use the spool command

Sqlplus-s/As Sysdba << EOF  echo  off;   0 ;    Max ;  Spool temp.txt;   Select username from dba_users;  Spool off;  Exiteof

Method Two: Using redirection

Echo "set echo off;">>${operate_sql}Echo "set pagesize 0;">>${operate_sql}Echo "set linesize;">>${operate_sql}Echo "select username from dba_users;">>${operate_sql}Echo "Exit">>${operate_sql}sqlplus-s/as sysdba < ${operate_sql} > ${operate_sql_result}

Method Three: Using redirection

Sqlplus-s/As Sysdba > Temp.txt << EOF  echo  off;   0 ;    Max ;   Select username from dba_users;  Exiteof

4. Common SQL

Select username from dba_users; #dba用户登录 # Gets the number of user records one Select ' Analyze Table '| | t.table_name| | ' compute statistics; ' from user_tables t; #先刷新 Select table_name,num_rows from User_tables; #获取用户记录数二 Select Count (*) from TABLE_NAME;

5. Determine the Oracle instance startup status

PS grep grep grep &>/dev/null  #通过pmon进程判断 [$0 ]  01  # Return 0 is normal, 1 means close

6. Determine Oracle Listening status

PS grep grep grep &>/dev/null  #通过tnslsnr进程判断 [$0 ]  01  # Return 0 is normal, 1 means close

Oracle-related commands commonly used in [Shell programming] programming