Improve performance for my plsql block First of all let me make kind of a disclaimer here:

1) i'm a developer, not an administrator;

2) and i dont have admin rights on my database.

3) table specs : 

-- oulp_path_attendance_base_t - 5.437.370 rows

-- ota_gsi_delegate_bookings_t@DISEULPROD (over dblink) - 3.135.400 rows

--inted_parted_events_t@DISEULPROD (over dblink) 683.912 rows

That being said , to the code we go!

CREATE OR REPLACE TYPE  "OULP_AVIDS" as table of varchar2(50)
/


CREATE OR REPLACE PROCEDURE "OULP_UPDATE_ATTENDACE_INCLASS"
AS
   TYPE users_completed_avids IS RECORD(
     avid oulp_path_attendance_base_t.avid%TYPE,
     close_date oulp_path_attendance_base_t.completed_date%TYPE
    );
  TYPE avid_results IS TABLE OF users_completed_avids;

  resulted_avids avid_results;


  v_users OULP_AVIDS;
  v_user_avids OULP_AVIDS;

  v_c SYS_REFCURSOR;
  v_get_avids SYS_REFCURSOR;
  v_get_results SYS_REFCURSOR;

BEGIN
 PROCESS_ERROR('OULP_UPDATE_ATTENDACE_INCLASS START');
  OPEN v_c FOR
    SELECT distinct person_id
      FROM oulp_path_attendance_base_t
     WHERE avid is not null;

   LOOP
    FETCH v_c BULK COLLECT INTO v_users LIMIT 100;

    FOR i IN 1..v_users.COUNT LOOP


      OPEN v_get_avids FOR
      SELECT avid
        FROM oulp_path_attendance_base_t
       WHERE avid is not null
         AND person_id = v_users(i);

      FETCH v_get_avids BULK COLLECT INTO v_user_avids;
      CLOSE v_get_avids;      


     OPEN v_get_results FOR
      SELECT ev.activity_version_id avid,
             ev.course_end_date close_date
        FROM ota_gsi_delegate_bookings_t@DISEULPROD gsi,
             inted_parted_events_t@DISEULPROD ev
       WHERE gsi.event_id = ev.event_id
         AND gsi.delegate_id_emp = v_users(i)
         AND ota_status IN ('ATTENDED','Attended')
         AND ev.activity_version_id IN (SELECT column_value 
                                         FROM TABLE(v_user_avids));                              
        FETCH v_get_results BULK COLLECT INTO resulted_avids;   
        CLOSE v_get_results;        

        FORALL j IN 1..resulted_avids.COUNT
        UPDATE oulp_path_attendance_base_t 
           SET completed_date = resulted_avids(j).close_date
         WHERE avid = resulted_avids(j).avid
           AND person_id = v_users(i);



    END LOOP;   
      EXIT WHEN v_c%NOTFOUND;
   END LOOP;

  CLOSE v_c;
  PROCESS_ERROR('OULP_UPDATE_ATTENDACE_INCLASS END');

  EXCEPTION
    WHEN others THEN 
  PROCESS_ERROR('OULP_UPDATE_ATTENDACE_INCLASS FAILED');
END;


And the comments : 

-- the purpose of this procedure is to updates my local table (oulp_path_attendance_base_t) with the close_dates for some activity_ids (named avids).

-- PROCESS_ERROR() procedure is just a simple logging procedure.

-- im running this via a scheduled job with dbms_scheduler.

Is there any way i can improve the performance here? 

The job runs for about 10 hours and needs to run daily.

Cheers,
Alex
If your problem is timing out and you have no way of solving that issue, maybe you might want to process your records in chunks, but also with a single update. Here is a little demo, just to give you the idea, you can use another chunking method other than mod if you don't have an incremental key. And I have to add, for the good practise follow Philip's advice if you can.

create table BIG_UPDATE as
(select 
    rownum COL1, 
    'data' COL2 from ALL_OBJECTS);

--612 msec
update(
    select COL1, COL2 from BIG_UPDATE
    where 
        mod(COL1, 8) = 0
)
set col2 = '1234';

--3secs
update(
    select COL1, COL2 from BIG_UPDATE
)
set col2 = '1234';

rollback;