12c新特性-扩展VARCHAR2、NVARCHAR2、RAW类型长度到32767字节
Oct252013
今天来测试下12C的另一个新特性-Increased Size Limit for VARCHAR2, NVARCHAR2, and RAW Data Types。在12C版本的数据库，VARCHAR2、NVARCHAR2类型的字段长度，从以前的4000字节扩展到32767字节，RAW类型的字段长度由原来的2000字节扩展到32767字节。下面看下官方文档的描述：
The maximum size of the VARCHAR2, NVARCHAR2, and RAW data types has been increased from 4,000 to 32,767 bytes. Increasing the allotted size for these data types allows users to store more information in character data types before switching to large objects (LOBs). This is especially useful for brief textual data types and the capabilities to build indexes on these types of columns. See Also: Oracle Database SQL Language Reference for details
在这里，ORACLE只告诉有这么个功能，算是功能介绍，具体怎么用还需要看红色部分的链接。点击Oracle Database SQL Language Reference连接，跳转到Extended Data Types部分才会看到要用这个功能，需要哪些前提条件。
Beginning with Oracle Database 12c, you can specify a maximum size of 32767 bytes for the VARCHAR2, NVARCHAR2, and RAW data types. You can control whether your database supports this new maximum size by setting the initialization parameter MAX_STRING_SIZE as follows: •If MAX_STRING_SIZE = STANDARD, then the size limits for releases prior to Oracle Database 12c apply: 4000 bytes for the VARCHAR2 and NVARCHAR2 data types, and 2000 bytes for the RAW data type. This is the default. •If MAX_STRING_SIZE = EXTENDED, then the size limit is 32767 bytes for the VARCHAR2, NVARCHAR2, and RAW data types. Note: Setting MAX_STRING_SIZE = EXTENDED may update database objects and possibly invalidate them. Refer to Oracle Database Reference for complete information on the implications of this parameter and how to set and enable this new functionality.
在这里，ORACLE告诉了如果要用扩展VARCHAR2等字段长度到32767字节，需要调整MAX_STRING_SIZE参数，这个参数有两个值，默认是STANDARD，也就是不支持32767长度，还有一个值是EXTENDED，也就是支持32767长度。在这里ORACLE还提示修改MAX_STRING_SIZE参数会修改一些对象的信息，必须要将数据库打开到升级模式。具体怎么修改MAX_STRING_SIZE参数，还需要在看红色部分的链接。打开Oracle Database Reference链接，就会看到很详细的修改MAX_STRING_SIZE参数的过程以及范例。
修改CDB中MAX_STRING_SIZE参数方法如下：从这部分可以看到，这个特性不支持CDB。
Increasing the Maximum Size of VARCHAR2, NVARCHAR2, and RAW Columns in a Non-CDB
To increase the maximum size of VARCHAR2, NVARCHAR2, and RAW columns in a non-CDB: 1.Shut down the database. 2.Restart the database in UPGRADE mode. 3.Change the setting of MAX_STRING_SIZE to EXTENDED. 4.Run the rdbms/admin/utl32k.sql script. You must be connected AS SYSDBA to run the script. 5.Restart the database in NORMAL mode. Note: The utl32k.sql script increases the maximum size of the VARCHAR2, NVARCHAR2, and RAW columns for the views where this is required. The script does not increase the maximum size of the VARCHAR2, NVARCHAR2, and RAW columns in some views because of the way the SQL for those views is written.
修改PDB中的MAX_STRING_SIZE参数方法如下：
Increasing the Maximum Size of VARCHAR2, NVARCHAR2, and RAW Columns in a PDB
To increase the maximum size of VARCHAR2, NVARCHAR2, and RAW columns in a PDB: 1.Shut down the PDB. 2.Restart the PDB in UPGRADE mode. 3.Change the setting of MAX_STRING_SIZE in the PDB to EXTENDED. 4.Run the rdbms/admin/utl32k.sql script in the PDB. You must be connected AS SYSDBA to run the utl32k.sql script. 5.Restart the PDB in NORMAL mode. Note: The utl32k.sql script increases the maximum size of the VARCHAR2, NVARCHAR2, and RAW columns for the views where this is required. The script does not increase the maximum size of the VARCHAR2, NVARCHAR2, and RAW columns in some views because of the way the SQL for those views is written.
可见无论是CDB还是PDB，修改MAX_STRING_SIZE参数都需要在升级模式运行utl32k.sql脚本，下面做下测试。
如果MAX_STRING_SIZE参数值为默认值STANDARD，那么创建或修改VARCHAR2长度超过4000字节时就会报 ORA-00910错误。
SQL> show parameter MAX_STRING_SIZE
NAME TYPE VALUE
------------------------------------ ---------------------- ------------------------------
max_string_size string STANDARD
SQL> create table cha_test(text varchar2(32767));
create table cha_test(text varchar2(32767))
*
ERROR at line 1:
ORA-00910: specified length too long for its datatype
下面根据官方文档的描述，来修改MAX_STRING_SIZE参数。这是个静态参数，正常模式指定SCPOE=SPFILE修改这个参数也可以成功，但是没有作用。
SQL> alter system set MAX_STRING_SIZE=EXTENDED; alter system set MAX_STRING_SIZE=EXTENDED * ERROR at line 1: ORA-02097: parameter cannot be modified because specified value is invalid ORA-02095: specified initialization parameter cannot be modified SQL> alter system set MAX_STRING_SIZE=EXTENDED scope=spfile; System altered. ----------不重启不会生效，重启会告诉需要运行utl32k.sql脚本，详见下文。
下面按照官方文档的步骤，来修改这个参数，将数据库启动到升级模式。
SQL> shutdown immediate Database closed. Database dismounted. ORACLE instance shut down. SQL> startup UPGRADE ORACLE instance started. Total System Global Area 413372416 bytes Fixed Size 2289016 bytes Variable Size 322962056 bytes Database Buffers 79691776 bytes Redo Buffers 8429568 bytes Database mounted. Database opened.
修改MAX_STRING_SIZE参数。
SQL> alter system set MAX_STRING_SIZE=EXTENDED scope=spfile; System altered.
运行utl32k.sql脚本,该脚本运行时间较长，上趟洗手间，出去抽根烟，还没跑完，吃完午饭回来才跑完，要有足够的耐心。
SQL> @?/rdbms/admin/utl32k.sql ----日志略
重启数据库。
SQL> shutdown immediate Database closed. Database dismounted. ORACLE instance shut down. SQL> startup ORACLE instance started. Total System Global Area 413372416 bytes Fixed Size 2289016 bytes Variable Size 322962056 bytes Database Buffers 79691776 bytes Redo Buffers 8429568 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: 10489 Session ID: 237 Serial number: 5
修改这个参数，PDB$SEED也需要一起修改，要不数据库打不开，数据库打不开也不要紧张，继续将数据库打开到升级模式。
SQL> startup upgrade ORACLE instance started. Total System Global Area 413372416 bytes Fixed Size 2289016 bytes Variable Size 322962056 bytes Database Buffers 79691776 bytes Redo Buffers 8429568 bytes Database mounted. Database opened.
切换到PDB$SEED容器。
SQL> ALTER SESSION SET CONTAINER = PDB$SEED; Session altered.
此时在PDB$SEED容器里，MAX_STRING_SIZE参数已经修改为extended。
SQL> show parameter max_string_size NAME TYPE VALUE ------------------------------------ ---------------------- ------------------------------ max_string_size string EXTENDED
那为什么数据库打不开，还报上面的错误呢？这是因为PDB$SEED并未运行utl32k.sql脚本，只需要在PDB_SEED容器里运行这个脚本即可。
SQL> @?/rdbms/admin/utl32k.sql --日志略
同样漫长的等待，时间不要浪费，去看看官方文档介绍的其他新特性，跑完了重启数据库，数据库可以正常打开。
SQL> shutdown immediate Database closed. Database dismounted. ORACLE instance shut down. SQL> startup ORACLE instance started. Total System Global Area 413372416 bytes Fixed Size 2289016 bytes Variable Size 322962056 bytes Database Buffers 79691776 bytes Redo Buffers 8429568 bytes Database mounted. Database opened.
数据库已经可以打开，在CDB创建个表试试可不可以使用这个特性。
SQL> create table var_test(text varchar2(32767));
create table var_test(text varchar2(32767))
*
ERROR at line 1:
ORA-00910: specified length too long for its datatype
CDB果然不支持这个特性，在看看PDB。
SQL> select NAME,OPEN_MODE from v$pdbs NAME OPEN_MODE -------------------- -------------------- PDB$SEED READ ONLY STREAM MOUNTED WIND MOUNTED
我只有CDB和PDB$SEED运行了utl32k.sql,另两个PDB都没有运行这个脚本，而SEED又是只读的，那就把这两个PDB也跑下脚本。
SQL> shutdown immediate Database closed. Database dismounted. ORACLE instance shut down. SQL> startup upgrade ORACLE instance started. Total System Global Area 413372416 bytes Fixed Size 2289016 bytes Variable Size 322962056 bytes Database Buffers 79691776 bytes Redo Buffers 8429568 bytes Database mounted. Database opened. SQL> ALTER SESSION SET CONTAINER=stream; Session altered. SQL> select status from v$instance; STATUS ------------------------ MOUNTED SQL> alter database open upgrade; Database altered. SQL> @?/rdbms/admin/utl32k.sql ================================================================================================================= SQL> ALTER SESSION SET CONTAINER=CDB$ROOT; Session altered. SQL> ALTER SESSION SET CONTAINER=wind; Session altered. SQL> select status from v$instance; STATUS ------------------------ MOUNTED SQL> alter database open upgrade; Database altered. SQL> @?/rdbms/admin/utl32k.sql
两个小时过去了，看来做测试时PDB多也不是好事，重启数据库。
SQL> ALTER SESSION SET CONTAINER=CDB$ROOT; Session altered. SQL> shutdown immediate Database closed. Database dismounted. ORACLE instance shut down. SQL> startup ORACLE instance started. Total System Global Area 413372416 bytes Fixed Size 2289016 bytes Variable Size 322962056 bytes Database Buffers 79691776 bytes Redo Buffers 8429568 bytes Database mounted. Database opened.
看看PDB中是否已经可以使用这个特性？
SQL> select name,open_mode from v$pdbs; NAME OPEN_MODE ------------------- -------------------- PDB$SEED READ ONLY STREAM MOUNTED WIND MOUNTED SQL> ALTER SESSION SET CONTAINER=stream; Session altered. SQL> alter database open; Database altered. SQL> create table var_test(text varchar2(32767)); Table created.
PDB已经可以了，这个特性只有在PDB或非CDB使用，CDB还不支持，不知道在以后的版本会不会让CDB也支持这个特性。
12c新特性-扩展VARCHAR2、NVARCHAR2、RAW类型长度到32767字节：等您坐沙发呢！
发表评论
