[Oracle] 오라클 스케줄 기능을 이용한 컬럼 추가 · 삭제

[실습1] emp 테이블에 address 를 컬럼을 오늘 3시에 수행되게하시오

BEGIN
  DBMS_SCHEDULER.CREATE_JOB (
    job_name        => 'add_email_job',
    job_type        => 'PLSQL_BLOCK',
    job_action      => 'BEGIN EXECUTE IMMEDIATE ''ALTER TABLE emp ADD address VARCHAR2(10)''; END;',
    start_date      => TO_TIMESTAMP('2025-09-16 14:51:00', 'YYYY-MM-DD HH24:MI:SS'),
    enabled         => TRUE,
    auto_drop       => TRUE   -- 실행 완료 후 자동 삭제
  );
END;
/

확인

SELECT job_name, state, start_date, next_run_date
FROM dba_scheduler_jobs
WHERE job_name = 'ADD_EMAIL_JOB';

과거에 수행되었던 스케줄을 확인하고 싶으면

SELECT log_id, job_name, status, actual_start_date, run_duration
FROM dba_scheduler_job_run_details
WHERE job_name = 'ADD_EMAIL_JOB';

---

문제1. dept 테이블에 loc 컬럼을 drop 하는 잡을 생성하고 2시 56분에 수행되게 하시오

BEGIN
  DBMS_SCHEDULER.CREATE_JOB (
    job_name        => 'drop_dept_loc',
    job_type        => 'PLSQL_BLOCK',
    job_action      => 'BEGIN EXECUTE IMMEDIATE ''ALTER TABLE dept drop column loc''; END;',
    start_date      => TO_TIMESTAMP('2025-09-16 14:56:00', 'YYYY-MM-DD HH24:MI:SS'),
    enabled         => TRUE,
    auto_drop       => TRUE   -- 실행 완료 후 자동 삭제
  );
END;
/


SELECT job_name, state, start_date, next_run_date
FROM dba_scheduler_jobs
WHERE job_name = upper('drop_dept_loc');


SELECT log_id, job_name, status, actual_start_date, run_duration
FROM dba_scheduler_job_run_details
WHERE job_name = upper('drop_dept_loc');

select * from dept;