從系統中匯出兩個含有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

lob欄位的ora-1555處理方案

Applies to:

Symptoms

Changes

Cause

Solution

相關文章