Shulou(Shulou.com)06/01 Report--

Expdp ORA-01555 (1)

Environmental Information:

DB:Oracle 11.2.0.1.0

OS:Windows Server 2012

-sensitive data has been replaced

Question:

Expdp reported an error ORA-01555 when exporting a large table (20g) with BLOG fields

-expdp_log

Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE

Processing object type SCHEMA_EXPORT/VIEW/VIEW

Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS

ORA-31693: Table data object "CHENJCH". "T_XXX_XXXXX" 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 10 with name "_ SYSSMU10_XXXXXXXXX$" too small

-alert_XXX.log

Tue Oct 30 12:05:20 2018

Archived Log entry 6560 added for thread 1 sequence 46359 ID 0x7262bbad dest 1:

Tue Oct 30 13:19:12 2018

ORA-01555 caused by SQL statement below (SQL ID: 88tquba1dj6s0, SCN: 0x0000.47dd74a2):

SELECT * FROM RELATIONAL ("CHENJCH". "T_XXX_XXXXX")

Tue Oct 30 13:29:42 2018

Problem analysis:

There are generally two reasons for ORA-01555 problems:

(1) insufficient UNDO table space

(2) undo_retention time is too small

-check out the UNDO tablespace. There is a lot left.

SQL > select bytes / 1024 / 1024 / 1024

Tablespace_name

Autoextensible

Maxbytes / 1024 / 1024 / 1024

From dba_data_files a

Where tablespace_name = 'UNDOTBS1'

BYTES/1024/1024/1024 TABLESPACE_NAME AUTOEXTENSIBLE MAXBYTES/1024/1024/1024

1.4306640625 UNDOTBS1 YES 31.9999847412109

SQL > select segment_name

Tablespace_name

R.status

(initial_extent / 1024) InitialExtent

(next_extent / 1024) NextExtent

Max_extents

V.curext CurExtent

From dba_rollback_segs r, v$rollstat v

Where r.segment_id = v.usn (+)

And segment_name ='_ SYSSMU10_XXXXXXXXX$'

SEGMENT_NAME TABLESPACE_NAME STATUS INITIALEXTENT NEXTEXTENT MAX_EXTENTS CUREXTENT

-

_ SYSSMU10_XXXXXXXXX$ UNDOTBS1 ONLINE 128 64 32765 3

-check the default value of undo_retention for 900s

SQL > show parameter undo

NAME TYPE VALUE

-

Undo_management string AUTO

Undo_retention integer 900

Undo_tablespace string UNDOTBS1

-check that retention defaults to 900s and PCTVERSION is empty

SQL > select column_name, pctversion, retention

From dba_lobs

Where table_name = 'Tunable XXXXX XXXXX'

And owner = 'CHENJCH'

COLUMN_NAME PCTVERSION RETENTION

FFILE 900

-check whether you are currently using retention or PCTVERSION

Select decode (bitand (flags, 32), 32, 'Retention',' Pctversion') | |

'policy used'

From lob$

Where lobj# in

(select object_id

From dba_objects

Where object_name in (select segment_name

From dba_lobs

Where table_name in ('Tunable XXXIX XXXXX')

And owner = 'CHENJCH'))

DECODE (BITAND (FLAGS,32), 32)

-

Retention policy used

SQL > select max (maxquerylen) from v$undostat

MAX (MAXQUERYLEN)

-

1939

Solution: turn up the RETENTION

SQL > ALTER SYSTEM SET UNDO_RETENTION = 3600 scope=both

SQL > SHow parameter undo

NAME TYPE VALUE

-

Undo_management string AUTO

Undo_retention integer 3600

Undo_tablespace string UNDOTBS1

-the retention used in the lob field is still 900.

SQL > select column_name, pctversion, retention

From dba_lobs

Where table_name = 'Tunable XXXXX XXXXX'

And owner = 'CHENJCH'

COLUMN_NAME PCTVERSION RETENTION

FFILE 900

-the retention used in the lob field needs to be executed once

SQL > alter table CHENJCH.T_XXX_XXXXX modify lob (FFILE) (retention)

-ORA-00054: the resource is busy, but the resource is specified to be obtained by NOWAIT, or the timeout expires.

-I'll execute it later.

Table altered

-alter table CHENJCH.T_XXX_XXXXX modify lob (FFILE) (pctversion 5)

SQL > select column_name, pctversion, retention

From dba_lobs

Where table_name = 'Tunable XXXXX XXXXX'

And owner = 'CHENJCH'

COLUMN_NAME PCTVERSION RETENTION

FFILE 3600

-fallback operation

-ALTER SYSTEM SET UNDO_RETENTION = 900 scope=both

-alter table CHENJCH.T_XXX_XXXXX modify lob (FFILE) (retention)

Reference:

Data Pump Export Fails With ORA-31693 ORA-02354 and ORA-01555 Errors And No LOB Corruption (document ID 1507116.1)

Https://support.oracle.com/epmos/faces/SearchDocDisplay?_afrLoop=337135896307291&_afrWindowMode=0&_adf.ctrl-state=1bqt29sg65_4

Welcome to follow my Wechat official account "IT Little Chen" and learn and grow together!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.