ORA-01752: cannot delete from view without exactly one key-preserved table

ORA-01752: cannot delete from view without exactly one key-preserved table

Environment:

Oracle database server 11.2.0.2.

Reason is that you are trying to delete from view instead of deleting from the underlying base table.

I was testing a new scheduler job in Oracle that calls a shell script on the Linux server. As part of the test, I had to run the job manually using dbms_scheduler to see if the run was fine. I got it working after a while of debugging and testing.

But, the job run log was ugly. There were some failures and some successes. I wanted to purge the entries in the log for the failed runs as this was a new job. That way, I can have a clean log going forward.

Error Message:

I first got the log_id for the failures from dba_scheduler_job_run_details.

Next, I attempted to delete the entries for the log_id from dba_scheduler_job_log.

SQL> delete from DBA_SCHEDULER_JOB_LOG  where log_id=123534;

delete from DBA_SCHEDULER_JOB_LOG  where log_id=123534

            *

ERROR at line 1:

ORA-01752: cannot delete from view without exactly one key-preserved table

Error Explanation:

> oerr ora 01752

01752, 00000, "cannot delete from view without exactly one key-preserved table"

// *Cause: The deleted table had

//         - no key-preserved tables,

//         - more than one key-preserved table, or

//         - the key-preserved table was an unmerged view.

// *Action: Redefine the view or delete it from the underlying base tables.

I knew instantly the reason. Next, find the base table.

SQL> set long 9999 pages 0 head off

SQL> select dbms_metadata.get_ddl('VIEW','DBA_SCHEDULER_JOB_LOG','SYS') from dual;

  CREATE OR REPLACE FORCE VIEW "SYS"."DBA_SCHEDULER_JOB_LOG" ("LOG_ID", "LOG_DAT

E", "OWNER", "JOB_NAME", "JOB_SUBNAME", "JOB_CLASS", "OPERATION", "STATUS", "USE

R_NAME", "CLIENT_ID", "GLOBAL_UID", "CREDENTIAL_OWNER", "CREDENTIAL_NAME", "DEST

INATION_OWNER", "DESTINATION", "ADDITIONAL_INFO") AS

  (SELECT

     LOG_ID, LOG_DATE, OWNER,

     DECODE(instr(e.NAME,'"'),0, e.NAME,substr(e.NAME,1,instr(e.NAME,'"')-1)),

     DECODE(instr(e.NAME,'"'),0,NULL,substr(e.NAME,instr(e.NAME,'"')+1)),

     co.NAME, OPERATION,e.STATUS, USER_NAME, CLIENT_ID, GUID,

     decode(e.credential, NULL, NULL,

        substr(e.credential, 1, instr(e.credential, '"')-1)),

     decode(e.credential, NULL, NULL,

        substr(e.credential, instr(e.credential, '"')+1,

           length(e.credential) - instr(e.credential, '"'))),

     decode(bitand(e.flags, 1), 0, NULL,

        substr(e.destination, 1, instr(e.destination, '"')-1)),

     decode(bitand(e.flags, 1), 0, e.destination,

        substr(e.destination, instr(e.destination, '"')+1,

           length(e.destination) - instr(e.destination, '"'))),

     ADDITIONAL_INFO

  FROM scheduler$_event_log e, obj$ co

  WHERE e.type# = 66 and e.dbid is null and e.class_id = co.obj#(+))

So, my base table is scheduler$_event_log

I was able to delete the entries.

SQL> delete from scheduler$_event_log where LOG_ID=123534;

1 row deleted.

SQL> delete from scheduler$_event_log where LOG_ID=123435;

1 row deleted.

SQL> delete from scheduler$_event_log where LOG_ID=123215;

1 row deleted.

SQL> delete from scheduler$_event_log where LOG_ID=122574;

1 row deleted.

SQL> commit;

Commit complete.

My log looks clean now. I have only the SUCCEEDED entries.

SQL> set lines 200

SQL> col JOB_NAME for a30

SQL> select log_id, JOB_NAME,STATUS from dba_scheduler_job_run_details where job_name='DROP_OLDEST_PARTITION';

    LOG_ID JOB_NAME                        STATUS

---------- ------------------------------  ------------------------------

    121810 DROP_OLDEST_PARTITION           SUCCEEDED

    121970 DROP_OLDEST_PARTITION           SUCCEEDED

    119031 DROP_OLDEST_PARTITION           SUCCEEDED

    119611 DROP_OLDEST_PARTITION           SUCCEEDED

    120310 DROP_OLDEST_PARTITION           SUCCEEDED