Oracle timed query results output to the specified log file

Recently there is a monitoring project to collect database information, the original plan is to write a SQL script, each script put a query statement and then through the operating system layer SU to the Oracle user through Sqlpus execute this. sql, and then add to crontab timed execution. But this problem has a disadvantage, is to establish a connection too much, if every 5 minutes to collect data that means 5 minutes through the Sqlplus landing database and then exit. Then think how at the database layer every 5 minutes to execute the query and not the operating system frequently through the sqlplus landing, the specific idea of writing a stored procedure through the stored procedure to output the query results to the specified log file. And then through the job scheduled scheduling.
1. Establish dir to grant appropriate permissions
sql> Create or replace directory monitor as '/oracle/monitor ';
Sql> Grant Read,write on the directory Monitor to system;
Grant succeeded.
Sql> Grant Read,write on the directory Monitor to SYS;
Grant succeeded.
2. CREATE PROCEDURE
sql> Create or replace procedure Pro_inst_status as
2 v_tmp varchar2 (60);
3 V_out_file Utl_file.file_type;
4 v_file_name varchar2 (30);
5 begin
6 V_file_name: = ' inst_status.log ';
7 V_out_file: = Utl_file.fopen (' MONITOR ', V_file_name, ' W ');
8 if (Utl_file.is_open (v_out_file)) Then
9 Select To_char (sysdate, ' yyyy-mm-dd hh24:mi:ss ') | | ', ' | | instance_name| | ', ' | | Status
Ten into v_tmp from V$instance;
Utl_file.put_line (V_out_file, v_tmp);
Else
Raise_application_error ( -20001, ' Inst file Open failure! ');
+ End If;
Utl_file.fclose (V_out_file);
+ End Pro_inst_status;
17/

Procedure created.
3, the implementation of procedure
sql> exec pro_inst_status;
BEGIN Pro_inst_status; END;

*
ERROR at line 1:
Ora-29283:invalid file operation
Ora-06512:at "SYS. Utl_file ", line 536
Ora-29283:invalid file operation
Ora-06512:at "Sys.pro_inst_status", line 7
Ora-06512:at Line 1
Error because Dir was not built. Open the building.
[Email protected] ~]# cd/oracle/
[Email protected] oracle]# mkdir Monitor
[Email protected] oracle]# chown-r oracle:oinstall monitor/

sql> exec pro_inst_status;

PL/SQL procedure successfully completed.

sql> exec pro_inst_status;

PL/SQL procedure successfully completed.
4. View build Log
Sql> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0-64bit Production
With the partitioning, OLAP, Data Mining and Real application testing options
[Email protected] oracle]$ CD monitor/
[Email protected] monitor]$ ls-ltr
Total 4
-rw-r--r--1 Oracle Oinstall 13:26 inst_status.log
[email protected] monitor]$ cat Inst_status.log
2016-08-31 13:26:04, IDB, OPEN
[Email protected] monitor]$ Sqlplus/as SYSDBA

Sql*plus:release 11.2.0.4.0 Production on Wed 31 13:27:17 2016
Copyright (c) 1982, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0-64bit Production
With the partitioning, OLAP, Data Mining and Real application testing options
sql> exec pro_inst_status;
PL/SQL procedure successfully completed.
Sql> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0-64bit Production
With the partitioning, OLAP, Data Mining and Real application testing options
[email protected] monitor]$ cat Inst_status.txt
2016-08-31 13:27:21, IDB, OPEN
[Email protected] monitor]$ Sqlplus/as SYSDBA
Sql*plus:release 11.2.0.4.0 Production on Wed 31 13:36:53 2016
Copyright (c) 1982, Oracle. All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0-64bit Production
With the partitioning, OLAP, Data Mining and Real application testing options
5, build job to do timed work
sql> variable monitor_job number;
Sql> begin
2 Dbms_job.submit (: Monitor_job, ' pro_inst_status; ', sysdate, ' sysdate+1/288 ');
3 END;
4/

PL/SQL procedure successfully completed.

Sql> desc dba_jobs
Name Null? Type
----------------------------------------- -------- ----------------------------
JOB not NULL number
Log_user not NULL VARCHAR2 (30)
Priv_user not NULL VARCHAR2 (30)
Schema_user not NULL VARCHAR2 (30)
Last_date DATE
Last_sec VARCHAR2 (8)
This_date DATE
This_sec VARCHAR2 (8)
Next_date not NULL DATE
Next_sec VARCHAR2 (8)
Total_time number
Broken VARCHAR2 (1)
INTERVAL not NULL VARCHAR2 (200)
Failures number
What VARCHAR2 (4000)
NLS_ENV VARCHAR2 (4000)
Misc_env RAW (32)
INSTANCE number

Sql> Select Job, what from Dba_jobs;

JOB what
---------- ----------------------------------------------------------------------------------------------------
4001 wwv_flow_cache.purge_sessions (p_purge_sess_older_then_hrs = 24);
4002 Wwv_flow_mail.push_queue (wwv_flow_platform.get_preference (' smtp_host_address '), Wwv_flow_platform.get
_preference (' Smtp_host_port '));

7 Pro_inst_status;
6 Dbms_refresh.refresh (' "I6000_sys". Sys_v_userroleorgrs "');

6. Running Job
Sql> begin
2 Dbms_job.run (7);
3 END;
4/

PL/SQL procedure successfully completed.

7. View running Results
[Email protected] monitor]# tail-f Inst_status.log
2016-08-31 13:45:45, IDB, OPEN
Tail:inst_status.log:file truncated
2016-08-31 13:50:45, IDB, OPEN
Tail:inst_status.log:file truncated
2016-08-31 13:55:46, IDB, OPEN
Tail:inst_status.log:file truncated
2016-08-31 14:00:46, IDB, OPEN
Tail:inst_status.log:file truncated
2016-08-31 14:05:46, IDB, OPEN
Tail:inst_status.log:file truncated
2016-08-31 14:10:46, IDB, OPEN

Oracle timed query results output to the specified log file