Shulou(Shulou.com)05/31 Report--

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!

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.