Under UNIX, let ORACLE periodically execute *.sql file

ORACLE database comes with DBMS_JOB function can realize the execution of PL /SQL stored procedures, but if the SQL statement is very complicated, SQL statements are many, and often need to change the SQL statement The way of writing, using the method of writing PL /SQL stored procedures and then re-execution will be more cumbersome. Moreover, some UNIX system administrators do not write PL/SQL stored procedures, so I introduced a simple shell program to implement a *.sql file on a UNIX machine with ORACLE SERVER or CLIENT.

First we connect to the destination database on a UNIX machine with ORACLE SERVER or CLIENT installed:

$sqlplus username/password@servie_name

If you can successfully enter SQL> state, And execute a simple SQL statement

SQL> SELECT SYSDATE FROM DUAL;

indicates that the connection is successful, otherwise check if servie_name is correctly defined in /$ORACLE_HOME/network/admin/tnsnames.ora.

Whether /etc/hostname contains the host name of the destination database, etc. (other network checks are not listed here in detail).

Next run the test SQL statement under the scott user: scott_select.sql

SQL> SELECT D.DNAME, E.ENAME, E.JOB, E.HIREDATE FROM EMP E, DEPT D WHERE TO_CHAR(E.HIREDATE, 'YYYY')='1981' AND E.DEPTNO=D.DEPTNO;

Then write a shell file like scott_select.sh in the directory /oracle_backup/bin/Br>

--------------------------------------------- --------------------------- su - oracle -c "sqlplus scott/tiger@servie_name"<<EOF spool /oracle_backup/Log/scott_select.txt; @/oracle_backup/bin/scott_select.sql; spool off; exit; ----------------------------- --------------------------------------------

Description:

Spool statement output the execution result of scott_select.sql statement to /oracle_backup/log/scott_select.txt file

@symbol is execute /oracle_backup/bin/scott_select.sql file

You can store multiple SQL statements such as DML and DDL in the *.sql file to be executed.

Change the attribute of scott_select.sh to 755, you can execute

$chmod 755 /oracle_backup/bin/scott_select.sh

This way, UNIX system administrator (root privileges) You can use the crontab command to add scott_select.sh to the timer operation queue.

Or directly edit the configuration file under OS:

Sun Solaris file /var/spool/cron/crontabs/root Linux file /var/spool/cron/root

Add a line after the root file (meaning: execute scott_select.sh at 4:40 on the 18th of each month)

40 4 18 * * /oracle_backup/bin/scott_select.sh

Timetable In order: minutes (0— 59) hours (0— 23) dates (1— 31) months (1— 12) days of the week (0— 6)

You can combine them according to different needs .

Restart the timing service of the OS to make the newly added task take effect.

Sun Solaris #/etc/rc2.d/S75cron stop #/etc/rc2.d/S75cron start Linux #/etc/rc.d/init.d/crond restart

This way The ORACLE database will periodically execute the scott_select.sql file and output the result to the OS file scott_select.txt.

If we want to write or modify the scott_select.sql file, just edit it.