合 Oracle 11.2.0.4 数据泵expdp导出含CLOB字段(basicfile)的表超级慢的问题
Tags： Oracle数据泵expdpCLOBbasicfilesecurefile
👉 本文共约4116个字，系统预计阅读时间或需16分钟。
目录
简介
在 11.2.0.4 上 expdp导出含有CLOB字段的大表时会非常慢，而且可能会发生ora-01555错误。该错误的解决办法见：https://www.dbaup.com/oracleshujubengexpdpdaochubaoora-01555heora-22924.html#ke_neng_yuan_yin_er ，但是导出仍然会很慢，解决办法只能是重建表，然后修改存储属性STORE AS securefile，再进行导出才会解决慢的问题。
Oracle 11g专门针对LOB字段的新特性securefile，这是在11g推出的新的LOB存储模式，之前的版本都是以basicfile方式存储LOB字段，securefile于basicfile相比，对LOB的读取和写入操作的效率均有明显提高，但却需要占用更多的磁盘空间，但也多的有限，基本可以忽略不计。在11g版本，默认的LOB存储模式还是basicfile，如果使用securefile，需要在建表的时候指定或者数据库securefile相关的参数才可以，个人建议LOB都以securefile存储。
启用SecureFiles 存储
使用DB_SECUREFILE 初始化参数，数据库管理员(DBA) 可确定SecureFiles 的使用情况，其中有效值为：
• ALWAYS ：尝试将所有LOB 创建为SecureFile LOB，但是仅可将自动段空间管理(ASSM) 表空间外的任何 LOB 创建为BasicFile LOB
• FORCE：强制将所有LOB 创建为SecureFile LOB
• PERMITTED：允许创建SecureFiles （默认值）
• NEVER：禁止创建SecureFiles
• IGNORE ：禁止创建SecureFiles ，并忽略使用SecureFiles 选项强制创建BasicFiles 而导致的任何错误
如果指定了NEVER，则任何指定为SecureFiles 的LOB 均被创建为BasicFiles。如果对BasicFiles 使用任何SecureFiles 特定的存储选项和功能（如压缩、加密和取消重复），则会导致异常错误。将对任何未指定的存储选项使用BasicFiles 默认值。如果指定了ALWAYS ，则系统中创建的所有LOB 均会被创建为SecureFiles 。必须在ASSM 表空间中创建LOB ，否则会发生错误。将忽略所有指定的BasicFiles 存储选项。可以使用
ALTER SYSTEM 命令更改所有存储的SecureFiles 默认值，如幻灯片中所示。也可以通过单击“Server （服务器）”选项卡中的“Initialization Parameters （初始化参数）”链接来使用Enterprise Manager 设置参数。
修改参数：
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 | SQL> show parameter db_securefile NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ db_securefile string PERMITTED SQL> alter system set db_securefile=xxxxxx; alter system set db_securefile=xxxxxx * ERROR at line 1: ORA-00096: invalid value XXXXXX for parameter db_securefile, must be from among FORCE, IGNORE, ALWAYS, PERMITTED, NEVER SQL> alter system set db_securefile=ALWAYS; System altered. SQL> show parameter db_securefile NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ db_securefile string ALWAYS SQL> |
Evaluate the following command:
SQL>ALTER SYSTEM SET db_securefile = 'IGNORE';
What is the impact of this setting on the usage of SecureFiles?
A. It forces BasicFiles to be created even if the SECUREFILE option is specified to create the LOB.
B. It forces SecureFiles to be created even if the BASICFILE option is specified to create the LOB.
C. It does not allow the creation of SecureFiles and generates an error if the SECUREFILE option is
specified to create the LOB.
D. It ignores the SECUREFILE option only if a Manual Segment Space Management tablespace is used and creates a BasicFile.
Answer: A
示例
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 | [oracle@oarac1 ~]$ expdp \'/ AS SYSDBA\' directory=D1 dumpfile=TEST.dmp EXCLUDE=STATISTICS tables="LHRLOGY"."LHRAFLOW_XXTABCDLOG" CLUSTER=N COMPRESSION=ALL Export: Release 11.2.0.4.0 - Production on Sun Nov 27 09:55:34 2022 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP, Data Mining and Real Application Testing options Starting "SYS"."SYS_EXPORT_TABLE_01": "/******** AS SYSDBA" directory=D1 dumpfile=TEST.dmp EXCLUDE=STATISTICS tables=LHRLOGY.LHRAFLOW_XXTABCDLOG CLUSTER=N COMPRESSION=ALL Estimate in progress using BLOCKS method... Processing object type TABLE_EXPORT/TABLE/TABLE_DATA Total estimation using BLOCKS method: 56.92 GB Processing object type TABLE_EXPORT/TABLE/TABLE Processing object type TABLE_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX Processing object type TABLE_EXPORT/TABLE/TRIGGER . . exported "LHRLOGY"."LHRAFLOW_XXTABCDLOG" 11.25 GB 31961861 rows Master table "SYS"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded ****************************************************************************** Dump file set for SYS.SYS_EXPORT_TABLE_01 is: /data/TEST.dmp Job "SYS"."SYS_EXPORT_TABLE_01" successfully completed at Sun Nov 27 10:33:39 2022 elapsed 0 00:38:04 |
获取表结构：
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 | SQL> set long 9999 SQL> SQL> SELECT DBMS_METADATA.GET_DDL(U.OBJECT_TYPE, U.OBJECT_NAME,U.owner) 2 FROM DBA_OBJECTS U WHERE U.OBJECT_TYPE IN ('TABLE', 'INDEX', 'PROCEDURE', 'FUNCTION','PACKAGE','TRIGGER') 3 AND U.OBJECT_NAME='LHRAFLOW_XXTABCDLOG' 4 AND U.owner='LHRLOGY'; DBMS_METADATA.GET_DDL(U.OBJECT_TYPE,U.OBJECT_NAME,U.OWNER) -------------------------------------------------------------------------------- CREATE TABLE "LHRLOGY"."LHRAFLOW_XXTABCDLOG" ( "REQUESTID" NUMBER(*,0), "SIGNDOCIDS" VARCHAR2(1000), "SIGNWORKFLOWIDS" VARCHAR2(1000), "RECEIVEDPERSONS" CLOB, "ISMOBILE" CHAR(1), "TMPHANDWRITTENSIGN" NUMBER(*,0), "SPEECHATTACHMENT" NUMBER(*,0), "RECEIVEDPERSONIDS" CLOB, "REMARKLOCATION" VARCHAR2(1000), "ISSUBMITDIRECT" CHAR(1), "REMARKQUOTE" CLOB, "FULLTEXTANNOTATION" VARCHAR2(1000), "SPEECHATTACHMENTE9" VARCHAR2(1000), "UUID" VARCHAR2(100), "REMARK" CLOB, "HANDWRITTENSIGN" VARCHAR2(300), "OPERATORSUB" NUMBER(*,0), "OPERATORJOB" NUMBER(*,0), "ISROBOTNODE" CHAR(1), "SECLEVEL" VARCHAR2(10) ) SEGMENT CREATION IMMEDIATE PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING STORAGE(INITIAL 1016709120 NEXT 516096 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) TABLESPACE "LHRLOGY" LOB ("RECEIVEDPERSONS") STORE AS BASICFILE ( TABLESPACE "LHRLOGY" ENABLE STORAGE IN ROW CHUNK 8192 RETENTION NOCACHE LOGGING STORAGE(INITIAL 516096 NEXT 516096 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)) LOB ("RECEIVEDPERSONIDS") STORE AS BASICFILE ( TABLESPACE "LHRLOGY" ENABLE STORAGE IN ROW CHUNK 8192 RETENTION NOCACHE LOGGING STORAGE(INITIAL 516096 NEXT 516096 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)) LOB ("REMARKQUOTE") STORE AS BASICFILE ( TABLESPACE "LHRLOGY" ENABLE STORAGE IN ROW CHUNK 8192 RETENTION NOCACHE LOGGING STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)) LOB ("REMARK") STORE AS BASICFILE ( TABLESPACE "LHRLOGY" ENABLE STORAGE IN ROW CHUNK 8192 RETENTION NOCACHE LOGGING STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)) |
表的大小：
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 | SQL> set line 1000 SQL> SELECT 2 (SELECT trunc(SUM(S.BYTES)/1024/1024/1024) 3 FROM DBA_SEGMENTS S 4 WHERE S.OWNER = UPPER('LHRLOGY') AND 5 (S.SEGMENT_NAME = UPPER('LHRAFLOW_XXTABCDLOG'))) as tbsize_g, 6 (SELECT trunc(SUM(S.BYTES)/1024/1024/1024) 7 FROM DBA_SEGMENTS S, DBA_LOBS L 8 WHERE S.OWNER = UPPER('LHRLOGY') AND 9 (L.SEGMENT_NAME = S.SEGMENT_NAME AND L.TABLE_NAME = UPPER('LHRAFLOW_XXTABCDLOG') AND L.OWNER = UPPER('LHRLOGY'))) as lobsize_g, 10 (SELECT trunc(SUM(S.BYTES)/1024/1024/1024) 11 FROM DBA_SEGMENTS S, DBA_INDEXES I 12 WHERE S.OWNER = UPPER('LHRLOGY') AND 13 (I.INDEX_NAME = S.SEGMENT_NAME AND I.TABLE_NAME = UPPER('LHRAFLOW_XXTABCDLOG') AND INDEX_TYPE = 'LOB' AND I.OWNER = UPPER('LHRLOGY'))) as lobindexsize_g , 14 (select count(*) from LHRLOGY.LHRAFLOW_XXTABCDLOG) 15 FROM DUAL; TBSIZE_G LOBSIZE_G LOBINDEXSIZE_G (SELECTCOUNT(*)FROMLHRLOGY.LHRAFLOW_XXTABCDLOG) ---------- ---------- -------------- ----------------------------------------------- 15 41 0 31961861 |
可见，表才15g，但是clob字段就41g。
重新建立临时表，将STORE AS BASICFILE修改为STORE AS securefile
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 | CREATE TABLE "LHRLOGY"."TEST" ( "REQUESTID" NUMBER(*,0), "WORKFLOWID" NUMBER(*,0), "SIGNWORKFLOWIDS" VARCHAR2(1000), "RECEIVEDPERSONS" CLOB, "ISMOBILE" CHAR(1), "TMPHANDWRITTENSIGN" NUMBER(*,0), "SPEECHATTACHMENT" NUMBER(*,0), "RECEIVEDPERSONIDS" CLOB, "REMARKLOCATION" VARCHAR2(1000), "ISSUBMITDIRECT" CHAR(1), "REMARKQUOTE" CLOB, "FULLTEXTANNOTATION" VARCHAR2(1000), "SPEECHATTACHMENTE9" VARCHAR2(1000), "UUID" VARCHAR2(100), "REMARK" CLOB, "HANDWRITTENSIGN" VARCHAR2(300), "OPERATORSUB" NUMBER(*,0), "OPERATORJOB" NUMBER(*,0), "ISROBOTNODE" CHAR(1), "SECLEVEL" VARCHAR2(10) ) SEGMENT CREATION IMMEDIATE PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING STORAGE(INITIAL 1016709120 NEXT 516096 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) TABLESPACE "LHRLOGY" LOB ("RECEIVEDPERSONS") STORE AS securefile ( TABLESPACE "LHRLOGY" ENABLE STORAGE IN ROW CHUNK 8192 RETENTION NOCACHE LOGGING STORAGE(INITIAL 516096 NEXT 516096 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)) LOB ("RECEIVEDPERSONIDS") STORE AS securefile ( TABLESPACE "LHRLOGY" ENABLE STORAGE IN ROW CHUNK 8192 RETENTION NOCACHE LOGGING STORAGE(INITIAL 516096 NEXT 516096 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)) LOB ("REMARKQUOTE") STORE AS securefile ( TABLESPACE "LHRLOGY" ENABLE STORAGE IN ROW CHUNK 8192 RETENTION NOCACHE LOGGING STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)) LOB ("REMARK") STORE AS securefile ( TABLESPACE "LHRLOGY" ENABLE STORAGE IN ROW CHUNK 8192 RETENTION NOCACHE LOGGING STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)); |
然后导入数据：
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 | [oracle@oarac1 ~]$ impdp \'/ AS SYSDBA\' directory=D1 dumpfile=LHRAFLOW_XXTABCDLOG.dmp FULL=Y CLUSTER=N table_exists_action=APPEND REMAP_TABLE=LHRLOGY.LHRAFLOW_XXTABCDLOG:LHRLOGY.TEST1 Import: Release 11.2.0.4.0 - Production on Sun Nov 27 11:50:31 2022 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP, Data Mining and Real Application Testing options Master table "SYS"."SYS_IMPORT_FULL_05" successfully loaded/unloaded Starting "SYS"."SYS_IMPORT_FULL_05": "/******** AS SYSDBA" directory=D1 dumpfile=LHRAFLOW_XXTABCDLOG.dmp FULL=Y CLUSTER=N table_exists_action=APPEND REMAP_TABLE=LHRLOGY.LHRAFLOW_XXTABCDLOG:LHRLOGY.TEST1 Processing object type TABLE_EXPORT/TABLE/TABLE Processing object type TABLE_EXPORT/TABLE/TABLE_DATA ^C Import> status Job: SYS_IMPORT_FULL_05 Operation: IMPORT Mode: FULL State: EXECUTING Bytes Processed: 0 Current Parallelism: 1 Job Error Count: 0 Dump File: /data/LHRAFLOW_XXTABCDLOG.dmp Worker 1 Status: Process Name: DW00 State: EXECUTING Object Schema: LHRLOGY Object Name: LHRAFLOW_XXTABCDLOG Object Type: TABLE_EXPORT/TABLE/TABLE_DATA Completed Objects: 1 Completed Rows: 19,864,843 Completed Bytes: 12,089,455,616 Percent Done: 62 Worker Parallelism: 1 Import> status Job: SYS_IMPORT_FULL_05 Operation: IMPORT Mode: FULL State: EXECUTING Bytes Processed: 0 Current Parallelism: 1 Job Error Count: 0 Dump File: /data/LHRAFLOW_XXTABCDLOG.dmp Worker 1 Status: Process Name: DW00 State: EXECUTING Object Schema: LHRLOGY Object Name: LHRAFLOW_XXTABCDLOG Object Type: TABLE_EXPORT/TABLE/TABLE_DATA Completed Objects: 1 Completed Rows: 21,615,289 Completed Bytes: 12,089,455,616 Percent Done: 67 Worker Parallelism: 1 Import> |
等待导入完成后，然后继续进行导出：
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 | [oracle@oarac1 ~]$ expdp \'/ AS SYSDBA\' directory=D1 dumpfile=TEST.dmp EXCLUDE=STATISTICS tables="LHRLOGY"."TEST" CLUSTER=N COMPRESSION=ALL Export: Release 11.2.0.4.0 - Production on Sun Nov 27 11:55:34 2022 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP, Data Mining and Real Application Testing options Starting "SYS"."SYS_EXPORT_TABLE_01": "/******** AS SYSDBA" directory=D1 dumpfile=TEST.dmp EXCLUDE=STATISTICS tables=LHRLOGY.TEST CLUSTER=N COMPRESSION=ALL Estimate in progress using BLOCKS method... Processing object type TABLE_EXPORT/TABLE/TABLE_DATA Total estimation using BLOCKS method: 56.92 GB Processing object type TABLE_EXPORT/TABLE/TABLE Processing object type TABLE_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX Processing object type TABLE_EXPORT/TABLE/TRIGGER . . exported "LHRLOGY"."TEST" 11.25 GB 31961861 rows Master table "SYS"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded ****************************************************************************** Dump file set for SYS.SYS_EXPORT_TABLE_01 is: /data/TEST.dmp Job "SYS"."SYS_EXPORT_TABLE_01" successfully completed at Sun Nov 27 12:02:39 2022 elapsed 0 00:06:04 |
可见，速度提升很快。
DataPump Export (EXPDP) Is Taking Long time To Export Tables With BASICFILES (Doc ID 1595380.1)
APPLIES TO:
Oracle Database - Enterprise Edition - Version 11.2.0.3 to 12.1.0.2 [Release 11.2 to 12.1]
Oracle Database Cloud Schema Service - Version N/A and later
Oracle Database Exadata Cloud Machine - Version N/A and later
Oracle Database Exadata Express Cloud Service - Version N/A and later
Oracle Cloud Infrastructure - Database Service - Version N/A and later
Information in this document applies to any platform.
SYMPTOMS
Datapump export is taking more than the double time after a database upgrade from 10.2.0.4 to 11.2.0.3
In our example, one table with a LOB column takes more than 18 hours. The rest of the objects are exported within 30 mins.
Here is the current size of the table with a blob column based on the output of the export..
. . exported "
本人提供Oracle(OCP、OCM)、MySQL(OCP)、PostgreSQL(PGCA、PGCE、PGCM)等数据库的培训和考证业务，私聊QQ646634621或微信dbaup66，谢谢！
