Shell script for Oracle data export

# Set the execution encoding format to prevent garbled Characters During Chinese Export

Export NLS_LANG = "AMERICAN_AMERICA.UTF8"

# Load the Oracle client program to the environment variable. Note: This line must be added if the script is placed in the scheduled task for execution.

Export ORACLE_HOME =/usr/lib/oracle/xe/app/oracle/product/10.2.0/client

# Load oracle databases

Export LD_LIBRARY_PATH = $ ORACLE_HOME/lib: $ LD_LIBRARY_PATH

# Loading oracle listeners

Export TNS_ADMIN =/usr/lib/oracle/xe/app/oracle/product/10.2.0/client

# Load the bin directory of oracle

Export PATH = $ PATH: $ ORACLE_HOME/bin

# Define the exported file name. I have added the time here.

Filename = filename. 'date-d yesterday + % Y % m % d'

# Setting export parameters

# Echo off does not display the executed SQL content

# Newpage 0

# Space 0

# Pagesize 0 the number of output lines per page is 24 by default

# Line 5000 sets the maximum number of words in each line to prevent line breaks

# Whether to display the processing result of feed off

# Head off whether the field name is displayed

# Trimspool on remove redirection (spool) output the trailing space of each row is on by default

Echo set echo off newpage 0 space 0 pagesize 0 line 5000 feed off head off trimspool on> tem001. SQL

Echo SPOOL/home/usr/data/$ filename> tem001. SQL
Echo "select * from tablename;"> tem001. SQL
Echo spool off> tem001. SQL
Echo quit> tem001. SQL
Sqlplus username @ database name/password @ tem001. SQL
Rm-fr tem001. SQL