ORA-02143: invalid STORAGE option --DSG oracle 11g 复制数据到oracle 10g-优快云博客

本文链接：https://blog.youkuaiyun.com/naguang/article/details/8079504

本文介绍了解决Oracle数据库中ORA-02143错误的方法，该错误通常由于使用了不兼容的STORAGE选项引起。文章详细解释了如何通过调整SQL语句来避免使用特定于Oracle 11g的FLASH_CACHE选项，从而确保可以在Oracle 10g环境中正确执行。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

ORA-02143: invalid STORAGE option
ORA-02143: invalid STORAGE option
SQL: alter table "user"."table_name" ADD CONSTRAINT "table_PK" PRIMARY KEY ("ID")
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
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)
TABLESPACE test ENABLE

报错原因：oracle 11g新增了一个存储选项 FLASH_CACHE，oracle 10g不支持此选项

FLASH_CACHE

The FLASH_CACHE clause lets you override the automatic buffer cache policy and specify how specific schema objects are cached in flash memory. To use this clause, Database Smart Flash Cache (flash cache) must be configured on your system. The flash cache is an extension of the database buffer cache that is stored on a flash disk, a storage device that uses flash memory. Because flash memory is faster than magnetic disks, the database can improve performance by caching buffers in the flash cache instead of reading from magnetic disk.

KEEP SpecifyKEEP if you want the schema object buffers to remain cached in the flash cache as long as the flash cache is large enough.

NONE SpecifyNONE to ensure that the schema object buffers are never cached in the flash cache. This allows you to reserve the flash cache space for more frequently accessed objects.

DEFAULT SpecifyDEFAULT if you want the schema object buffers to be written to the flash cache when they are aged out of main memory, and then be aged out of the flash cache with the standard buffer cache replacement algorithm. This is the default if flash cache is configured and you do not specifyKEEP orNONE.

Note:

Database Smart Flash Cache is available only in Solaris and Oracle Linux.

解决办法：

在源端oracle 11g抽取sql语句是去掉存储选项，

在源端 config 下编辑 session_prev.sql 文件
配置如下：
declare
sql_str varchar2(500);
begin
DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'STORAGE',false);
end;

重新发起全同步问题解决