从系统中导出两个含有lob字段的表，连续两次都因ora-1555错误而终止
[oracle@db-8 ~]$ expdp justin/***** tables=temp_justin, justin_description parallel=8 directory=pump dumpfile=new_lobs_20111008_%U.dmp

Export: Release 10.2.0.5.0 - 64bit justinion on Saturday, 08 October, 2011 17:05:38

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit justinion
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "justin"."SYS_EXPORT_TABLE_02": justin/******** tables=temp_justin, justin_description parallel=8 directory=pump dumpfile=new_lobs_20111008_%U.dmp
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
ORA-31693: Table data object "justin"."TEMP_justin" 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
. . exported "justin"."justin_DESCRIPTION" 6.961 GB 628170 rows
Master table "justin"."SYS_EXPORT_TABLE_02" successfully loaded/unloaded
******************************************************************************
Dump file set for justin.SYS_EXPORT_TABLE_02 is:
/data/oracle/pump/new_lobs_20111008_01.dmp
/data/oracle/pump/new_lobs_20111008_02.dmp
/data/oracle/pump/new_lobs_20111008_03.dmp
/data/oracle/pump/new_lobs_20111008_04.dmp
Job "justin"."SYS_EXPORT_TABLE_02" completed with 1 error(s) at 19:13:55

从ORA-22924可以看出是lob字段的问题
而目前的retention只有900秒，且pctversion为null
SQL> select column_name, pctversion, retention from user_lobs where table_name = 'JUSTIN';

COLUMN_NAME                    PCTVERSION RETENTION
------------------------------ ---------- ----------
DESCRIPTION                                900
SKILL                                      900

另外，可以用sysdba账户查询这两个lob字段是使用pctversion还是retention
SQL> select decode(bitand(flags, 32), 32, 'Retention', 'Pctversion') ||
2         ' policy used'
3    from lob$
4   where lobj# in (select object_id
5                    from dba_objects
6                   where object_name in
7                         (select segment_name
8                            from dba_lobs
9                           where table_name in ('TEMP_JUSTIN') and wner ='JUSTIN'));

DECODE(BITAND(FLAGS,32
----------------------
Retention policy used
Retention policy used

目前解决办法有两个，加大retention或者使用pctversion

1、加大retention
lob字段使用的retention与系统中的undo_retention值是一样的，需要先调整undo_retention
SQL> alter system set undo_retention=18000;

System altered.

SQL> alter table JUSTIN modify lob(SKILL)(retention);

Table altered.

SQL> select column_name, pctversion, retention from user_lobs where table_name = 'JUSTIN';

COLUMN_NAME                    PCTVERSION RETENTION
------------------------------ ---------- ----------
DESCRIPTION                                900
SKILL                                      18000

SQL> alter table JUSTIN modify lob(DESCRIPTION)(retention);

Table altered.

SQL> select column_name, pctversion, retention from user_lobs where table_name = 'JUSTIN';

COLUMN_NAME                    PCTVERSION RETENTION
------------------------------ ---------- ----------
DESCRIPTION                               18000
SKILL                                     18000

2、使用pctversion
从结果中可以看到，当显示指定pctversion的时候，retention参数会失效
SQL> alter table JUSTIN modify lob(DESCRIPTION) (pctversion 10);

Table altered.

SQL> select column_name, pctversion, retention from user_lobs where table_name = 'JUSTIN';

COLUMN_NAME                    PCTVERSION RETENTION
------------------------------ ---------- ----------
DESCRIPTION                    10
SKILL                                     18000
此时再运行脚本，已经多出一条Pctversion policy used
SQL> select decode(bitand(flags, 32), 32, 'Retention', 'Pctversion') ||
2         ' policy used'
3    from lob$
4   where lobj# in (select object_id
5                    from dba_objects
6                   where object_name in
7                    (select segment_name
8                                from dba_lobs
9                           where table_name in ('JUSTIN') and wner ='JUSTIN'));

DECODE(BITAND(FLAGS,32
----------------------
Retention policy used
Pctversion policy used

另外，对于使用MSSM表空间表，只有pctverion可用，lob retention不可用

ORA-1555 - UNDO_RETENTION is silently ignored if the LOB resides in a MSSM tablespace [ID 800386.1]

Applies to:

Oracle Server - Enterprise Edition - Version: 9.0.1.0 to 11.2.0.0
This problem can occur on any platform.

Symptoms

ORA-1555/ORA-22924 may occur when accessing LOB columns, even when the LOB RETENTION seems to be sufficient.

This may occur when LOB column resides in a MSSM (Manual Segment Space Management) tablespace.

-- Verify Segment Management

select tablespace_name, extent_management, segment_space_management
from dba_tablespaces
where tablespace_name in
(select tablespace_name from dba_segments
   where wner='TEST' and segment_name='TAB1');

TABLESPACE_NAME                EXTENT_MAN SEGMEN
------------------------------ ---------- ------
TESTCASE                            LOCAL MANUAL <==

To check whether tablespace is using Manual verify whether PCTVERSION or RETENTION is used, execute:

-- Verify LOB retention parameter
select column_name, pctversion, retention
from dba_lobs
where wner = 'TEST' and table_name ='TAB1';

COLUMN_NAME PCTVERSION RETENTION
------------- ---------- ----------
DATA 86400 <==

.

Changes

LOB column is stored on MSSM tablespace.

Cause

The LOB RETENTION parameter has *no* effect if the LOB resides in a tablespace using MANUAL space management (MSSM). In order for LOB RETENTION to honour the UNDO_RETENTION period Automatic Segment Space Managemetn (ASSM) should be used.

The RETENTION parameter is silently ignored if the LOB resides in an MSSM tablespace.
This is currently not documented in any RDBMS documentation of releases 9.x / 10.x / 11.1

The following information will be added to the 11.2 documenation:

Oracle® Database SecureFiles and Large Objects Developer's Guide
11g Release 2 (11.2)

ASSM is required for LOB RETENTION to be in effect for BASICFILE LOBs. The RETENTION parameter of the SQL (in the STORE AS clause) is silently ignored if the BASICFILE LOB resides in an MSSM tablespace.

Solution

If you want to use LOB retention for LOB columns to avoid ORA-1555, you must use ASSM (Automatic Segment Space Management) tablespace.

The segment space management mode, which was specified at tablespace creation time, applies to all segments which will be created in the tablespace.

You cannot change the segment space management mode of a tablespace. If your LOB column is store on MSSM tablespace and you would like to use the ASSM option, you'd have to create a new tablespace using 'segment space management auto' followed by moving the objects to the new tablespace which was created to use Automatic Segment Space Management.

create tablespace assm_ts datafile
...
autoextend on
extent management local
segment space management auto; <==

alter table move tablespace ;

If you can't move to ASSM and you need to store your LOB data on MSSM tablespace, you'd have to use PCTVERSION instead of RETENTION.

-- Example: Setting PCTVERSION to 20 percent
SQL> alter table modify lob() (pctversion 20);

PCTVERSION is the percent of used LOB data blocks that is available
for versioning old LOB data.

Segment Space Management	use
AUTO	RETENTION or PCTVERSION
MANUAL	PCTVERSION

ITPub博客

lob字段的ora-1555处理方案

Applies to:

Symptoms

Changes

Cause

Solution

最新文章