美文网首页
[杂七杂八的小问题]清除DBA_DATAPUMP_JOBS视图中

[杂七杂八的小问题]清除DBA_DATAPUMP_JOBS视图中

作者: 胖熊猫l | 来源:发表于2017-03-09 00:25 被阅读0次

    0. summary

    1. 问题现象
    2. 问题处理
    .   2.1 查找在数据库中有哪些数据泵作业
    .   2.2 查找相关的master表
    .   2.3 对于过去被终止的和根本不会再启动的作业,删除它的master表
    .   2.4 重新检查
    

    1. 问题现象

    Export: Release 10.2.0.5.0 - 64bit Production on Wednesday, 08 March, 2017 10:10:16
    
    Copyright (c) 2003, 2007, Oracle.  All rights reserved.
    
    Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
    With the Partitioning, OLAP, Data Mining and Real Application Testing options
    ORA-31626: job does not exist
    ORA-31633: unable to create master table "SYS.CSBLOG_JOB"
    ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95
    ORA-06512: at "SYS.KUPV$FT", line 866
    ORA-00955: name is already used by an existing object
    

    2. 问题处理

    参考:

    如何清除 DBA_DATAPUMP_JOBS 视图中的异常数据泵作业? (文档 ID 1626201.1)

    2.1 查找在数据库中有哪些数据泵作业

    set lines 200 pages 200
    COL owner_name FORMAT a10; 
    COL job_name FORMAT a20 
    COL state FORMAT a12
    COL operation LIKE state 
    COL job_mode LIKE state
    
    SELECT owner_name, job_name, operation, job_mode, 
    state, attached_sessions 
    FROM dba_datapump_jobs 
    WHERE job_name NOT LIKE 'BIN$%' 
    ORDER BY 1,2; 
    
    OWNER_NAME JOB_NAME             OPERATION    JOB_MODE     STATE        ATTACHED_SESSIONS
    ---------- -------------------- ------------ ------------ ------------ -----------------
    SYS        CSBLOG_JOB           EXPORT       SCHEMA       NOT RUNNING                  0
    

    确保在dba_datapump_jobs中列出的作业不是活动的数据泵作业,状态应该是'NOT RUNNING'. 且确认是非暂停的作业。

    2.2 查找相关的master表

    col "OWNER.OBJECT" for a30
    SELECT o.status, o.object_id, o.object_type, 
           o.owner||'.'||object_name "OWNER.OBJECT" 
      FROM dba_objects o, dba_datapump_jobs j 
     WHERE o.owner=j.owner_name AND o.object_name=j.job_name 
       AND j.job_name NOT LIKE 'BIN$%' ORDER BY 4,2;  
    
    STATUS   OBJECT_ID OBJECT_TYPE         OWNER.OBJECT
    ------- ---------- ------------------- ------------------------------
    VALID       252249 TABLE               SYS.CSBLOG_JOB
    

    2.3 对于过去被终止的和根本不会再启动的作业,删除它的master表

    SQL> drop table SYS.CSBLOG_JOB purge;
    
    Table dropped.
    

    2.4 重新检查

    SELECT owner_name, job_name, operation, job_mode, 
    state, attached_sessions 
    FROM dba_datapump_jobs 
    WHERE job_name NOT LIKE 'BIN$%' 
    ORDER BY 1,2;
    
    no rows selected
    
    SELECT o.status, o.object_id, o.object_type, 
           o.owner||'.'||object_name "OWNER.OBJECT" 
      FROM dba_objects o, dba_datapump_jobs j 
     WHERE o.owner=j.owner_name AND o.object_name=j.job_name 
       AND j.job_name NOT LIKE 'BIN$%' ORDER BY 4,2;  
    
    no rows selected
    

    相关文章

      网友评论

          本文标题:[杂七杂八的小问题]清除DBA_DATAPUMP_JOBS视图中

          本文链接:https://www.haomeiwen.com/subject/olnigttx.html