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

最新推荐文章于 2024-03-09 15:56:09 发布

weixin_34161029

最新推荐文章于 2024-03-09 15:56:09 发布

阅读量383

点赞数 1

文章标签：数据库

【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

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

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

 @?/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:
- The maximum length of a text literal. See "Text Literals" for more information.
- The size limit for concatenating two character strings. See "Concatenation Operator" for more information.
- The length of the collation key returned by the NLSSORT function. See NLSSORT.
- The size of some of the attributes of the XMLFormat object. See "XML Format Model" for more information.
- The size of some expressions in the following XML functions: XMLCOLATTVAL, XMLELEMENT, XMLFOREST, XMLPI, and XMLTABLE.

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 2³¹ -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:

Shut down the database.
Restart the database in UPGRADE mode.
Change the setting of MAX_STRING_SIZE to EXTENDED.
Run the rdbms/admin/utl32k.sql script. You must be connected AS SYSDBA to run the script.
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.
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:

Connect to the CDB AS SYSDBA.
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.
Shut down the CDB.
Restart the CDB in UPGRADE mode.
```
startup upgrade;
```
Open all the PDBs in migrate mode and then exit the database:
```
ALTER PLUGGABLE DATABASE ALL OPEN UPGRADE;
EXIT;
```

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.

Connect to the CDB AS SYSDBA and shut down the database.
Restart the CDB in NORMAL mode.
```
startup;
```

Open all the PDBs in the CDB:

ALTER PLUGGABLE DATABASE ALL OPEN READ WRITE;

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
$

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

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

no rows selected

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

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.

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群，学习最实用的数据库技术。

weixin_34161029

关注

1
点赞
踩
1

收藏

觉得还不错? 一键收藏
0
评论
【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的初始数据库参数设置为EXTEN...
复制链接

扫一扫

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

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

Extended Data Types

Oracle Built-in Data Types

MAX_STRING_SIZE

一.1.1.1 自己的实验

“相关推荐”对你有帮助么？