• 数据泵导出报ORA-01555 ORA-22924


    最近对某数据库执行数据泵导出操作时碰到如下错误:
    Processing object type DATABASE_EXPORT/SCHEMA/JOB
    Processing object type DATABASE_EXPORT/SCHEMA/TABLE/POST_INSTANCE/PROCACT_INSTANCE
    Processing object type DATABASE_EXPORT/SCHEMA/TABLE/POST_INSTANCE/PROCDEPOBJ
    Processing object type DATABASE_EXPORT/SCHEMA/POST_SCHEMA/PROCOBJ
    Processing object type DATABASE_EXPORT/SCHEMA/POST_SCHEMA/PROCACT_SCHEMA
    Processing object type DATABASE_EXPORT/AUDIT
    . . exported "ZXUSER"."T_MX_ATTACHMENT" 9.452 GB 9549 rows
    ORA-31693: Table data object "SGBIZ"."T_MX_ATTACHMENT" failed to load/unload and is being skipped due to error:
    ORA-02354: error in exporting/importing data
    ORA-01555: snapshot too old: rollback segment number with name "" too small
    ORA-22924: snapshot too old
    Master table "SYS"."SYS_EXPORT_FULL_01" successfully loaded/unloaded

    首先修改undo_retention=10800重新导出问题依旧。
    alter system set undo_retention=10800 sid='*';
    $ oerr ora 22924
    22924, 00000, "snapshot too old"
    // *Cause: The version of the LOB value needed for the consistent read was
    // already overwritten by another writer.
    // *Action: Use a larger version pool.

    --才明白这个跟lob字段有关。

    select column_name, pctversion, retention from dba_lobs where table_name = 'T_MX_ATTACHMENT';
    col COLUMN_NAME for a30
    select column_name, pctversion, retention from dba_lobs where table_name = 'T_MX_ATTACHMENT';

    COLUMN_NAME PCTVERSION RETENTION
    ------------------------------ ---------- ----------
    ATT_FILE 900

    --这个表本身不大,但是lob字段占用的空间很大,可以发现即使改动了参数undo_retention=10800,表lob的属性
    --RETENTION依旧900,必须增加该参数,或者使用PCTVERSION参数。

    alter table SGBIZ.T_MX_ATTACHMENT modify lob(ATT_FILE)(retention);
    alter table SGBIZ.T_MX_ATTACHMENT modify lob(ATT_FILE)(PCTVERSION 20);

    select column_name, pctversion, retention from dba_lobs where table_name = 'T_MX_ATTACHMENT';
    COLUMN_NAME PCTVERSION RETENTION
    -------------------- ---------- ----------
    REPORTPRINT 10800

    修改后重新导出,导出正常。
    Processing object type DATABASE_EXPORT/SCHEMA/JOB
    Processing object type DATABASE_EXPORT/SCHEMA/TABLE/POST_INSTANCE/PROCACT_INSTANCE
    Processing object type DATABASE_EXPORT/SCHEMA/TABLE/POST_INSTANCE/PROCDEPOBJ
    Processing object type DATABASE_EXPORT/SCHEMA/POST_SCHEMA/PROCOBJ
    Processing object type DATABASE_EXPORT/SCHEMA/POST_SCHEMA/PROCACT_SCHEMA
    Processing object type DATABASE_EXPORT/AUDIT
    . . exported "ZXUSER"."T_MX_ATTACHMENT" 9.452 GB 9549 rows
    . . exported "SGBIZ"."T_MX_ATTACHMENT" 21.30 GB 35716 rows
    Master table "SYS"."SYS_EXPORT_FULL_02" successfully loaded/unloaded

  • 相关阅读:
    会声会影教程之图片音乐相册制作
    js校验表单后提交表单的三种方法总结(转)
    如何避免后台IO高负载造成的长时间JVM GC停顿(转)
    nginx的upstream目前支持5种方式的分配(转)
    Nginx配置文件详细说明(转)
    如何将character_set_database latin1 改为 gbk(转)
    Maven打包可执行Jar包方式
    六种微服务架构的设计模式(转)
    Linux Shell 命令
    Condition的await-signal流程详解(转)
  • 原文地址:https://www.cnblogs.com/datalife/p/7574396.html
Copyright © 2020-2023  润新知