第32次活动：2017-11-13（星期一晚上7:00-9:30）
64位Linux要做Filewatcher一定要：# ls -l libpam.so*
lrwxrwxrwx. 1 root root 16 10月6 00:23 libpam.so.0 -> libpam.so.0.82.2
-rwxr-xr-x. 1 root root 552807月 10 18:59 libpam.so.0.82.2
# ln -s libpam.so.0.82.2 libpam.so
# pwd
/lib64
#
关于总file_watcher_job和原来就有的file_watcher_scheduler（而且都是关闭或disabled）：
--------
SYS:
select* from dba_scheduler_credentials;
select* from dba_scheduler_file_watchers;
begin
dbms_scheduler.create_file_watcher(
file_watcher_name => 'filewatcher1',
directory_path => '/home/oracle/myfile',
file_name => 'botang*.txt',
credential_name => 'cred1');
end;
grant execute on filewatcher1 to hr;
grant execute on SYS.SCHEDULER_FILEWATCHER_RESULT to hr;
grant create external job to hr;
grant create job to hr;
HR:
create table hr.tfilewatcher1 ( avarchar2(200)) ;
create or replace procedure hr.procfilewatcher ( p_1 SYS.SCHEDULER_FILEWATCHER_RESULT)
is
begin
insert into hr.tfilewatcher1values ( to_char( p_1.file_timestamp,'YYYY-MM-DD:HH24:MI:SS') ||' '
||p_1.directory_path||' '
||p_1.actual_file_name||' '
||p_1.file_size) ;
commit;
end;
----
begin
dbms_scheduler.create_program(
program_name => 'HR.program10',
program_type => 'STORED_PROCEDURE',
program_action => 'HR.procfilewatcher',
number_of_arguments => 1,
enabled => false);
end;
----
begin
dbms_scheduler.define_metadata_argument(
program_name => 'HR.program10',
metadata_attribute => 'event_message',argument_position => 1 ) ;
end;
-----
begin
dbms_scheduler.enable('HR.program10');
end;
----
BEGIN
DBMS_SCHEDULER.CREATE_JOB(
jOB_NAME=> 'hr.job10',
PROGRAM_NAME=> 'hr.program10',
EVENT_CONDITION => 'tab.user_data.file_size > 10',
QUEUE_SPEC=> 'filewatcher1',
AUTO_DROP=> FALSE,
ENABLED=> true);
END;
begin
dbms_scheduler.set_attribute('hr.job10','parallel_instances',true);
end;
select* from hr.tfilewatcher1;
Remote Scheduler：
$ sqlplus /nolog
SQL*Plus: Release 11.2.0.1.0 Production on Mon Nov 13 20:09:24 2017
Copyright (c) 1982, 2009, Oracle.All rights reserved.
SQL> conn / as sysdba
Connected.
SQL> show parameter dispa
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
dispatchers string (PROTOCOL=TCP) (SERVICE=orclXD
B)
max_dispatchers integer
SQL> alter system set dispatchers=
2' (PROTOCOL=TCP) (dispatchers=3)';
System altered.
SQL> show parameter dispa
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
dispatchers string (PROTOCOL=TCP) (dispatchers=3
)
max_dispatchers integer
SQL> !lsnrctl services
LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 13-NOV-2017 20:09:58
Copyright (c) 1991, 2009, Oracle.All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=station90.example.com)(PORT=1521)))
Services Summary...
Service "+ASM" has 1 instance(s).
Instance "+ASM", status READY, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:259 refused:0 state:ready
LOCAL SERVER
Service "orcl.example.com" has 1 instance(s).
Instance "orcl", status READY, has 4 handler(s) for this service...
Handler(s):
"DEDICATED" established:259 refused:0 state:ready
LOCAL SERVER
"D000" established:0 refused:0 current:0 max:1022 state:ready
DISPATCHER <machine: station90.example.com, pid: 27496>
(ADDRESS=(PROTOCOL=tcp)(HOST=station90.example.com)(PORT=22919))
"D001" established:0 refused:0 current:0 max:1022 state:ready
DISPATCHER <machine: station90.example.com, pid: 8105>
(ADDRESS=(PROTOCOL=tcp)(HOST=station90.example.com)(PORT=13743))
"D002" established:0 refused:0 current:0 max:1022 state:ready
DISPATCHER <machine: station90.example.com, pid: 8110>
(ADDRESS=(PROTOCOL=tcp)(HOST=station90.example.com)(PORT=32283))
Service "orclXDB.example.com" has 1 instance(s).
Instance "orcl", status READY, has 0 handler(s) for this service...
The command completed successfully
SQL> show parameter shared
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
hi_shared_memory_address integer 0
max_shared_servers integer
shared_memory_address integer 0
shared_pool_reserved_size big integer 36909875
shared_pool_size big integer 0
shared_server_sessions integer
shared_servers integer 1
SQL> alter system set shared_servers=3;
System altered.
SQL>
走以下脚本会多一个人：
SQL> @?/rdbms/admin/prvtrsch.plb
BEGIN
DBMS_XDB.SETHTTPPORT(8888);
END;
SELECT DBMS_XDB.GETHTTPPORT() FROM DUAL;
BEGIN
DBMS_SCHEDULER.SET_AGENT_REGISTRATION_PASS('buhaoji');
END;
组策略：
select* from dba_scheduler_external_dests;
DECLARE
versionnum VARCHAR2(30);
BEGIN
versionnum := DBMS_SCHEDULER.GET_AGENT_VERSION('192.168.0.199');
DBMS_OUTPUT.PUT_LINE(versionnum);
END;
grant all on station199 to hr;
select* from dba_scheduler_external_dests;
select* from dba_scheduler_dests;
begin
dbms_scheduler.drop_agent_destination(destination_name => 'station14');
end;
DECLARE
versionnum VARCHAR2(30);
BEGIN
versionnum := DBMS_SCHEDULER.GET_AGENT_VERSION('192.168.0.199');
DBMS_OUTPUT.PUT_LINE(versionnum);
END;
----
begin
dbms_scheduler.create_file_watcher(
file_watcher_name => 'filewatcher2',
directory_path => 'c:\Users\oracle\yourfile',
file_name =>'botang*.txt',
credential_name => 'cred1',
destination => 'STATION199');
end;
grant execute on filewatcher2 to hr;
select* fromhr.tfilewatcher1;
BEGIN
DBMS_SCHEDULER.CREATE_JOB(
jOB_NAME=> 'hr.job12',
PROGRAM_NAME=> 'hr.program10',
EVENT_CONDITION => 'tab.user_data.file_size > 10',
QUEUE_SPEC=> 'filewatcher2',
AUTO_DROP=> FALSE,
ENABLED=> true);
END;
begin
dbms_scheduler.set_attribute('hr.job12','parallel_instances',true);
end;
页:
[1]