Oracle数据泵expdp导出报ORA-01555和ORA-22924
Tags： CLOBexpdpORA-01555ORA-22924Oracle数据泵
现象
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 | [oracle@oadata bk]$ expdp \'/ AS SYSDBA\' directory=D1 dumpfile=oa_%U.dmp EXCLUDE=STATISTICS LOGFILE=expdp_oa_20221124.log \ > SCHEMAS=RMAN,ABOLOGY,C CLUSTER=N COMPRESSION=ALL parallel=12 FILESIZE=10g Export: Release 11.2.0.4.0 - Production on Thu Nov 24 04:31:38 2022 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production Starting "SYS"."SYS_EXPORT_SCHEMA_02": "/******** AS SYSDBA" directory=D1 dumpfile=oa_%U.dmp EXCLUDE=STATISTICS LOGFILE=expdp_oa_20221124.log SCHEMAS=RMAN,AB,C CLUSTER=N COMPRESSION=ALL parallel=12 FILESIZE=10g Estimate in progress using BLOCKS method... Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA Total estimation using BLOCKS method: 166.6 GB Processing object type SCHEMA_EXPORT/USER Processing object type SCHEMA_EXPORT/SYSTEM_GRANT Processing object type SCHEMA_EXPORT/ROLE_GRANT Processing object type SCHEMA_EXPORT/DEFAULT_ROLE Processing object type SCHEMA_EXPORT/TABLESPACE_QUOTA Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA Processing object type SCHEMA_EXPORT/TYPE/TYPE_SPEC Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE Processing object type SCHEMA_EXPORT/SEQUENCE/GRANT/OWNER_GRANT/OBJECT_GRANT Processing object type SCHEMA_EXPORT/TABLE/TABLE ORA-31693: Table data object "ABOLOGY"."WF_ABCDEFGHIJKLMNOPQLOG" 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 |
原因
1、undo_retention 参数配置过小，表本身很大或含有CLOB字段
2、表存在大的CLOB字段，而lob字段有坏块导致。
解决
可能原因一
表中存在lob字段，而lob字段有坏块导致。报错如下：
1 2 | ORA-01555: snapshot too old: rollback segment number with name "" too small ORA-22924: snapshot too old |
首先应该查询坏块的rowid，并且记录到sys.corrupted_lob_data表：
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 | drop table sys.corrupted_lob_data; create table sys.corrupted_lob_data (owner varchar(255),tbname varchar(255),col_name varchar(255),corrupted_rowid rowid); truncate table sys.corrupted_lob_data; set concat off declare error_1555 exception; pragma exception_init(error_1555,-1555); num number; begin for cur in (SELECT L.TABLE_NAME,L.COLUMN_NAME FROM DBA_LOBS L WHERE L.OWNER = UPPER('&&table_owner') and L.TABLE_NAME=UPPER('&&table_name') ) loop for cursor_lob in (select rowid r, cur.COLUMN_NAME from &&table_owner.&&table_name) loop begin num := dbms_lob.instr (cur.COLUMN_NAME, hextoraw ('889911')) ; exception when error_1555 then insert into sys.corrupted_lob_data values ('&&table_owner','&&table_name',cur.COLUMN_NAME,cursor_lob.r); commit; end; end loop; end loop; end; / |
然后查询表sys.corrupted_lob_data
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 | select * from sys.corrupted_lob_data; -- 发现报错 select * from "ABOLOGY"."WF_ABCDEFGHIJKLMNOPQLOG" where rowid in ( select corrupted_rowid from sys.corrupted_lob_data ); SQL> select * from "ABOLOGY"."WF_ABCDEFGHIJKLMNOPQLOG" 2 where rowid in ( select * from sys.corrupted_lob_data ); ERROR: ORA-01555: snapshot too old: rollback segment number with name "" too small ORA-22924: snapshot too old SQL> select * from "ABOLOGY"."WF_ABCDEFGHIJKLMNOPQLOG" where rowid='AABOzeAAIAAHxKWAAB'; ERROR: ORA-01555: snapshot too old: rollback segment number with name "" too small ORA-22924: snapshot too old |
MOS上给出的导出方案是将问题数据exclude掉，这里为了临时解决问题，将这几条数据的COLB字段置空。然后再次导出数据库数据，不再提示报错。
1 2 3 4 | update "ABOLOGY"."WF_ABCDEFGHIJKLMNOPQLOG" t set t.ABMIDS='' where rowid in ( select corrupted_rowid from sys.corrupted_lob_data ); commit; |
可能原因二
原因二是undo_retention 参数配置过小，表本身很大或含有CLOB字段，报错：
1 2 3 4 5 | ORA-31693: Table data object "LHR"."ABC_RLOG" 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 25 with name "_SYSSMU25_1063943794$" too small |
首先修改undo_retention=10800，若含有大的LOB字段，则重新导出可能问题依旧存在：
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 | [oracle@oadata bk]$ 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. SQL> show parameter retention NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ db_flashback_retention_target integer 1440 undo_retention integer 900 SQL> alter system set undo_retention =10800; System altered. SQL> show parameter retention NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ db_flashback_retention_target integer 1440 undo_retention integer 10800 SQL> |
这个表本身不大，但是lob字段占用的空间很大，可以发现即使改动了参数undo_retention=10800，表lob的属性RETENTION依旧900，必须增加该参数，或者使用PCTVERSION参数。
1 2 3 4 5 6 7 8 | SQL> col COLUMN_NAME for a30 SQL> select column_name, pctversion, retention from dba_lobs where table_name = 'WF_ABCDEFGHIJKLMNOPQLOG'; COLUMN_NAME PCTVERSION RETENTION ------------------------------ ---------- ---------- ABQUESTJSON 900 ABSPONSEJSON 900 |
修改以PCTVERSION为准：
1 2 3 4 5 6 7 | alter table ABOLOGY.WF_ABCDEFGHIJKLMNOPQLOG modify lob(ABQUESTJSON)(retention ); alter table ABOLOGY.WF_ABCDEFGHIJKLMNOPQLOG modify lob(ABQUESTJSON)(PCTVERSION 20); alter table ABOLOGY.WF_ABCDEFGHIJKLMNOPQLOG modify lob(ABSPONSEJSON)(retention); alter table ABOLOGY.WF_ABCDEFGHIJKLMNOPQLOG modify lob(ABSPONSEJSON)(PCTVERSION 20); |
查询：
1 2 3 4 5 6 7 8 | SQL> col COLUMN_NAME for a30 SQL> select column_name, pctversion, retention from dba_lobs where table_name = 'WF_ABCDEFGHIJKLMNOPQLOG'; COLUMN_NAME PCTVERSION RETENTION ------------------------------ ---------- ---------- ABQUESTJSON 20 ABSPONSEJSON 20 |
若是以undo_retention为准，则前后顺序需要修改：
1 2 3 4 5 6 7 8 | alter system set undo_retention =10800 sid='*'; alter table ABOLOGY.WF_ABCDEFGHIJKLMNOPQLOG modify lob(ABQUESTJSON)(PCTVERSION 20); alter table ABOLOGY.WF_ABCDEFGHIJKLMNOPQLOG modify lob(ABQUESTJSON)(retention ); alter table ABOLOGY.WF_ABCDEFGHIJKLMNOPQLOG modify lob(ABSPONSEJSON)(PCTVERSION 20); alter table ABOLOGY.WF_ABCDEFGHIJKLMNOPQLOG modify lob(ABSPONSEJSON)(retention); |
查询：
1 2 3 4 5 6 7 8 | SQL> col COLUMN_NAME for a30 SQL> select column_name, pctversion, retention from dba_lobs where table_name = 'WF_ABCDEFGHIJKLMNOPQLOG'; COLUMN_NAME PCTVERSION RETENTION ------------------------------ ---------- ---------- ABQUESTJSON 10800 ABSPONSEJSON 10800 |
然后重新导出表即可。
LOB字段大小查询
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 | -- 某个含lob字段的表大小 SELECT (SELECT trunc(SUM(S.BYTES)/1024/1024/1024) FROM DBA_SEGMENTS S WHERE S.OWNER = UPPER('&&SCHEMA') AND (S.SEGMENT_NAME = UPPER('&&TABNAME'))) as tbsize_g, (SELECT trunc(SUM(S.BYTES)/1024/1024/1024) FROM DBA_SEGMENTS S, DBA_LOBS L WHERE S.OWNER = UPPER('&&SCHEMA') AND (L.SEGMENT_NAME = S.SEGMENT_NAME AND L.TABLE_NAME = UPPER('&&TABNAME') AND L.OWNER = UPPER('&&SCHEMA'))) as lobsize_g, (SELECT trunc(SUM(S.BYTES)/1024/1024/1024) FROM DBA_SEGMENTS S, DBA_INDEXES I WHERE S.OWNER = UPPER('&&SCHEMA') AND (I.INDEX_NAME = S.SEGMENT_NAME AND I.TABLE_NAME = UPPER('&&TABNAME') AND INDEX_TYPE = 'LOB' AND I.OWNER = UPPER('&&SCHEMA'))) as lobindexsize_g , (select count(*) from &&SCHEMA..&&TABNAME) FROM DUAL; -- 含LOB字段的表总大小 SELECT (SELECT trunc(SUM(S.BYTES)/1024/1024/1024) -- The Table Segment size FROM DBA_SEGMENTS S, DBA_LOBS L WHERE S.OWNER = UPPER('&&SCHEMA') and L.TABLE_NAME = S.SEGMENT_NAME ) as tbsize_g, (SELECT trunc(SUM(S.BYTES)/1024/1024/1024) -- The Lob Segment Size FROM DBA_SEGMENTS S, DBA_LOBS L WHERE S.OWNER = UPPER('&&SCHEMA') AND (L.SEGMENT_NAME = S.SEGMENT_NAME AND L.OWNER = UPPER('&&SCHEMA'))) as lobsize_g, (SELECT trunc(SUM(S.BYTES)/1024/1024/1024) -- The Lob Index size FROM DBA_SEGMENTS S, DBA_INDEXES I WHERE S.OWNER = UPPER('&&SCHEMA') AND (I.INDEX_NAME = S.SEGMENT_NAME AND INDEX_TYPE = 'LOB' AND I.OWNER = UPPER('&&SCHEMA'))) as lobindexsize_g FROM DUAL; |
LOB字段的retention和PCTVERSION说明
pctversion n /retention:这两个属性用来解决lob段的一致性读问题。lob的特殊性决定它不能使用undo/rollback segment来管理自己的更新的old version,通常lob会在自己所在的表空间中划分一部分空间来管理自己的undo，保证read consistent.lob中更新原理是在lob segment中分配新的chunk插入新的数据,保留旧的镜像,如果一个数据有多个更新存在的话, 那么就会存在多个版本.pctversion用来定义lob segment中undo区域的大小,pctverision 是一个百分比,定义所有lob空间用来存放前镜像的百分比,如果前镜像使用空间超过这个百分比了,oracle不自动扩展这部分的大小, 会重用这些前镜像的空间.如果一个lob segment段的更新很频繁的情况下，那么该lob段的增长可能会很快.retention是9i的新参数, 只能用在tablespace采用ASSM的情况,在lob更新的时候,前镜像会保留一段时间, 具体的时间由undo_retention参数决定.决定采用乃种undo 方式,必须对应用测试后在决定.
Lob字段默认是pctversion等到于10,意思是表lob字段所在的表空间需要预留10%的空间给lob的前映象使用,当表空间比较小时,用pctversion应该不会有大的问题,当表空间超过100g时,就需要预留10g,可能存在浪费空间的问题,比如每小时更新lob记录的频率增长不多,还要减去(当lob的内容小于4000字节时,lob的前映象保存在undo表空间)这部分,但如果该表空间增长到300g,就有30g的空间预留，导致较多的空间被浪费.所以,当您的LOB字段所在的表空间已经很大时,要注意pctversion参数要适当的调小,否则空间的浪费是相当的严重...
关于lob的retention参数,这个与undo表空间的undo_retention是一致的,如果你的lob更新较小,就可以通过修改undo_retention来调整lob前映象的失效时间,由于lob的retention参数是与undo_retention保持一致,所以修改undo_retention来说,一定要慎重,毕竟它将会影响库中所有lob字段的retention参数...
UNDO_RETENTION specifies (in seconds) the low threshold value of undo retention. For AUTOEXTEND undo tablespaces, the system retains undo for at least the time specified in this parameter, and automatically tunes the undo retention period to satisfy the undo requirements of the queries. For fixed- size undo tablespaces, the system automatically tunes for the maximum possible undo retention period, based on undo tablespace size and usage history, and ignores UNDO_RETENTION unless retention guarantee is enabled.
The setting of this parameter should account for any flashback requirements of the system. Automatic tuning of undo retention is not supported for LOBs. The RETENTION value for LOB columns is set to the value of the UNDO_RETENTION parameter.
The UNDO_RETENTION parameter can only be honored if the current undo tablespace has enough space. If an active transaction requires undo space and the undo tablespace does not have available space, then the system starts reusing unexpired undo space. This action can potentially cause some queries to fail with a "snapshot too old" message.
Export Fails With Errors ORA-2354 ORA-1555 ORA-22924 And How To Confirm LOB Segment Corruption Using Export Utility (Doc ID 833635.1)
APPLIES TO:
Oracle Database - Enterprise Edition - Version 10.2.0.1 to 11.2.0.2 [Release 10.2 to 11.2]
Oracle Database - Enterprise Edition - Version 11.2.0.3 to 11.2.0.4 [Release 11.2]
Oracle Database Cloud Schema Service - Version N/A and later
Oracle Database Exadata Cloud Machine - Version N/A and later
Oracle Cloud Infrastructure - Database Service - Version N/A and later
Information in this document applies to any platform.
SYMPTOMS
Following errors encountered during export:
ORA-02354: error in exporting/importing data
ORA-01555: snapshot too old: rollback segment number with name " " too small
ORA-22924: snapshot too old
CAUSE
LOB segment corruption.
How To Confirm LOB Segment Corruption Using Export Utility
- Identify the table rowid's referencing the corrupted LOB segment blocks by running the PL/SQL procedure mentioned in the Note 787004.1.
As there is already a similar PL/SQL procedure indicated in various notes such as: Note 452341.1 or Note 253131.1, the reason for this article is to propose a method to confirm the LOB segment corruption using export utility by exporting the corrupted rowids.
For example:
If you encounter the error “ORA-1555: snapshot too old: rollback segment number with name " " too small” during export of table (
Note: Run the PL/SQL procedure after creating a temporary table “CORRUPTED_LOB_DATA”. This table is used to spool the corrupted rowids.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 | create table corrupted_lob_data (corrupted_rowid rowid); set concat off declare error_1555 exception; pragma exception_init(error_1555,-1555); num number; begin for cursor_lob in (select rowid r, &&lob_column from &table_owner.&table_with_lob) loop begin num := dbms_lob.instr (cursor_lob.&&lob_column, hextoraw ('889911')) ; exception when error_1555 then insert into corrupted_lob_data values (cursor_lob.r); commit; end; end loop; end; / After running the above procedure, it prompts for: Enter value for lob_column : <LOB_COLUMN> Enter value for table_owner : <SCHEMA_NAME> Enter value for table_with_LOB: <TABLE_NAME> Like this, we can check the corruption in all the LOB columns. In this example, the output of the table “CORRUPTED_LOB_DATA” is showing three rowid’s referencing the corrupted lob segment select * from corrupted_lob_data; CORRUPTED_ROWID --------------------- AAEWBsAAGAAACewAAC AAEWBsAAGAAACewAAF AAEWBsAAGAAACewAAG 3 rows selected Confirm the LOB corruption using Datapump: #> expdp <USER>/<PASSWORD> directory=data_pump_dir dumpfile=<DUMP_NAME1>.dmp logfile=<LOG_NAME1>.log tables=<TABLE_NAME> query=\"where rowid = \'AAEWBsAAGAAACewAAC\'\" #> expdp <USER>/<PASSWORD> directory=data_pump_dir dumpfile=<DUMP_NAME2>.dmp logfile=<LOG_NAME2>.log tables=<TABLE_NAME> query=\"where rowid = \'AAEWBsAAGAAACewAAF\'\" #> expdp <USER>/<PASSWORD> directory=data_pump_dir dumpfile=<DUMP_NAME3>.dmp.logfile=<LOG_NAME3>.log tables=<TABLE_NAME> query=\"where rowid = \'AAEWBsAAGAAACewAAG \'\" Or, confirm the LOB corruption using original export: #> exp <USER>/<PASSWORD> file=<DUMP_NAME1>.dmp log=<LOG_NAME1>.log tables=<TABLE_NAME> query=\"where rowid = \'AAEWBsAAGAAACewAAC\'\" #> exp <USER>/<PASSWORD> file=<DUMP_NAME2>.dmp log=<LOG_NAME2>.log tables=<TABLE_NAME> query=\"where rowid = \'AAEWBsAAGAAACewAAF\'\" #> exp <USER>/<PASSWORD> file=<DUMP_NAME3>.dmp log=<LOG_NAME3>.log tables=<TABLE_NAME> query=\"where rowid = \'AAEWBsAAGAAACewAAG\'\" If any of the above export fails then the LOB corruption confirmed. |
SOLUTION
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 | o Restore and recover the LOB segment using physical backup. - OR - o Empty the affected LOBs using the UPDATE statement as mentioned in the Note 787004.1: -- NOTE: for BLOB and BFILE columns use EMPTY_BLOB; for CLOB and NCLOB columns use EMPTY_CLOB, e.g.: update <TABLE_NAME> set <LOB_COLUMN> = empty_blob() where rowid in (select corrupted_rowid from corrupted_lob_data); commit; o Perform the export excluding the corrupted rowids. Using DataPump export: #> expdp <USER>/<PASSWORD> directory=data_pump_dir dumpfile=<DUMP_NAME>.dmp logfile=<LOG_NAME>.dmp.log tables=<TABLE_NAME> query=\"where rowid not in \(\'AAEWBsAAGAAACewAAC\', \'AAEWBsAAGAAACewAAF\', \'AAEWBsAAGAAACewAAG\'\)\" Using original export: #> exp <USER>/<PASSWORD> file=<DUMP_NAME>.dmp log=<LOG_NAME>.log tables=<TABLE_NAME> query=\"where rowid not in \(\'AAEWBsAAGAAACewAAC\', \'AAEWBsAAGAAACewAAF\', \'AAEWBsAAGAAACewAAG\'\)\" - OR - |
o Alternatively, you could use flashback query to solve the LOB corruption. However, details are beyond the scope of this article.
For more information, please refer to http://docs.oracle.com/cd/B28359_01/appdev.111/b28424/adfns_flashback.htm#i1008579
Note: If there is no corrupt lob data and the issue persists then rebuild the entire table to resolve the issue.
REFERENCES
NOTE:253131.1 - Concurrent Writes May Corrupt LOB Segment When Using Auto Segment Space Management (ORA-1555)
NOTE:452341.1 - ORA-01555 And Other Errors while Exporting Table With LOBs, How To Detect Lob Corruption.
NOTE:787004.1 - Export Receives The Errors ORA-1555 ORA-22924 ORA-1578 ORA-22922
Lob retention not changing when undo_retention is changed (Doc ID 563470.1)
APPLIES TO:
Oracle Database - Enterprise Edition - Version 8.1.7.4 to 10.2.0.5 [Release 8.1.7 to 10.2]
Oracle Database Exadata Cloud Machine - Version N/A and later
Oracle Cloud Infrastructure - Database Service - Version N/A and later
Oracle Database Exadata Express Cloud Service - Version N/A and later
Oracle Database Cloud Exadata Service - Version N/A and later
Information in this document applies to any platform.
SYMPTOMS
Disclaimer: this note illustrates a "problem"/behavior of setting retention in 10g (8.1.7.4~10.2.0.5), and is not intended to explain how retention/pctversion works or more retention option in 11g/12c.
In 10g we have no control over the actual setting for RETENTION for lobs other than the current setting for UNDO_RETENTION at the time of the creation or modification of the LOB.
Stopping and starting the database will not modify the setting as it seems to be static.
It is assumed that when UNDO_RETENTION is changed the lobs connected to that retention are also changed which is not the case . (this is incorrect)
If a lob is modified from RETENTION to PCTVERSION and back to RETENTION again then the lob retention is updated based on the current UNDO_RETENTION at the time that the LOB was altered / created to use RETENTION.
In 11g we allow users to specify retention for each LOB segment
Current documentation reads
Oracle� Database Application Developer's Guide - Large Objects
10g Release 2 (10.2)
Part Number B14249-01
4 LOBs in Tables
RETENTION
As an alternative to the PCTVERSION parameter, you can specify the RETENTION parameter in the LOB storage clause of the CREATE TABLE
or ALTER TABLE statement. Doing so, configures the LOB column to store old versions of LOB data for a period of time, rather than using a
percentage of the table space.
For example:
.
CREATE TABLE ContainsLOB_tab (n NUMBER, c CLOB)
lob (c) STORE AS SEGNAME (TABLESPACE lobtbs1 CHUNK 4096
RETENTION
NOCACHE LOGGING
STORAGE (MAXEXTENTS 5)
);
The RETENTION parameter is designed for use with Undo features of the database, such as Flashback Versions Query.When a LOB column has
the RETENTION property set, old versions of the LOB data are retained for the amount of time specified by the UNDO_RETENTION parameter.
Note the following with respect to the RETENTION parameter:
* Undo SQL is not enabled for LOB columns as it is with other datatypes. You must set the RETENTION property on a LOB column to use
Undo SQL on LOB data.
* You cannot set the value of the RETENTION parameter explicitly. The amount of time for retention of LOB versions in determined by the
UNDO_RETENTION parameter.
* Usage of the RETENTION parameter is only supported in Automatic Undo Management mode. You must configure your tablefor use with
Automatic Undo Management before you can set RETENTION on a LOB column.
* The LOB storage clause can specify RETENTION or PCTVERSION, but not both.
CAUSE
Problem is caused as current documentation not fully explaining how lob retention works.
SOLUTION
Here is example of the problem.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 | SQL> CREATE TABLE lobretention (LOBLOC blob,id NUMBER) LOB ( lobLoc ) STORE AS (TABLESPACE users STORAGE (INITIAL 5k NEXT 5k PCTINCREASE 0) retention); SQL> select retention from dba_lobs where table_name='LOBRETENTION'; RETENTION ---------- 1400 SQL> show parameter undo_retention; NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ undo_retention integer 1400 SQL> alter system set undo_retention= 1800 scope=both; System altered. SQL> select retention from dba_lobs where table_name='LOBRETENTION'; RETENTION ---------- 1400 SQL> shutdown immediate; Database closed. Database dismounted. ORACLE instance shut down. SQL> startup SQL> connect test/test Connected. SQL> select retention from dba_lobs where table_name='LOBRETENTION'; RETENTION ---------- 1400 SQL> show parameter undo_retention; NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ undo_retention integer 1800 SQL> alter table lobretention modify lob(lobLoc) (pctversion 5); Table altered. SQL> alter table lobretention modify lob(lobLoc) (retention); Table altered. SQL> select retention from dba_lobs where table_name='LOBRETENTION'; RETENTION ---------- 1800 |
参考
https://www.cnblogs.com/datalife/p/7574396.html
http://dbs-service.cn/index.php/a/271.html
