**ORA-43853:** SECUREFILE lobs cannot be used in non-ASSM tablespace “string”

ORA-43853

[oracle@testosa:/home/oracle]$ oerr ora 43853
43853, 0000, "SECUREFILE lobs cannot be used in non-ASSM tablespace \"%s\""
// *Document: YES
// *Cause:    SECUREFILE lobs require ASSM
// *Action:   Specify an ASSM tablespace for SECUREFILE lobs.
//43854, 0000, "Deprecated"
// *Document: No
// *Cause:    Don't use this for external errors.  Was used in 11.1.0.6.0.
// *Action:
[oracle@testosa:/home/oracle]$

文档解释

ORA-43853: SECUREFILE lobs cannot be used in non-ASSM tablespace “string”

Cause: SECUREFILE lobs require ASSM

Action: Specify an ASSM tablespace for SECUREFILE lobs. 43854, 0000, “Deprecated”

ORA-43853: SECUREFILE lobs不能够在非ASSM表空间中使用。

官方解释

ORA-43853 “SECUREFILE lobs cannot be used in non-ASSM tablespace string” indicates that the SECUREFILE lob feature has been enabled and a large object (LOB) column has been created in a tablespace that does not use Automatic Segment Space Management (ASSM).

Secure File (LOB) is a feature introduced in Oracle 10g which provides additional secure options for LOBs like Encryption, compression and de-duplication etc. It is not supported to use this feature in non-ASSM tablespaces due to the complexity of managing the segment space.

常见案例

ORA-43853异常常见于以下情况：

在Oracle 10g或更高版本中，尝试在非ASSM表空间中创建一个启用了SECUREFILE LOBs的LOB字段；

在使用ALTER TABLE语句启用SECUREFILE LOBs字段功能的表空间中；

一般处理方法及步骤

为了解决ORA-43853异常，您可以按照以下步骤来处理：

只有在表空间使用automatic segment space management (ASSM)时，才能启用SECUREFILE LOBs字段功能。检查错误中提到的表空间，确定其是否使用了ASSM。如果是，则可以尝试重建表空间，以重新启用SECUREFILE属性。

将表空间转换为使用大对象（LOB）字段时，必须使用automatic segment space management (ASSM)才能启用SECUREFILE LOBs功能。因此，应该首先建立新的ASSM表空间，将原始表中的数据转移到新的表空间中，然后在该表空间中启用SECUREFILE LOBs字段功能。

Exadata to Non Exadata ORA-64307 HCC not supported

当从Oracle Exadata工程系统迁移到非Exadata环境时，或配置Datagurad时需要注意，有些功能时Exadata专属特性，如EHCC (Exadata Hybrid Columnar Compression )的表对象在standby 查询或使用datapump迁移时会遇到下面的错误:

ORA-64307: hybrid columnar compression is only supported in tablespaces residing on Exadata storage
-- or --

CREATE TABLE "ANBOB"."BILLING_XXXXX" ("BILLING_FREQUENCY" NUMBER(3,0) NOT NULL ENABLE, .. "DISPLAY_VALUE" VARCHAR2(240 BYTE) NOT NULL ENABLE) SEGMENT CREATION IMMEDIATE PCTFREE 0 PCTUSED 40 INITRANS 1 MAXTRANS 255 COLUMN STORE COMPRESS FOR QUERY HIGH NO ROW LEVEL LOCKING NOLOGGING 
STORAGE(INITIAL 81920 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) TABLESPACE "USERS"

ORA-39083: Object type TABLE:"ANBOB"."XXXXX" failed to create with error:
ORA-64307: Exadata Hybrid Columnar Compression is not supported for tablespaces on this storage type

Exadata Hybrid Columnar Compression (EHCC)

• EHCC is Exadata-specific and can realize both significant savings AND performance benefits:
• EHCC can be applied to tables and partitions.
• Compression Units (CU) stores groups of rows in a table in columnar format.
• Values of each column stored and compressed together.
• More data compressed at once = larger CU = better compression.

Am I Using EHCC?

• ‘%cell CU%’ statistics show EHCC usage.
• 2 statistics determine rows / compression unit:
• EHCC Total Rows for Decompression – e.g. 5,000,000,000
• EHCC CUs Decompressed – e.g. 5,000,000
• Average rows per EHCC compression unit = 1,000.
• Determine compression used for table / partition / row:
• DBMS_COMPRESSION procedures.
• COMPRESS_FOR column in DBA_TABLES / DBA_INDEXES.

EHCC支持的压缩级别为：

• COMPRESS FOR QUERY LOW
• COMPRESS FOR QUERY High
• COMPRESS FOR ARCHIVE LOW
• COMPRESS FOR ARCHIVE HIGH

解决方法：

1、对于DATAGUARD环境，需要在primary side上先解压

alter table anbob.xxx move nocompress;

Note: You may also convert HCC tables using the following compression options on third party storage: Basic Table Compression [compress/compress basic], or OLTP compression[ compress for oltp] . OLTP compression requires a license for the Oracle Advanced Compression Option.

2、对于datapump环境，增加忽略storage选项： transform = segment_attributes: n: table

impdp \"/ as sysdba\" SCHEMAS=Anobob directory=PUMP dumpfile=xx%U.dmp logfile=xxx.log cluster=n parallel=4   transform = segment_attributes: n: table