如何清理v$rman_backup_job_details视图报错ORA-02030

最新推荐文章于 2025-08-28 12:21:54 发布

原创最新推荐文章于 2025-08-28 12:21:54 发布 · 937 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#数据库

DBA 专栏收录该内容

1070 篇文章

订阅专栏

本文介绍如何通过删除特定会话密钥的数据及截断表来清理Oracle RMAN备份作业详情表v$rman_backup_job_details。此外，还提供了一个复杂的SQL查询示例，用于从该表中获取详细的备份统计数据。

delete from v$rman_backup_job_details where session_key=4;

在这里插入图片描述

truncate table v$rman_backup_job_details;

在这里插入图片描述

SELECT view_definition FROM v$fixed_view_definition WHERE lower(view_name)='v$rman_backup_job_details';

 SELECT  /*+ rule */ a.session_key,
      a.session_recid,
      a.session_stamp,
      a.command_id,
      a.start_time,
      a.end_time,
      a.input_bytes,
      a.output_bytes,
      a.status_weight,
      a.optimized_weight,
      a.object_type_weight,
      a.output_device_type,
      a.autobackup_count,
      a.backed_by_osb,
      a.autobackup_done,
      a.status,
      a.input_type,
      a.optimized,
      a.elapsed_seconds,
      a.compression_ratio,
      a.input_bytes_per_sec,
      a.output_bytes_per_sec,
      sys.dbms_rcvman.num2displaysize(input_bytes) input_bytes_display,
      sys.dbms_rcvman.num2displaysize(output_bytes) output_bytes_display,
      sys.dbms_rcvman.num2displaysize(input_bytes_per_sec) input_bytes_per_sec_display,
      sys.dbms_rcvman.num2displaysize(output_bytes_per_sec) output_bytes_per_sec_display, sys.dbms_rcvman.sec2displaytime(elapsed_seconds)time_taken_display, a.con_id
FROM 
  ( SELECT  unique a.session_recid session_key, a.*, decode(autobackup_count, 0, 'NO', 'YES') autobackup_done, decode(status_weight, 2000, 'FAILED', 1900, 'RUNNING WITH ERRORS', 1500, 'RUNNING WITH WARNINGS', 1001, 'RUNNING', 900, 'COMPLETED WITH ERRORS', 500, 'COMPLETED WITH WARNINGS', 001, 'COMPLETED', 'FAILED') status, decode(object_type_weight, 9, 'DB FULL', 8, 'RECVR AREA', 7, 'DB INCR', 6, 'DATAFILE FULL', 5, 'DATAFILE INCR', 4, 'ARCHIVELOG', 3, 'CONTROLFILE', 2, 'SPFILE', 1, 'BACKUPSET', null) input_type, decode(optimized_weight, 1, 'YES', 'NO') optimized, abs(a.end_time-a.start_time)*86400 elapsed_seconds,
    CASE
    WHEN a.input_bytes/decode(a.output_bytes, 0, null, a.output_bytes) > 1
  THEN   a.input_bytes/decode(a.output_bytes, 0, null, a.output_bytes)
    ELSE 1
    END compression_ratio, a.input_bytes/(decode(a.end_time-a.start_time, 0, 1, abs(a.end_time-a.start_time)*86400)) input_bytes_per_sec, a.output_bytes/(decode(a.end_time-a.start_time, 0, 1, abs(a.end_time-a.start_time)*86400)) output_bytes_per_sec
  FROM 
    ( SELECT  session_recid, session_stamp, command_id, min(start_time)
      OVER (partition by session_recid, session_stamp) start_time, max(end_time)
      OVER (partition by session_recid, session_stamp) end_time, sum(input_bytes)
      OVER (partition by session_recid, session_stamp) input_bytes, sum(output_bytes)
      OVER (partition by session_recid, session_stamp) output_bytes, max(status_weight)
      OVER (partition by session_recid, session_stamp)status_weight, max(optimized_weight)
      OVER (partition by session_recid, session_stamp) optimized_weight, max(object_type_weight)
      OVER (partition by session_recid, session_stamp) object_type_weight, decode(count(distinct output_device_type)
      OVER (partition by session_recid, session_stamp), 1, first_value(output_device_type)
      OVER (partition by session_recid, session_stamp), 0, null, '*') output_device_type, sum(autobackup_count)
      OVER (partition by session_recid, session_stamp) autobackup_count, backed_by_osb, con_id
    FROM V$RMAN_BACKUP_SUBJOB_DETAILS) a)a