How to solve the problem of exporting error snapshots with clob fields in oracle
Xiaobian to share with you how to solve the export of oracle containing clob field table error snapshot too old problem, I hope you have something to gain after reading this article, let's discuss it together!
Export table data with clob field, report snapshot is too old, unable to export
This problem is caused by the large data in the table containing clob fields. It is necessary to modify the database flashback parameters to solve this problem.
Check whether the undo table space is large enough, and whether the undo_retention parameter is set too small. Generally, it has nothing to do with undo space. Just modify the parameter.
There are two ways to solve this problem: increase retention or use pctversion.
Also, for tables using MSSM tablespaces, only pctverion is available, lob retention is not
1) Increased retention
View undo parameters
SQL> show parameter undo
--modify undo flashback parameters
alter system set undo_retention=7200 scope=both;
By querying oracle official website, oracle lob field has its own retention parameter. If you only adjust undo_retention without synchronizing to lob field, this parameter is still the default 900s. Confirm the query result as follows:
select table_name,column_name,pctversion,retention from dba_lobs where table_name='T_PATIENT_DOC_COLB';
select table_name,column_name,pctversion,retention from dba_lobs where table_name='T_PATIENTS_DOC_CA';
select table_name,column_name,pctversion,retention from dba_lobs where table_name='T_CARE_DOC';
Modify retention settings for tables with large lob fields
ALTER TABLE T_PATIENT_DOC_COLB MODIFY LOB(CONTENT)(retention);
ALTER TABLE T_PATIENTS_DOC_CA MODIFY LOB(SIGN)(retention);
ALTER TABLE T_PATIENTS_DOC_CA MODIFY LOB(TIMESTAMP)(retention);
ALTER TABLE T_CARE_DOC MODIFY LOB(CARE_DOC)(retention);
2. Use pctversion
As you can see from the results, the retention parameter is invalidated when the specified pctversion is displayed
SQL> alter table T_PATIENTS_DOC_CA modify lob(SIGN) (pctversion 10);
SQL> select column_name, pctversion, retention from user_lobs where table_name = 'T_PATIENTS_DOC_CA';
COLUMN_NAME PCTVERSION RETENTION
------------------------------ --------- ----------
SIGN 10
TIMESTAMP 7200
After reading this article, I believe you have a certain understanding of "how to solve the problem of exporting incorrect snapshots containing clob fields in oracle". If you want to know more about it, welcome to pay attention to the industry information channel. Thank you for reading!
