从系统中导出两个含有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

Copyright (c) 2003, 2007, Oracle.  All rights reserved.

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

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博客 ” ，链接：http://blog.itpub.net/15480802/viewspace-708843/，如需转载，请注明出处，否则将追究法律责任。