【12c】扩展数据类型（Extended Data Types）-- MAX_STRING_SIZE

时间：2023-03-09 15:45:09
【12c】扩展数据类型（Extended Data Types）-- MAX_STRING_SIZE

【12c】扩展数据类型（Extended Data Types）-- MAX_STRING_SIZE

在12c中，与早期版本相比，诸如VARCHAR2, NAVARCHAR2以及 RAW这些数据类型的大小会从4K以及2K字节扩展至32K字节。只要可能，扩展字符的大小会降低对LOB 数据类型的使用。为了启用扩展字符大小，你必须将MAX_STRING_SIZE的初始数据库参数设置为EXTENDED。

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

在Oracle Database 12c中，我们可以为varchar2、nvarchar2和RAW数据类型指定32767 bytes 的最大长度了， 以便用户将更长的字符串存储在数据库中。

在12c之前的版本中，varchar2和nvarchar2数据类型的最大长度是4000 bytes，而raw是2000 bytes。

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

  • 定义为4000字节或更小的varchar2、nvarchar2以及2000字节或更小的raw字段，将被inline存放
  • 定义为4000字节以上的varchar2、nvarchar2以及2000字节以上的raw字段的话，被称作extended character data type columns，以out of line方式存储。

参数MAX_STRING_SIZE控制扩展数据类型extended data type的最大长度：

  • STANDARD 代表12c之前的长度限制，即varchar2、nvarchar2 4000 bytes， raw 是2000  bytes
  • EXTENDED 代表12c 32k strings新特性，varchar2、nvarchar2、raw最大长度32k  bytes

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

  • 不支持cluster table 簇表和index-organized tables索引组织表
  • 不支持intrapartition的并行DDL、UPDATE和DELETE DML
  • 不支持在Automatic Segment Space Management (ASSM)表空间上的intrapartition parallel direct-path inserts

为数据库配置扩展数据类型 的步骤如下：

  1. 关闭数据库实例  shutodnw immediate，如果是RAC则需要关闭所有实例
  2. 以upgrade模式启动数据库 实例 startup upgrade;
  3. 修改参数MAX_STRING_SIZE 为EXTENDED   ALTER SYSTEM SET MAX_STRING_SIZE = EXTENDED;
  4. 运行 @?/rdbms/admin/utl32k
  5. 重启数据库实例
 @?/rdbms/admin/utl32k

...Database user "SYS", database schema "APEX_040200", user# "98" 13:29:59

...Compiled 0 out of 2998 objects considered, 0 failed compilation 13:30:00

...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 13:30:00

...Completed key object existence check 13:30:00

...Setting DBMS Registry 13:30:00

...Setting DBMS Registry Complete 13:30:00

...Exiting validate 13:30:00

必须要将MAX_STRING_SIZE 设置为EXTENDED 否则无法使用extended character data type columns 。

之后我们可以创建具体有extended character data type columns 的表了。

当然我们也可以将已有的VARCHAR2, NVARCHAR2, 和RAW字段修改其长度， 具体使用ALTER TABLE MODIFY (COLUMN 命令。在此场景中Oracle将实施块中的长度扩展，而不将inline的存储迁移为以外部LOB存储。

实际上Oracle并不建议你广泛积极地将现有的varchar2的长度增加为4000 bytes以上，基于以下的原因：

  • 很容易造成链式行row chaining
  • inline存储的数据行将被读取，不管该字段是否被select 。 实际inline的扩展字符类型显然会一定程度上影响性能。
  • 为了迁移到新的out-of-line的存储扩展字符类型方式，用户需要重建表。否则任何类型的表重新组织方式例如alter table move都将无法打破inline存储

用户可以将32k的字段加入到现有的堆表，具体使用ALTER TABLE ADD 的DDL语句。

Data Pump导入导出以及SQL*Loader均支持extended character data type columns。

现有字段上的索引无法实现数据类型扩展，所以必须先将字段上的索引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

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.

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 extended data type. Extended data type columns are stored out-of-line, leveraging Oracle's LOB technology. The LOB storage is always aligned with the table. In tablespaces managed with Automatic Segment Space Management (ASSM), extended data type columns are stored as SecureFiles LOBs. Otherwise, they are stored as BasicFiles LOBs. The use of LOBs as a storage mechanism is internal only. Therefore, you cannot manipulate these LOBs using the DBMS_LOB package.

Notes:

  • Oracle strongly discourages the use of BasicFiles LOBs as a storage mechanism. BasicFiles LOBs not only impose restrictions on the capabilities of extended data type columns, but the BasicFiles data type is planned to be deprecated in a future release.

  • Extended data types are subject to the same rules and restrictions as LOBs. Refer to 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 list_partitionsclause of CREATE TABLE for information on how to work around this issue.

  • The value of the initialization parameter MAX_STRING_SIZE affects the following:

Oracle Built-in Data Types

The table that follows summarizes Oracle built-in data types. Refer to the syntax in the preceding sections for the syntactic elements. The codes listed for the data types are used internally by Oracle Database. The data type code of a column or object attribute is returned by the DUMP function.

Table 2-1 Built-in Data Type Summary

Code Data Type Description

1

VARCHAR2(size [BYTE | CHAR])

Variable-length character string having maximum length size bytes or characters. You must specify size for VARCHAR2. Minimum size is 1 byte or 1 character. Maximum size is:

  • 32767 bytes or characters if MAX_STRING_SIZE = EXTENDED

  • 4000 bytes or characters if MAX_STRING_SIZE = STANDARD

Refer to "Extended Data Types" for more information on the MAX_STRING_SIZE initialization parameter.

BYTE indicates that the column will have byte length semantics. CHAR indicates that the column will have character semantics.

1

NVARCHAR2(size)

Variable-length Unicode character string having maximum length size characters. You must specify size for NVARCHAR2. The number of bytes can be up to two times size for AL16UTF16 encoding and three times size for UTF8 encoding. Maximum size is determined by the national character set definition, with an upper limit of:

  • 32767 bytes if MAX_STRING_SIZE = EXTENDED

  • 4000 bytes if MAX_STRING_SIZE = STANDARD

Refer to "Extended Data Types" for more information on the MAX_STRING_SIZE initialization parameter.

2

NUMBER [ (p [, s]) ]

Number having precision p and scale s. The precision p can range from 1 to 38. The scale s can range from -84 to 127. Both precision and scale are in decimal digits. A NUMBER value requires from 1 to 22 bytes.

2

FLOAT [(p)]

A subtype of the NUMBER data type having precision p. A FLOAT value is represented internally as NUMBER. The 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 231 -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)]

Year, month, and day values of date, as well as hour, minute, and second values of time, where fractional_seconds_precision is the number of digits in the fractional part of the SECOND datetime field. Accepted values of fractional_seconds_precision are 0 to 9. The default is 6. The default format is determined explicitly by the NLS_TIMESTAMP_FORMAT parameter or implicitly by theNLS_TERRITORY parameter. The size is 7 or 11 bytes, depending on the precision. This data type contains the datetime fields YEAR, MONTH, DAY, HOUR, MINUTE, and SECOND. It contains fractional seconds but does not have a time zone.

181

TIMESTAMP[(fractional_seconds_precision)]WITH TIME ZONE

All values of TIMESTAMP as well as time zone displacement value, where fractional_seconds_precision is the number of digits in the fractional part of the SECOND datetime field. Accepted values are 0 to 9. The default is 6. The default format is determined explicitly by the NLS_TIMESTAMP_FORMAT parameter or implicitly by the NLS_TERRITORY parameter. The size is fixed at 13 bytes. This data type contains the datetime fields YEAR, MONTH, DAY, HOUR, MINUTE, SECOND, TIMEZONE_HOUR, and TIMEZONE_MINUTE. It has fractional seconds and an explicit time zone.

231

TIMESTAMP[(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)] TO MONTH

Stores a period of time in years and months, where 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 [(day_precision)]TO SECOND[(fractional_seconds_precision)]

Stores a period of time in days, hours, minutes, and seconds, where

  • day_precision is the maximum number of digits in the DAY datetime field. Accepted values are 0 to 9. The default is 2.

  • fractional_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)

Raw binary data of length size bytes. You must specify size for a RAW value. Maximum size is:

  • 32767 bytes if MAX_STRING_SIZE = EXTENDED

  • 2000 bytes if MAX_STRING_SIZE = STANDARD

Refer 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)]

Base 64 string representing the logical address of a row of an index-organized table. The optional size is the size of a column of type UROWID. The maximum size and default is 4000 bytes.

96

CHAR [(size [BYTE | CHAR])]

Fixed-length character data of length size bytes or characters. Maximum size is 2000 bytes or characters. Default and minimum size is 1 byte.

BYTE and CHAR have the same semantics as for VARCHAR2.

96

NCHAR[(size)]

Fixed-length character data of length size characters. The number of bytes can be up to two times size for AL16UTF16 encoding and three times size for UTF8 encoding. Maximum size is determined by the national character set definition, with an upper limit of 2000 bytes. Default and minimum 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

Parameter type

String

Syntax

MAX_STRING_SIZE = { STANDARD | EXTENDED }

Default value

STANDARD

Modifiable

ALTER SYSTEM ... SID='*'Foot 1

Modifiable in a PDB

Yes

Basic

No

Oracle RAC

Multiple instances must use the same value.

Footnote 1

Use ALTER SYSTEM only when the database is in UPGRADE mode, and run the utl32k.sql script afterward, as explained in this section.

MAX_STRING_SIZE controls the maximum size of VARCHAR2, NVARCHAR2, and RAW data types in SQL.

STANDARD means that the length limits for Oracle Database releases prior to Oracle Database 12c apply (for example, 4000 bytes for VARCHAR2 and NVARCHAR2, and 2000 bytes for RAW).

EXTENDED means that the 32767 byte limit introduced in Oracle Database 12c applies.

The COMPATIBLE initialization parameter must be set to 12.0.0.0 or higher to set MAX_STRING_SIZE = EXTENDED.

You can change the value of MAX_STRING_SIZE from STANDARD to EXTENDED. However, you cannot change the value of MAX_STRING_SIZE from EXTENDED to STANDARD.

By setting MAX_STRING_SIZE = EXTENDED, users are taking an explicit action that could introduce application incompatibility in their database. Applications that do not want to use the expanded data types can be rewritten for compatibility with either setting; for example, these applications could use explicit CASTs to fix the length of VARCHAR2 expressions during CREATE TABLE AS SELECT.

Altering MAX_STRING_SIZE will update database objects and possibly invalidate them, as follows:

  • Tables with virtual columns will be updated with new data type metadata for virtual columns of VARCHAR2(4000), 4000-byte NVARCHAR2, or RAW(2000) type.

    • Functional indexes will become unusable if a change to their associated virtual columns causes the index key to exceed index key length limits. Attempts to rebuild such indexes will fail with ORA-01450: maximum key length exceeded.

  • Views will be invalidated if they contain VARCHAR2(4000), 4000-byte NVARCHAR2, or RAW(2000) typed expression columns.

  • Materialized views will be updated with new metadata VARCHAR2(4000), 4000-byte NVARCHAR2, and RAW(2000) typed expression columns

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.

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

Increasing the Maximum Size of VARCHAR2, NVARCHAR2, and RAW Columns in a CDB

To increase the maximum size of VARCHAR2, NVARCHAR2, and RAW columns in a CDB and in all the PDBs in the CDB:

  1. Connect to the CDB AS SYSDBA.

  2. In the root, change the setting of MAX_STRING_SIZE to EXTENDED:

    ALTER SESSION SET CONTAINER=CDB$ROOT;
    
ALTER SYSTEM SET max_string_size=extended SCOPE=SPFILE;

    Note:

    The root continues to use STANDARD semantics even after MAX_STRING_SIZE is set to EXTENDED. The reason for setting MAX_STRING_SIZE to EXTENDED in the root is so all the PDBs in the CDB can inherit the EXTENDED setting from the root.

  3. Shut down the CDB.

  4. Restart the CDB in UPGRADE mode.

    startup upgrade;

  5. Open all the PDBs in migrate mode and then exit the database:

    ALTER PLUGGABLE DATABASE ALL OPEN UPGRADE;
    
EXIT;

  6. Use the catcon.pl script to run the rdbms/admin/utl32k.sql script in the root and in all the PDBs in the CDB to increase the maximum size of the VARCHAR2, NVARCHAR2, and RAW columns. Enter the SYS password when prompted:

    $ cd $ORACLE_HOME/rdbms/admin
    
$ mkdir /scratch/mydir/utl32k_cdb_pdbs_output
    
$ $ORACLE_HOME/perl/bin/perl $ORACLE_HOME/rdbms/admin/catcon.pl -u SYS
    
-d $ORACLE_HOME/rdbms/admin -l '/scratch/mydir/utl32k_cdb_pdbs_output' -b
    
utl32k_cdb_pdbs_output utl32k.sql
    
catcon: ALL catcon-related output will be written to [/scratch/mydir/utl32k_cdb_pdbs_output/utl32k_cdb_pdbs_output_catcon_23172.lst]
    
catcon: See [/scratch/mydir/utl32k_cdb_pdbs_output/utl32k_cdb_pdbs_output*.log] files for output generated by scripts
    
catcon: See [/scratch/mydir/utl32k_cdb_pdbs_output/utl32k_cdb_pdbs_output_*.lst] files for spool files, if any
    
Enter Password:
    
catcon.pl: completed successfully
    
$

    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.

  7. Connect to the CDB AS SYSDBA and shut down the database.

  8. Restart the CDB in NORMAL mode.

    startup;

  9. Open all the PDBs in the CDB:

    ALTER PLUGGABLE DATABASE ALL OPEN READ WRITE;

  10. Use the catcon.pl script to run the rdbms/admin/utlrp.sql script to recompile invalid objects in the root and in all the PDBs in the CDB. Enter the SYS password when prompted:

    $ cd $ORACLE_HOME/rdbms/admin
    
$ mkdir /scratch/mydir/utlrp_cdb_pdbs_output
    
$ $ORACLE_HOME/perl/bin/perl $ORACLE_HOME/rdbms/admin/catcon.pl -u SYS -d $ORACLE_HOME/rdbms/admin -l '/scratch/mydir/utlrp_cdb_pdbs_output' -b utlrp_cdb_pdbs_output utlrp.sql
    
catcon: ALL catcon-related output will be written to [/scratch/mydir/utlrp_cdb_pdbs_output/utlrp_cdb_pdbs_output_catcon_24271.lst]
    
catcon: See [/scratch/mydir/utlrp_cdb_pdbs_output/utlrp_cdb_pdbs_output*.log] files for output generated by scripts
    
catcon: See [/scratch/mydir/utlrp_cdb_pdbs_output/utlrp_cdb_pdbs_output_*.lst] files for spool files, if any
    
Enter Password:
    
catcon.pl: completed successfully
    
$

See Also:

Oracle Database Administrator’s Guide for information about using the catcon.pl script to run Oracle-supplied scripts in a CDB and PDBs.

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. Reopen the PDB in migrate mode.

    Note:

    The following SQL statement can be used to reopen a PDB in migrate mode when the current container is the PDB:

    ALTER PLUGGABLE DATABASE pdb-name OPEN UPGRADE;

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

  6. 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 Administrator's Guide for more information about modifying the open mode of PDBs.

Increasing the Maximum Size of VARCHAR2, NVARCHAR2, and RAW Columns in an Oracle RAC Database

To increase the maximum size of VARCHAR2, NVARCHAR2, and RAW columns in an Oracle RAC database:

  1. Shut down all of the Oracle RAC database instances, except one.

  2. Restart the Oracle RAC database instance in UPGRADE mode.

  3. Change the setting of MAX_STRING_SIZE to EXTENDED.

  4. Run the rdbms/admin/utl32k.sql script in the Oracle RAC database instance. You must be connected AS SYSDBA to run the script.

  5. Restart all Oracle RAC database instances 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.

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

Increasing the Maximum Size of VARCHAR2, NVARCHAR2, and RAW Columns in an Oracle Data Guard Logical Standby Database

To increase the maximum size of VARCHAR2, NVARCHAR2, and RAW columns in an Oracle Data Guard logical standby database:

  1. Shut down the Oracle Data Guard primary database and logical standby database.

  2. Restart the primary database and logical standby database in UPGRADE mode.

  3. Change the setting of MAX_STRING_SIZE to EXTENDED on the primary database and logical standby database.

  4. Run the rdbms/admin/utl32k.sql script on both the primary database and the logical standby database. You must be connected AS SYSDBA to run the script.

  5. Restart the primary database and logical standby 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.

  6. Run the rdbms/admin/utlrp.sql script on the primary database and logical standby database to recompile invalid objects. You must be connected AS SYSDBA to run the script.

  7. Restart SQL Apply.

See Also:

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

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;

一.1.1.1  自己的实验

[oracle@lhrdb ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Wed Feb 15 10:25:17 2017

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

Connected to:

Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SYS@lhrdb> show parameter MAX_STRING_SIZE

NAME                                 TYPE        VALUE

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

max_string_size                      string      STANDARD

SYS@lhrdb> show parameter COMPATIBLE

NAME                                 TYPE        VALUE

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

compatible                           string      12.1.0.2.0

noncdb_compatible                    boolean     FALSE

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

create table t3(blog varchar2(4001)) tablespace users

*

ERROR at line 1:

ORA-00910: specified length too long for its datatype

SYS@lhrdb> 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-14694: database must in UPGRADE mode to begin MAX_STRING_SIZE migration

SYS@lhrdb> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SYS@lhrdb> startup upgrade;

ORACLE instance started.

Total System Global Area  591396864 bytes

Fixed Size                  2927096 bytes

Variable Size             469763592 bytes

Database Buffers          113246208 bytes

Redo Buffers                5459968 bytes

Database mounted.

Database opened.

SYS@lhrdb> select open_mode from v$database;

OPEN_MODE

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

READ WRITE

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

System altered.

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

SP2-0734: unknown command beginning ">@?/rdbms/..." - rest of line ignored.

SYS@lhrdb> >@?/rdbms/admin/utl32k.sql^[[D^C^C

SYS@lhrdb>

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

Session altered.

DOC>#######################################################################

DOC>#######################################################################

DOC>   The following statement will cause an "ORA-01722: invalid number"

DOC>   error if the database has not been opened for UPGRADE.

DOC>

DOC>   Perform a "SHUTDOWN ABORT"  and

DOC>   restart using UPGRADE.

DOC>#######################################################################

DOC>#######################################################################

DOC>#

no rows selected

DOC>#######################################################################

DOC>#######################################################################

DOC>   The following statement will cause an "ORA-01722: invalid number"

DOC>   error if the database does not have compatible >= 12.0.0

DOC>

DOC>   Set compatible >= 12.0.0 and retry.

DOC>#######################################################################

DOC>#######################################################################

DOC>#

PL/SQL procedure successfully completed.

Session altered.

2 rows updated.

Commit complete.

System altered.

PL/SQL procedure successfully completed.

Commit complete.

System altered.

Session altered.

PL/SQL procedure successfully completed.

No errors.

Session altered.

PL/SQL procedure successfully completed.

Commit complete.

Package altered.

Package altered.

SYS@lhrdb> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SYS@lhrdb> startup

ORACLE instance started.

Total System Global Area  591396864 bytes

Fixed Size                  2927096 bytes

Variable Size             469763592 bytes

Database Buffers          113246208 bytes

Redo Buffers                5459968 bytes

Database mounted.

Database opened.

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

Table created.

SYS@lhrdb> desc t3;

Name              Null?    Type

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

BLOG      VARCHAR2(4001)

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

Table created.

SYS@lhrdb> create table t1(blog varchar2(32728)) tablespace users;

create table t1(blog varchar2(32728)) tablespace users

*

ERROR at line 1:

ORA-00955: name is already used by an existing object

SYS@lhrdb> create table t4(blog varchar2(32728)) tablespace users;

Table created.

SYS@lhrdb> create table t4(blog varchar2(32767)) tablespace users;

create table t4(blog varchar2(32767)) tablespace users

*

ERROR at line 1:

ORA-00955: name is already used by an existing object

SYS@lhrdb> create table t5(blog varchar2(32767)) tablespace users;

Table created.

SYS@lhrdb> create table t5(blog varchar2(32768)) tablespace users;

create table t5(blog varchar2(32768)) tablespace users

*

ERROR at line 1:

ORA-00910: specified length too long for its datatype

SYS@lhrdb> show parameter MAX_STRING_SIZE

NAME                                 TYPE        VALUE

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

max_string_size                      string      EXTENDED

About Me

...............................................................................................................................

● 本文作者：小麦苗，只专注于数据库的技术，更注重技术的运用

● 本文在itpub（http://blog.itpub.net/26736162）、博客园（http://www.cnblogs.com/lhrbest）和个人微信公众号（xiaomaimiaolhr）上有同步更新

● 本文itpub地址：http://blog.itpub.net/26736162/viewspace-2133623/

● 本文博客园地址：http://www.cnblogs.com/lhrbest/p/6404355.html

● 本文pdf版及小麦苗云盘地址：http://blog.itpub.net/26736162/viewspace-1624453/

● QQ群：230161599     微信群：私聊

● 联系我请加QQ好友(642808185)，注明添加缘由

● 于 2017-02-16 08:00 ~ 2017-02-16 24:00 在上行完成

● 文章内容来源于小麦苗的学习笔记，部分整理自网络，若有侵权或不当之处还请谅解

● 版权所有，欢迎分享本文，转载请保留出处

...............................................................................................................................

拿起手机使用微信客户端扫描下边的左边图片来关注小麦苗的微信公众号：xiaomaimiaolhr，扫描右边的二维码加入小麦苗的QQ群，学习最实用的数据库技术。