News

  1. Home
  2. All posts
All posts, Oracle, SQL by Team Ajara

Running oracle sqlplus command in the background

How to run the sqlplus command in the background with one scenario.

 

For all long running adhoc sql scripts , we can run the script in the background.

For example one big table/index stats are stale or statistics were not gathered at all,
and found there is a performance degradation ,then we need to run the stats immediately.

For example:

SQL> select owner,table_name,last_analyzed from dba_tables where owner='STGUSR' and table_name='SUMM_TAB';
 
OWNER    TABLE_NAME                     LAST_ANAL
-------  -----------------------       ---------
STGUSR   SUMM_TAB


SQL>select owner,INDEX_NAME,TABLE_NAME,STALE_STATS from DBA_IND_STATISTICS 
where owner='STGUSR' and index_name='IND_STG';

OWNER        INDEX_NAME         TABLE_NAME         STA
--------- ------------------ ------------------    ---
STGUSR         IND_STG           STG_TAB          YES
 

Create one sql file with below commands.

File name:  ORDW1_STG_TAB.sql
alter session set sort_area_size=512000000;

alter session set db_file_multiblock_read_count=1024;

set time on timing on echo on

execute dbms_stats.gather_table_stats(ownname => ‘STGUSR’, tabname =>’SUMM_TAB’,
estimate_percent => 100,granularity=>’ALL’, cascade=>TRUE,
method_opt => ‘FOR ALL COLUMNS SIZE AUTO’,degree=>10);

execute dbms_stats.gather_table_stats(ownname => ‘STGUSR’,tabname => ‘STG_TAB’,
estimate_percent =>100,granularity=>’ALL’, cascade=>TRUE,
method_opt => ‘FOR ALL COLUMNS SIZE AUTO’,degree=>10);

 
 
Ho to run the above script in the background?
nohup sqlplus db_admin/******@ORDW1 < ORDW1_STG_TAB.sql >ORDW1_STG_table_stats.log & 
Here we are redirecting the input of SQL file ORDW1_STG_TAB.sql 
and writing the output into ORDW1_STG_table_stats.log logfile.
Above command meaning in detail
  • nohup                                                => no hangup
  • sqlplus                                               =>provides access to oracle RDBMS
  • db_admin/******@ORDW1        => connect string. connectivity to ORDW1 database, username=db_admin
  • <     (lessthan symbol)                  => Input Redirection
  • ORDW1_STG_TAB.sql                => sql filename
  • >     (greaterthan symbol)           => Output redirection
  • ORDW1_STG_table_stats.log   => logfile
  • &                                                            =>  ampersand(&)  is used for placing the command in background
 
see also:

How to run scp in the background on exadata server ?
http://oracle.com