-- Tabelle erzeugen

create table test (
         id number(6),
         name varchar2(20),
         vorname varchar2(30),
         datum date
     );
/

-- Datensatz hinzufügen

INSERT INTO test
VALUES(123,'Gnome','ORCL','01-02-14' );
/

-- oder create table test as select * from Tabelle Name;

-- PROCEDURE erzeugen

CREATE OR REPLACE PROCEDURE DELTAB(TABNAM IN VARCHAR2)
IS
COUNTS NUMBER;
BEGIN
 SELECT COUNT(*) INTO COUNTS FROM USER_TABLES WHERE TABLE_NAME = TABNAM;
 IF (COUNTS > 0) THEN
    execute immediate 'DELETE FROM ' || TABNAM;
 END IF;
END;
/

-- Job erzeugen

BEGIN
    SYS.DBMS_SCHEDULER.CREATE_JOB(
        job_name             => 'DELMYTAB_JOB',
        job_type             => 'PLSQL_BLOCK',
        job_action           => 'begin DELTAB(''test''); end;',
        start_date           => SYSTIMESTAMP,
        repeat_interval      => 'FREQ=WEEKLY;BYDAY=MON,TUE,WED,THU,FRI;BYHOUR=9;BYMINUTE=0;BYSECOND=0',
        job_class            => 'DEFAULT_JOB_CLASS',
        comments             => 'DELETE TABLE',
        auto_drop            => FALSE,
        enabled => TRUE);

END;
/

-- Jobs selektieren

SELECT * FROM dba_scheduler_jobs
WHERE owner = 'USER NAME';

-- manuell Job ausführen

BEGIN
    DBMS_SCHEDULER.RUN_JOB( 'DELMYTAB_JOB');
END;
/

-- LOG kontrolle

SELECT * FROM DBA_SCHEDULER_JOB_LOG
WHERE JOB_NAME = 'DELMYTAB_JOB';

-- Job Disable oder Enable

BEGIN
  DBMS_SCHEDULER.DISABLE('DELMYTAB_JOB');
END;
/

-- Job löschen

BEGIN
 DBMS_SCHEDULER.DROP_JOB('DELMYTAB_JOB');
END;
/

-- log History leeren
-- Anmeldung SYS as SYSDBA
-- Selktieren

SELECT
     LOG_ID, LOG_DATE, OWNER, e.NAME,
     co.NAME, OPERATION,e.STATUS, USER_NAME,
     CLIENT_ID, GUID, ADDITIONAL_INFO
  FROM scheduler$_event_log e, obj$ co
  WHERE e.NAME = 'DELMYTAB_JOB'  and e.class_id = co.obj#(+);

-- löschen

DELETE FROM obj$ co WHERE co.obj# =
 (SELECT e.class_id  FROM scheduler$_event_log e
  WHERE e.NAME = 'DELMYTAB_JOB');

commit;

DELETE FROM scheduler$_event_log e
 WHERE e.NAME = 'DELMYTAB_JOB';

commit;