注意：

• 此教程需要访问 Oracle Cloud。要注册免费账户，请参阅开始使用 Oracle Cloud Infrastructure Free Tier 。
• 它使用 Oracle Cloud Infrastructure 身份证明、租户和区间示例值。完成实验室时，请将这些值替换为特定于云环境的那些值。

为 Oracle 数据库启用扩展数据类型功能

简介

Oracle Database 12c 引入了 MAX_STRING_SIZE 参数，用于控制 VARCHAR2、NVARCHAR2 和 RAW 数据类型的最大大小。将 MAX_STRING_SIZE 参数设置为 EXTENDED 可使数据类型大小达到 32767 字节限制。将 MAX_STRING_SIZE 设置为 EXTENDED 值时，数据库 COMPATIBLE 初始化参数应为 12.0.0.0 或更高版本。

本教程详细介绍了使 Oracle Database 能够利用需要特定升级操作的新扩展数据类型的任务。

目标

• 利用支持的 VARCHAR2、NVARCHAR2 和 RAW 数据类型达到 32767 字节限制。

先决条件

• 数据库版本应为 Oracle 12.2 或更高版本。

任务 1：在非容器数据库上设置扩展数据类型

1. 运行以下命令。

   SQL> SHOW PARAMETER MAX_STRING_
   
   | NAME            | TYPE   | VALUE    |
   | --------------- | ------ | -------- |
   | max_string_size | string | STANDARD |
   

2. 以升级模式启动数据库。

   SQL> SHUTDOWN IMMEDIATE;
   
   Database closed.
   Database dismounted.
   ORACLE instance shut down.
   
   SQL>STARTUP UPGRADE
   
   ORACLE instance started.
   
   Total System Global Area   1.4663E+10 bytes
   Fixed Size                 15697000 bytes
   Variable Size              1.1878E+10 bytes
   Database Buffers           2717908992 bytes
   Redo Buffers               51404800 bytes
   
   Database mounted.
   Database opened.
   

3. 将 MAX_STRING_SIZE 值更改为 EXTENDED。

   SQL>  alter system set MAX_STRING_SIZE='EXTENDED' SCOPE=BOTH;
   
   System altered.
   
   SQL> show parameter MAX_STRING_SIZE
   
   | NAME            | TYPE   | VALUE    |
   | --------------- | ------ | -------- |
   | max_string_size | string | EXTENDED |
   

4. 运行 utl32k.sql 脚本：@?/rdbms/admin/utl32k.sql。

   SQL> @?/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.
   
   1524 rows updated.
   
   Commit complete.
   
   System altered.
   
   PL/SQL procedure successfully completed.
   
   Commit complete.
   
   System altered.
   
   Session altered.
   
   Session altered.
   
   Table created.
   
   Table created.
   
   Table created.
   
   Table truncated.
   
   0 rows created.
   
   PL/SQL procedure successfully completed.
   
   no rows selected
   
   DOC>#######################################################################
   DOC>#######################################################################
   DOC> The following statement will cause an "ORA-01722: invalid number"
   DOC> error if we encountered an error while modifying a column to
   DOC> account for data type length change as a result of enabling or
   DOC> disabling 32k types.
   DOC>
   DOC> Contact Oracle support for assistance.
   DOC>#######################################################################
   DOC>#######################################################################
   DOC>#
   
   PL/SQL procedure successfully completed.
   
   PL/SQL procedure successfully completed.
   
   Commit complete.
   

5. 重新启动数据库。

   shutdown immediate;
   startup
   
   SQL> show parameter max_string
   
   | NAME            | TYPE   | VALUE    |
   | --------------- | ------ | -------- |
   | max_string_size | string | EXTENDED |
   

任务 2：在容器数据库上设置扩展数据类型 (CDB$ROOT)

1. 运行以下命令。

   [oracle@vm4 ~]$ sqlplus "/ as sysdba"
   
   SQL*Plus: Release 12.2.0.1.0 Production on Thu Sep 7 13:26:41 2023
   
   Copyright (c) 1982, 2016, Oracle.  All rights reserved.
   
   Connected to:
   Oracle Database 12c EE Extreme Perf Release 12.2.0.1.0 - 64bit Production
   
   SQL> SHOW PARAMETER MAX_STRING_
   
   | NAME            | TYPE   | VALUE    |
   | --------------- | ------ | -------- |
   | max_string_size | string | STANDARD |
   
   SQL> show pdbs;
   
   | CON_ID | CON_NAME    | OPEN MODE  | RESTRICTED |
   | ------ | ----------- | ---------- | ---------- |
   | 2      | PDB$SEED    | READ ONLY  | NO         |
   | 3      | RATPRD_PDB1 | READ WRITE | NO         |
   
   [oracle@vm4 ~]$ cd $ORACLE_HOME/rdbms/admin
   [oracle@vm4 admin]$ pwd
   /u01/app/oracle/product/12.2.0.1/dbhome_1/rdbms/admin
   [oracle@vm4 ~]$  mkdir -p /home/oracle/mydir/utl32k_cdb_pdbs_output
   [oracle@vm4 ~]$ sqlplus "/ as sysdba"
   
   SQL*Plus: Release 12.2.0.1.0 Production on Thu Sep 7 13:30:18 2023
   
   Copyright (c) 1982, 2016, Oracle.  All rights reserved.
   
   Connected to:
   Oracle Database 12c EE Extreme Perf Release 12.2.0.1.0 - 64bit Production
   
   SQL> ALTER SESSION SET CONTAINER=CDB$ROOT;
   
   Session altered.
   

2. 将 MAX_STRING_SIZE 值更改为 EXTENDED。

   SQL> ALTER SYSTEM SET MAX_STRING_SIZE=extended SCOPE=SPFILE;
   
   System altered.
   

3. 以升级模式启动数据库。

   SQL> shutdown;
   
   Database closed.
   Database dismounted.
   ORACLE instance shut down.
   
   SQL> startup upgrade;
   
   ORACLE instance started.
   
   Total System Global Area 3.2749E+10 bytes
   Fixed Size                 12351112 bytes
   Variable Size            4160751992 bytes
   Database Buffers         2.8387E+10 bytes
   Redo Buffers              188973056 bytes
   
   Database mounted.
   Database opened.
   
   SQL> exit
   

4. 运行 catcon.pl 脚本。

   注：使用 catcon.pl 脚本在根和 CDB 的所有 PDB 中运行 rdbms/admin/utl32k.sql 脚本，以增加 VARCHAR2、NVARCHAR2 和 RAW 列的最大大小。-force_pdb_mode“UPGRADE”选项用于确保所有 PDB（包括应用程序根克隆）在迁移模式下打开。在提示时输入 SYS 密码。

   [oracle@vm4 ~]$ $ORACLE_HOME/perl/bin/perl $ORACLE_HOME/rdbms/admin/catcon.pl -u SYS --force_pdb_mode 'UPGRADE' -d $ORACLE_HOME/rdbms/admin -l '/home/oracle/mydir/utl32k_cdb_pdbs_output' -b utl32k_cdb_pdbs_output utl32k.sql
   catcon: ALL catcon-related output will be written to [/home/oracle/mydir/utl32k_cdb_pdbs_output/utl32k_cdb_pdbs_output_catcon_88242.lst]
   catcon: See [/home/oracle/mydir/utl32k_cdb_pdbs_output/utl32k_cdb_pdbs_output*.log] files for output generated by scripts
   catcon: See [/home/oracle/mydir/utl32k_cdb_pdbs_output/utl32k_cdb_pdbs_output_*.lst] files for spool files, if any
   Enter Password:    -> enter sys password
   catcon.pl: completed successfully
   [oracle@vm4 ~]$ sqlplus "/ as sysdba"
   
   SQL*Plus: Release 12.2.0.1.0 Production on Thu Sep 7 13:48:56 2023
   
   Copyright (c) 1982, 2016, Oracle.  All rights reserved.
   
   Connected to:
   Oracle Database 12c EE Extreme Perf Release 12.2.0.1.0 - 64bit Production
   

   SQL> startup;
   
   ORA-01081: cannot start already-running ORACLE - shut it down first
   
   SQL> shut immediate;
   
   Database closed.
   Database dismounted.
   ORACLE instance shut down.
   
   SQL> startup;
   
   ORACLE instance started.
   
   Total System Global Area 3.2749E+10 bytes
   Fixed Size                 12351112 bytes
   Variable Size            4160751992 bytes
   Database Buffers         2.8387E+10 bytes
   Redo Buffers              188973056 bytes
   
   Database mounted.
   Database opened.
   
   SQL> show parameter max_string_size;
   
   | NAME            | TYPE   | VALUE    |
   | --------------- | ------ | -------- |
   | max_string_size | string | EXTENDED |
   

任务 3：在 PDB 上设置扩展数据类型

1. 运行以下命令可更改数据库。

   SQL> alter session set container = RATPRD_PDB1;
   
   Session altered.
   
   SQL> show pdbs
   
   | CON_ID | CON_NAME    | OPEN MODE  | RESTRICTED |
   | ------ | ----------- | ---------- | ---------- |
   | 3      | RATPRD_PDB1 | READ WRITE | NO         |
   
   SQL> ALTER PLUGGABLE DATABASE RATPRD_PDB1 CLOSE IMMEDIATE;
   
   Pluggable database altered.
   
   SQL>  show pdbs
   
   | CON_ID | CON_NAME    | OPEN MODE | RESTRICTED |
   | ------ | ----------- | --------- | ---------- |
   | 3      | RATPRD_PDB1 | MOUNTED   |
   

2. 在升级模式下打开 PDB RATPRD_PDB1，这是修改 max_string_size 参数所必需的。

   SQL> ALTER PLUGGABLE DATABASE RATPRD_PDB1 OPEN UPGRADE;
   
   $
   Pluggable database altered.
   
   SQL>  show pdbs;
   
   | CON_ID | CON_NAME    | OPEN MODE | RESTRICTED |
   | ------ | ----------- | --------- | ---------- |
   | 3      | RATPRD_PDB1 | MIGRATE   | YES        |
   

3. 运行 utl32k.sql 以修改 PDB RATPRD_PDB1 上 VARCHAR2、NVARCHAR2 和 RAW 列的最大大小。

   SQL> @$ORACLE_HOME/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.
   
   0 rows updated.
   
   Commit complete.
   
   System altered.
   
   PL/SQL procedure successfully completed.
   
   Commit complete.
   
   System altered.
   
   Session altered.
   
   Session altered.
   
   Table created.
   
   Table created.
   
   Table created.
   
   Table truncated.
   
   0 rows created.
   
   PL/SQL procedure successfully completed.
   
   STARTTIME :
   
   09/07/2023 14:32:06.790646000
   
   PL/SQL procedure successfully completed.
   
   No errors.
   
   PL/SQL procedure successfully completed.
   
   Session altered.
   
   Session altered.
   
   0 rows created.
   
   no rows selected
   
   no rows selected
   
   DOC>#######################################################################
   DOC>#######################################################################
   DOC> The following statement will cause an "ORA-01722: invalid number"
   DOC> error if we encountered an error while modifying a column to
   DOC> account for data type length change as a result of enabling or
   DOC> disabling 32k types.
   DOC>
   DOC> Contact Oracle support for assistance.
   DOC>#######################################################################
   DOC>#######################################################################
   DOC>#
   
   PL/SQL procedure successfully completed.
   
   PL/SQL procedure successfully completed.
   
   Commit complete.
   
   Package altered.
   
   Package altered.
   
   SQL> show pdbs;
   
   | CON_ID | CON_NAME    | OPEN MODE | RESTRICTED |
   | ------ | ----------- | --------- | ---------- |
   | 3      | RATPRD_PDB1 | MIGRATE   | YES        |
   
   SQL> ALTER PLUGGABLE DATABASE RATPRD_PDB1 CLOSE IMMEDIATE;
   
   Pluggable database altered.
   

4. 验证 max_string_size 参数值是否已更改为 EXTENDED。

   SQL> ALTER PLUGGABLE DATABASE RATPRD_PDB1 open ;
   
   Pluggable database altered.
   
   SQL>  show pdbs;
   
   | CON_ID | CON_NAME    | OPEN MODE  | RESTRICTED |
   | ------ | ----------- | ---------- | ---------- |
   | 3      | RATPRD_PDB1 | READ WRITE | NO         |
   
   SQL> show parameter max_string_size;
   
   | NAME            | TYPE   | VALUE    |
   | --------------- | ------ | -------- |
   | max_string_size | string | EXTENDED |
   

相关链接

• Oracle Database 参考：MAX_STRING_SIZE

确认

• 作者 - Hakim Ahamad（主要云架构师）

更多学习资源

探索 docs.oracle.com/learn 上的其他实验室，或者访问 Oracle Learning YouTube 频道上的更多免费学习内容。此外，请访问 education.oracle.com/learning-explorer 成为 Oracle Learning Explorer。

有关产品文档，请访问 Oracle 帮助中心。

---

标题和版权信息

Enable the Extended Data Type Capability for Oracle databases

F87592-01

October 2023

Copyright © 2023, Oracle and/or its affiliates.