Expdp ORA-01555 (1)
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!
