Oracle 32k字段扩展使用测试

Oracle 32k字段扩展使用测试

12C以上可以使用32k扩展

下面来一组测试，在18c环境创建一张表，使用32k字段varchar2

SQL> create table tab32k (name varchar2(32768));
create table tab32k (name varchar2(32768))
                                   *
ERROR at line 1:
ORA-00910: specified length too long for its datatype


SQL> create table tab32k (name varchar2(32767));

Table created.

---->32k实际最大是32767字节

SQL> select userenv('language') from dual;

USERENV('LANGUAGE')
----------------------------------------------------
AMERICAN_AMERICA.AL32UTF8
18c环境是utf8

数据插入测试

UTF8一个中文是3个字节，32k扩展最大值为32767，32767➗3=10922.3
测试实际插入为10922个中文

导出dmp到11g测试

[ora18c@oradb dump]$ expdp admin/oracle@pdb18c directory=dump dumpfile=ora18c_admin.dmp schemas=admin version=11.2.0.4

warning: Oracle Data Pump is exporting from a database that supports long identifiers to a version that does not support long identifiers.

dmp解析为sql, 测试下来不会导出32k扩展之后的表
[ora18c@oradb dump]$ vi m.sql

-- CONNECT ADMIN
ALTER SESSION SET EVENTS '10150 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '10904 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '25475 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '10407 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '10851 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '22830 TRACE NAME CONTEXT FOREVER, LEVEL 192 ';
-- new object type path: SCHEMA_EXPORT/USER
 CREATE USER "ADMIN" IDENTIFIED BY VALUES 'S:7A5ACCB1287AB38907CAFFF503424A0D675A2715EB61A2593620AFC33C92;T:DE8BE8ECA479D976F2235D53C3AED03C36EDEED3
4943A545DE4A53BEA9E334A5AC5457FDB57F647B98CCD7F84B4264DC7DD68F7FB127D321D2812DCCD7DC187D573F40CFABEFCE73117169B87BFEBDFD' DEFAULT COLLATION "USING_N
LS_COMP"
      DEFAULT TABLESPACE "ADMIN"
      TEMPORARY TABLESPACE "TEMP";
-- new object type path: SCHEMA_EXPORT/SYSTEM_GRANT
GRANT UNLIMITED TABLESPACE TO "ADMIN";
-- new object type path: SCHEMA_EXPORT/ROLE_GRANT
 GRANT "DBA" TO "ADMIN";
-- new object type path: SCHEMA_EXPORT/DEFAULT_ROLE
 ALTER USER "ADMIN" DEFAULT ROLE ALL;
-- new object type path: SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA

BEGIN
sys.dbms_logrep_imp.instantiate_schema(schema_name=>SYS_CONTEXT('USERENV','CURRENT_SCHEMA'), export_db_name=>'PDB18C', inst_scn=>'1589586');
COMMIT;
END;
/

导入到19c

ORA-39083: Object type TABLE:"ADMIN"."TAB32K" failed to create with error:
ORA-00910: specified length too long for its datatype

Failing sql is:
CREATE TABLE "ADMIN"."TAB32K" ("NAME" VARCHAR2(32767 BYTE)) SEGMENT CREATION IMMEDIATE PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255  NOCOMPRESS 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) TABLESPACE "ADMIN" 

因为19c未开启32k扩展，同样报错