Database SQL Language Reference ---- 》》 Data Types

在Oracle Database 12c中，可以指定 VARCHAR2 、 NVARCHAR2 和 RAW 数据类型的最大大小为 32767 字节。这样，用户便可以在数据库中存储更长的字符串。在本发行版之前， VARCHAR2和 NVARCHAR2 数据类型的最大大小为 4000 字节， RAW 数据类型的最大大小为 2000 字节 。声明的VARCHAR2、 NVARCHAR2 或 RAW 列长度影响如何在内部存储列。

•声明列长度为 4000 字节或更少的 VARCHAR2 和 NVARCHAR2 列以及声明列长度为 2000 字节或更少的 RAW 列在行内存储。

•声明列长度大于 4000 字节的 VARCHAR2 和 NVARCHAR2 列以及声明列长度大于 2000 字节的 RAW 列称为“扩展字符数据类型列”，它们在行外存储。

MAX_STRING_SIZE控制 SQL 中扩展数据类型的最大大小：

• STANDARD 表示在 Oracle 12c 之前使用的数据类型长度限制。

• EXTENDED 表示 Oracle Database 12c 中的 32767 字节限制。

扩展字符数据类型具有以下限制：

•在聚簇表和按索引组织的表中不受支持。

•没有分区内并行 DDL 、 UPDATE 和 DELETE DML 。

•对于在使用自动段空间管理 (Automatic Segment Space Management,ASSM) 进行管理的表空间中存储的表，没有分区内并行直接路径插入。

对比 LOB 数据类型，在 ASSM 表空间管理中，扩展数据类型的字段以 SecureFiles LOB 加以存储，而在非 ASSM 表空间管理中，它们则是以 BasciFiles LOB 进行存储的。

注意点：

Ø 不能将值从EXTENDED更改为 STANDARD 。 The only way to revert is to restore the database from backup prior to running the conversion script, $ORACLE_HOME/rdbms/admin/utl32k.sql

Ø 在RAC环境中，要关闭所有实例。只有将数据库实例参数 MAX_STRING_SIZE 设置为 EXTENDED 后，才能创建包含扩展字符数据类型列的表。 RAC 环境中所有 RAC 节点上 MAX_STRING_SIZE 初始化参数的值也必须相同。

Ø 需要特别注意的是：

①　 Oracle 12c中的扩展的数据类型只是针对 非CDB 和 PDB 而言的，而 CDB的根容器不支持扩展的数据类型 ，即使在根容器中修改成功也不能在CDB的根容器中创建超过 4000 字节的列。

②　 从Oracle 12.2版本开始，可以在不修改 MAX_STRING_SIZE 参数的情况下创建最大 32767 字节的列，但是，实际最大存储依然是 4000 字节。所以，要想真正支持扩展数据类型，那么必须修改 MAX_STRING_SIZE 参数，而且运行相关脚本 utl32k.sql 和 utlrp.sql 。

与早期版本相比，诸如 VARCHAR2, NAVARCHAR2 这些数据类型的大小会从 4K 字节扩展至 32K 数据类型的使用。为了启用扩展字符大小，你必须将 MAX_STRING_SIZE 。

要使用扩展字符类型需要执行以下过程：
1.关闭数据库
2.以升级模式重启数据库
3.更改参数: ALTER SYSTEM SET MAX_STRING_SIZE=EXTENDED;
4.执行 utl32k.sql as sysdba :SQL> @?/rdbms/admin/utl32k.sql
5.关闭数据库
6.以读写模式重启数据库

varcha2、nvarchar2和raw字段的定义长度将影响字段的内部存储方式

• STANDARD 代表12c之前的长度限制，即varchar2、nvarchar2 4000 bytes， raw 是2000 bytes

• EXTENDED 代表12c 32k strings新特性，varchar2、nvarchar2、raw最大长度32k bytes

Extended character data types 扩展字符类型存在以下的限制：

• 4. 当然我们也可以将已有的VARCHAR2, NVARCHAR2, 和RAW字段修改其长度， 具体使用ALTER TABLE MODIFY (COLUMN 命令。在此场景中Oracle将实施块中的长度扩展，而不将inline的存储迁移为以外部LOB存储。 实际上Oracle并不建议你广泛积极地将现有的varchar2的长度增加为4000 bytes以上，基于以下的原因： 现有字段上的索引无法实现数据类型扩展，所以必须先将字段上的索引drop掉，再修改为扩展长度，之后再重建索引。 关于32k varchar2 max_string_size 、extended character data type columns的一些演示： SQL> set linesize 200 pagesize 20000 SQL> select banner from v$version where rownum=1; BANNER ----------------------------------------------------------------------------------------- Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production SQL> show parameter MAX_STRING_SIZE NAME TYPE VALUE ------------------------------------ ---------------------- ----------------------------- max_string_size string EXTENDED SQL> CREATE TABLE long_varchar(id NUMBER,vc VARCHAR2(32767)); 表已创建。 SQL> DESC long_varchar 名称 是否为空? 类型 ----------------------------------------- -------- ---------------------------- ID NUMBER VC VARCHAR2(32767) SQL> insert into long_varchar values(1,rpad('MACLEAN',30000,'A')); 已创建 1 行。 SQL> commit; 提交完成。 SQL> alter system flush buffer_cache; 系统已更改。 SQL> select dbms_rowid.rowid_block_number(rowid),dbms_rowid.rowid_relative_fno(rowid) from long_varchar; DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID) DBMS_ROWID.ROWID_RELATIVE_FNO(ROWID) ------------------------------------ ------------------------------------ 97217 1 SQL> alter system dump datafile 1 block 97217; 系统已更改。 SQL> oradebug setmypid 已处理的语句 SQL> oradebug tracefile_name C:\APP\XIANGBLI\diag\rdbms\maclean\maclean\trace\maclean_ora_5688.trc tab 0, row 0, @0x1f65 tl: 59 fb: --H-FL-- lb: 0x1 cc: 2 col 0: [ 2] c1 02 col 1: [52] 00 54 00 01 01 0c 00 00 00 01 00 00 00 01 00 00 00 1f 50 05 00 20 05 00 00 00 00 03 15 e4 00 00 00 00 00 02 00 41 c5 73 00 41 c5 74 00 41 c5 75 00 41 c5 76 LOB Locator: Length: 84(52) Version: 1 Byte Length: 1 LobID: 00.00.00.01.00.00.00.1f.50.05 Flags[ 0x01 0x0c 0x00 0x00 ]: Type: BLOB Storage: BasicFile Enable Storage in Row Characterset Format: IMPLICIT Partitioned Table: No Options: ReadWrite Inode: Size: 32 Flag: 0x05 [ Valid InodeInRow(ESIR) ] Future: 0x00 (should be '0x00') Blocks: 3 Bytes: 5604 Version: 00000.0000000002 DBA Array[4]: 0x0041c573 0x0041c574 0x0041c575 0x0041c576 end_of_block_dump 可以看到原生的32k varchar实际以BasicFile BLOB的方式out-of-line存储 SQL> create table convert_long(t1 int,t2 varchar2(20)); 表已创建。 SQL> insert into convert_long values(1,'MACLEAN'); 已创建 1 行。 SQL> commit; 提交完成。 SQL> create index ind_cl on convert_long(t2); 索引已创建。 SQL> alter table convert_long modify t2 varchar2(32767); alter table convert_long modify t2 varchar2(32767) * 第 1 行出现错误: ORA-01404: ALTER COLUMN 将使索引过大 SQL> drop index ind_cl; 索引已删除。 SQL> alter table convert_long modify t2 varchar2(32767); 表已更改。 SQL> update convert_long set t2=rpad('MACLEAN',30000,'A'); 已更新 1 行。 SQL> commit; 提交完成。 SQL> alter system flush buffer_cache; 系统已更改。 SQL> select dbms_rowid.rowid_block_number(rowid),dbms_rowid.rowid_relative_fno(rowid) from convert_long; DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID) DBMS_ROWID.ROWID_RELATIVE_FNO(ROWID) ------------------------------------ ------------------------------------ 117121 1 SQL> oradebug setmypid 已处理的语句 SQL> oradebug tracefile_name C:\APP\XIANGBLI\diag\rdbms\maclean\maclean\trace\maclean_ora_4340.trc 可以看到形成了链式行 chained row tab 0, row 0, @0x7aa tl: 6120 fb: --H-F--N lb: 0x2 cc: 2 nrid: 0x0041c984.0 col 0: [ 2] c1 02 col 1: [6105] 4d 41 43 4c 45 41 4e 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41

     1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114

     当然我们也可以将已有的VARCHAR2, NVARCHAR2, 和RAW字段修改其长度， 具体使用ALTER TABLE MODIFY (COLUMN 命令。在此场景中Oracle将实施块中的长度扩展，而不将inline的存储迁移为以外部LOB存储。         实际上Oracle并不建议你广泛积极地将现有的varchar2的长度增加为4000 bytes以上，基于以下的原因：                   现有字段上的索引无法实现数据类型扩展，所以必须先将字段上的索引drop掉，再修改为扩展长度，之后再重建索引。                 关于32k varchar2 max_string_size 、extended character data type columns的一些演示：                   SQL> set linesize 200 pagesize 20000 SQL> select banner from v$version where rownum=1; BANNER ----------------------------------------------------------------------------------------- Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production SQL> show parameter MAX_STRING_SIZE NAME                                 TYPE                   VALUE ------------------------------------ ---------------------- ----------------------------- max_string_size                      string                 EXTENDED SQL> CREATE TABLE long_varchar(id NUMBER,vc VARCHAR2(32767)); 表已创建。 SQL> DESC long_varchar 名称                                      是否为空? 类型 ----------------------------------------- -------- ---------------------------- ID                                                 NUMBER VC                                                 VARCHAR2(32767) SQL> insert into long_varchar values(1,rpad('MACLEAN',30000,'A')); 已创建 1 行。 SQL> commit; 提交完成。 SQL> alter system flush buffer_cache; 系统已更改。 SQL> select dbms_rowid.rowid_block_number(rowid),dbms_rowid.rowid_relative_fno(rowid) from long_varchar; DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID) DBMS_ROWID.ROWID_RELATIVE_FNO(ROWID) ------------------------------------ ------------------------------------                             97217                                    1 SQL> alter system dump datafile 1 block 97217; 系统已更改。 SQL> oradebug setmypid 已处理的语句 SQL> oradebug tracefile_name C:\APP\XIANGBLI\diag\rdbms\maclean\maclean\trace\maclean_ora_5688.trc tab 0, row 0, @0x1f65 tl: 59 fb: --H-FL-- lb: 0x1  cc: 2 col  0: [ 2]  c1 02 col  1: [52] 00 54 00 01 01 0c 00 00 00 01 00 00 00 01 00 00 00 1f 50 05 00 20 05 00 00 00 00 03 15 e4 00 00 00 00 00 02 00 41 c5 73 00 41 c5 74 00 41 c5 75 00 41 c5 76 LOB Locator: Length:        84(52) Version:        1 Byte Length:    1 LobID: 00.00.00.01.00.00.00.1f.50.05 Flags[ 0x01 0x0c 0x00 0x00 ]: Type: BLOB Storage: BasicFile Enable Storage in Row Characterset Format: IMPLICIT Partitioned Table: No Options: ReadWrite Inode: Size:     32 Flag:     0x05 [ Valid InodeInRow(ESIR) ] Future:   0x00 (should be '0x00') Blocks:   3 Bytes:    5604 Version:  00000.0000000002 DBA Array[4]:    0x0041c573 0x0041c574 0x0041c575 0x0041c576 end_of_block_dump                             可以看到原生的32k varchar实际以BasicFile BLOB的方式out-of-line存储 SQL> create table convert_long(t1 int,t2 varchar2(20)); 表已创建。 SQL> insert into convert_long values(1,'MACLEAN'); 已创建 1 行。 SQL> commit; 提交完成。 SQL> create index ind_cl on convert_long(t2); 索引已创建。 SQL> alter table convert_long modify t2 varchar2(32767); alter table convert_long modify t2 varchar2(32767) * 第 1 行出现错误: ORA-01404: ALTER COLUMN 将使索引过大 SQL> drop  index ind_cl; 索引已删除。 SQL> alter table convert_long modify t2 varchar2(32767); 表已更改。 SQL> update convert_long set t2=rpad('MACLEAN',30000,'A'); 已更新 1 行。 SQL> commit; 提交完成。 SQL> alter system flush buffer_cache; 系统已更改。 SQL>  select dbms_rowid.rowid_block_number(rowid),dbms_rowid.rowid_relative_fno(rowid) from convert_long; DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID) DBMS_ROWID.ROWID_RELATIVE_FNO(ROWID) ------------------------------------ ------------------------------------                            117121                                    1 SQL> oradebug setmypid 已处理的语句 SQL> oradebug tracefile_name C:\APP\XIANGBLI\diag\rdbms\maclean\maclean\trace\maclean_ora_4340.trc 可以看到形成了链式行 chained row tab 0, row 0, @0x7aa tl: 6120 fb: --H-F--N lb: 0x2  cc: 2 nrid:  0x0041c984.0 col  0: [ 2]  c1 02 col  1: [6105] 4d 41 43 4c 45 41 4e 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41

  5. c , 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:

  6. • If MAX_STRING_SIZE = STANDARD , then the size limits for releases prior to Oracle Database 12 Oracle Database Reference for complete information on the implications of this parameter and how to set and enable this new functionality.

A VARCHAR2 or NVARCHAR2 data type with a declared size of greater than 4000 bytes, or a RAW data type with a declared size of greater than 2000 bytes, is an data Oracle Database SecureFiles and Large Objects Developer's Guide for more information.

Note that, although you must set MAX_STRING_SIZE = EXTENDED in order to set the size of a RAW data type to greater than 2000 bytes, a RAW data type is stored as an out-of-line LOB only if it has a size of greater than 4000 bytes. For example, you must set MAX_STRING_SIZE = EXTENDED in order to declare a RAW(3000) data type. However, the column is stored inline.

You can use extended data types just as you would standard data types, with the following considerations:

• For special considerations when creating an index on an extended data type column, or when requiring an index to enforce a primary key or unique constraint, see "Creating an Index on an Extended Data Type Column" .

• If the partitioning key column for a list partition is an extended data type column, then the list of values that you want to specify for a partition may exceed the 4K byte limit for the partition bounds. See the size [ BYTE | CHAR ])

  Variable-length character string having maximum length size for VARCHAR2 . Minimum size )	Variable-length Unicode character string having maximum length size for NVARCHAR2 . The number of bytes can be up to two times size for UTF8 encoding. Maximum p [, p and scale p can range from 1 to 38. The scale p )]	A subtype of the NUMBER data type having precision p can range from 1 to 126 binary digits. A FLOAT value requires from 1 to 22 bytes.
  8	LONG	Character data of variable length up to 2 gigabytes, or 2 31 -1 bytes. Provided for backward compatibility.
  12	DATE	Valid date range from January 1, 4712 BC, to December 31, 9999 AD. The default format is determined explicitly by the NLS_DATE_FORMAT parameter or implicitly by the NLS_TERRITORY parameter. The size is fixed at 7 bytes. This data type contains the datetime fields YEAR , MONTH , DAY , HOUR , MINUTE , and SECOND . It does not have fractional seconds or a time zone.
  100	BINARY_FLOAT	32-bit floating point number. This data type requires 4 bytes.
  101	BINARY_DOUBLE	64-bit floating point number. This data type requires 8 bytes.
  180	TIMESTAMP [( fractional_seconds_precision is the number of digits in the fractional part of the SECOND datetime field. Accepted values of fractional_seconds_precision )] WITH TIME ZONE	All values of TIMESTAMP as well as time zone displacement value, where fractional_seconds_precision )] WITH LOCAL TIME ZONE	All values of TIMESTAMP WITH TIME ZONE , with the following exceptions:Data is normalized to the database time zone when it is stored in the database.When the data is retrieved, users see the data in the session time zone.The default format is determined explicitly by the NLS_TIMESTAMP_FORMAT parameter or implicitly by the NLS_TERRITORY parameter. The size is 7 or 11 bytes, depending on the precision.
  182	INTERVAL YEAR [( year_precision is the number of digits in the YEAR datetime field. Accepted values are 0 to 9. The default is 2. The size is fixed at 5 bytes.
  183	INTERVAL DAY [( fractional_seconds_precision )]	Stores a period of time in days, hours, minutes, and seconds, wherefractional_seconds_precision is the number of digits in the fractional part of the SECOND field. Accepted values are 0 to 9. The default is 6.The size is fixed at 11 bytes.
  23	RAW ( size bytes. You must specify size is:32767 bytes if MAX_STRING_SIZE = EXTENDED2000 bytes if MAX_STRING_SIZE = STANDARDRefer to "Extended Data Types" for more information on the MAX_STRING_SIZE initialization parameter.
  24	LONG RAW	Raw binary data of variable length up to 2 gigabytes.
  69	ROWID	Base 64 string representing the unique address of a row in its table. This data type is primarily for values returned by the ROWID pseudocolumn.
  208	UROWID [( size is the size of a column of type UROWID . The maximum size and default is 4000 bytes.
  96	CHAR [( size bytes or characters. Maximum size is 1 byte.BYTE and CHAR have the same semantics as for VARCHAR2 .
  96	NCHAR [( size characters. The number of bytes can be up to two times size for UTF8 encoding. Maximum size is 1 character.
  112	CLOB	A character large object containing single-byte or multibyte characters. Both fixed-width and variable-width character sets are supported, both using the database character set. Maximum size is (4 gigabytes - 1) * (database block size).
  112	NCLOB	A character large object containing Unicode characters. Both fixed-width and variable-width character sets are supported, both using the database national character set. Maximum size is (4 gigabytes - 1) * (database block size). Stores national character set data.
  113	BLOB	A binary large object. Maximum size is (4 gigabytes - 1) * (database block size).
  114	BFILE	Contains a locator to a large binary file stored outside the database. Enables byte stream I/O access to external LOBs residing on the database server. Maximum size is 4 gigabytes.

  MAX_STRING_SIZE

  ---

  Property	Description
  Syntax	MAX_STRING_SIZE = { STANDARD | EXTENDED }
  Modifiable	ALTER SYSTEM ... SID='*' Foot 1
  Basic	No
  c apply (for example, 4000 bytes for VARCHAR 2 and NVARCHAR2 , and 2000 bytes for RAW ).EXTENDED means that the 32767 byte limit introduced in Oracle Database 12 pdb-name OPEN UPGRADE;

• Change the setting of MAX_STRING_SIZE in the PDB to EXTENDED .

• Run the rdbms/admin/utl32k.sql script in the PDB. You must be connected AS SYSDBA to run the utl32k.sql script.

• Reopen 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.

• Run the rdbms/admin/utlrp.sql script in the PDB to recompile invalid objects. You must be connected AS SYSDBA to run the script.

• See Also:

• Oracle Database Globalization Support Guide for more information about the MAX_STRING_SIZE parameter

• \1. 关闭数据库

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

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

• \4. 使用SYS用户执行脚本

• \5. 重新启动数据库

  本人提供Oracle(OCP、OCM)、MySQL(OCP)、PostgreSQL(PGCA、PGCE、PGCM)等数据库的培训和考证业务，私聊QQ646634621或微信dbaup66，谢谢！
  后续精彩内容已被站长无情隐藏，请输入验证码解锁本文！

  验证码：

  获取验证码： 请先关注本站微信公众号，然后回复“验证码”，获取验证码。在微信里搜索“AiDBA”或者“dbaup6”或者微信扫描右侧二维码都可以关注本站微信公众号。

  相关文章

  • 在Oracle中， IS NULL如何用到索引？
  • 在Oracle的存储过程、函数或触发器中，如何将相关错误信息写入告警日志中
  • 在物理 standby 数据库上如何解决 MRP 进程卡住的问题？ (Doc ID 2331842.1)(Doc ID 1221163.1)
  • Oracle中的临时表空间简介和维护
  • Oracle中，若临时表空间使用率过高有什么调优思路？
  • Oracle环境中报错TNS-00511、TNS-12541、No listener，其实监听已启动
  • DGMGRL ADD DATABASE Failing with ORA-16191 (Doc ID 3013838.1)
  • ORA-16191 and ORA-1017 reported for Log Transport to Standby when using redo_transport_user (Doc ID 2848435.1)
  • Troubleshooting ORA-16191 and ORA-1017 ORA-1031 in Data Guard Log Transport Services or Data Guard Broker (Doc ID 1368170.1)
  • Oracle 12c的DG自动同步密码文件--ASM 新特性：共享密码文件
  • Oracle DG中日志不同步的ORA-16191错误解决整理
  • Oracle的DG环境中由于配置了登陆触发器导致日志不能同步报错ORA-16191的实验
  • Oracle将秒转换为时分秒的格式
  • 在Oracle的DG环境中，主库新增数据文件的过程
  • Oracle对索引做monitoring和nomonitoring会导致游标失效
  • 客户某套Oracle rac业务库出现严重的library cache等待排查
  • Oracle中的临时表介绍
  • Oracle版本发布历史
  • Oracle 23ai介绍（含docker和单机安装）
  • 在Docker中快速体验Oracle 23ai版
  打赏赞(1)分享