2021-11-24

DB Link creation with direct insert

insert into sys.link$ (OWNER#, NAME, CTIME, HOST, USERID, PASSWORD, FLAG, AUTHUSR, AUTHPWD, PASSWORDX, AUTHPWDX)
SELECT user_id 
 ,'DB_LINK_NAME'
 ,SYSDATE 
 ,'CONNECTION_STRING',
 'TARGET_DATABASE_USERNAME',
 null, '2', null, NULL
 ,'hashed password can be extracted from sys.link$.PASSWORDX'
 , NULL
FROM dba_users
WHERE username = 'LINK_OWNER' -- DB link owner

2021-11-08

Async using dbms_scheduler package

Simple async procession in Oracle can be implemented with dbms_scheduler package

-- dbms_scheduler.create_job makes explicit commit!!!
dbms_scheduler.create_job(  
         job_name      =>  'async_execution' || seq_async.nextval(),  
         job_type      =>  'PLSQL_BLOCK',  
         job_action    =>  'BEGIN some_asynk_proc(); END;',  
         start_date    =>  sysdate,  
         enabled       =>  TRUE,  
         auto_drop     =>  TRUE,  
         comments      =>  'Async procedure call' 
         );  

Competitive solution is implementation using dbms_aq queues, but as for me oracle’s queues from time to time start to raise unexpected exceptions. From other side for dbms_scheduler solution it is impossible to guarantee the order.