12c 可扩展的字符类型MAX_STRING_SIZE与常见错误

12c推出了varchar2的可扩展特性，从4000B增加到了32727B。许多超过4000B的文本内容除了使用CLOb字段，现在可以考虑使用扩展后的varchar2。但是扩展操作不是Oracle12c默认支持的，需要手工开启才能生效。传统方式创建的数据库操作相对简单，可插拔库过程复杂些。我们从简单的入手，先看看传统数据库时如何开启扩展的。

1. 关闭数据库

$ shutdown immediate;

2. 启动数据库到升级模式

SYS@OCM12C >startup upgrade;

3. 修改max_string_size为EXTENDED，默认是standard

SYS@OCM12C >alter system set max_string_size=EXTENDED scope=both;

4. 使用SYS用户执行脚本

SYS@OCM12C >@?/rdbms/admin/utl32k.sql

5. 重新启动数据库

SYS@OCM12C >shutdown immediate;

SYS@OCM12C >startup

6. 在users表空间下创建表，varchar2字段可超过4000。在12c之前是做不到这一点的。

SYS@OCM12C >create table t3(blog varchar2(4001)) tablespace users;

7. 扩展后字段最大可支持到32727B大小

SYS@OCM12C >create table t4(blog varchar2(32727)) tablespace users;

下面看看可插拔数据库PDB是如何操作的，注意所有操作只针对PDB,不要去操作CDB。

1. 关闭PDB数据库

[oracle@snow ~]$ sqlplus / as sysdba

SYS@cdb > show pdbs;

CON_ID CON_NAME OPEN MODE RESTRICTED

---------- ------------------------------ ---------- ----------

2 PDB$SEED READ ONLY NO

3 PDB1 READ WRITE NO

SYS@cdb > alter pluggable database pdb1 close;

Pluggable database altered.

2. 重新启动PDB到migrate模式

SYS@cdb > alter pluggable database pdb1 open upgrade;

Pluggable database altered.

3. 修改max_string_size参数为extended

SYS@cdb > alter system set max_string_size=extended scope=both;

System altered.

4. 使用SYS用户执行脚本

SYS@cdb > @?/rdbms/admin/utl32k.sql

省去部分内容

...Database user "SYS", database schema "APEX_040200", user# "98" 10:49:54

...Compiled 0 out of 2998 objects considered, 0 failed compilation 10:49:54

...263 packages

...255 package bodies

...453 tables

...11 functions

...16 procedures

...3 sequences

...458 triggers

...1322 indexes

...207 views

...0 libraries

...6 types

...0 type bodies

...0 operators

...0 index types

...Begin key object existence check 10:49:54

...Completed key object existence check 10:49:54

...Setting DBMS Registry 10:49:54

...Setting DBMS Registry Complete 10:49:54

...Exiting validate 10:49:54

PL/SQL procedure successfully completed.

5. 重新启动PDB数据库

SYS@cdb > alter pluggable database pdb1 close;

Pluggable database altered.

SYS@cdb > alter pluggable database open;

Pluggable database altered.

6. 创建扩展列的varchar2表

SYS@cdb > create table t1(blog varchar2(32727)) tablespace users;

Table created.

常见错误：ORA-14696: MAX_STRING_SIZE migration is incomplete for pluggable database

注意：扩展操作是在PDB层面，而不是在CDB层面。

如果将CDB执行以上操作，会在执行完脚本rdbms/admin/utl32k.sql后重新启动数据库报错。

SYS@cdb > startup;  

ORACLE instance started.

Total System Global Area 1553305600 bytes

Fixed Size 2288728 bytes

Variable Size 939525032 bytes

Database Buffers 603979776 bytes

Redo Buffers 7512064 bytes

Database mounted.

ORA-01092: ORACLE instance terminated. Disconnection forced

ORA-14696: MAX_STRING_SIZE migration is incomplete for pluggable database

PDB$SEED

Process ID: 20105

Session ID: 237 Serial number: 5

这段报错的意识是说CDB数据库已经是migrate状态了（startup upgrade），但是PDB$SEED还不是。需要对PDB$SEED做写操作。

SYS@cdb > shutdown immediate;

SYS@cdb > startup mount;

SYS@cdb > alter database open migrate;

我们需要PDB$SEED处于MIGRATE状态

SYS@cdb > select con_id,name,open_mode from v$pdbs;

CON_ID     NAME                           OPEN_MODE

---------- ------------------------------ ----------

2          PDB$SEED                       MIGRATE

3          PDB1                           MOUNTED

SYS@cdb > alter session set container=pdb$seed;

SYS@cdb > show con_name

SYS@cdb > alter system set max_string_size = extended scope=both;

SYS@cdb > @?/rdbms/admin/utl32k.sql

当PDB$SEED处理结束后，接下来可以继续做PBD的扩展

SYS@cdb > alter pluggable database pdb1 close;

SYS@cdb > alter pluggable database pdb1 open upgrade;

SYS@cdb > alter system set max_string_size=extended scope=both;

SYS@cdb > @?/rdbms/admin/utl32k.sql

SYS@cdb > alter pluggable database pdb1 close;

SYS@cdb > alter pluggable database open;

SYS@cdb > create table t1(blog varchar2(32727)) tablespace users;


全文完

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/29047826/viewspace-1457479/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/29047826/viewspace-1457479/