【12c】擴充套件資料型別（Extended Data Types）-- MAX_STRING_SIZE

 
1.1  啟用擴充套件資料型別
1.1.1  pdb或非cdb（Non-CDB）
pdb或非cdb的資料庫要使用擴充套件字元型別需要執行以下過程：
 
1. 關閉pdb資料庫或非cdb資料庫例項。如果是RAC則需要關閉所有例項，若有必要可以把cluster_database設定為false：
alter system set cluster_database = false scope = spfile;
shutdown immediate
2.在UPGRADE模式下重新啟動資料庫；如果是rac，則只啟動一個例項。
startup upgrade
3.將MAX_STRING_SIZE的設定更改為EXTENDED。
ALTER SYSTEM SET MAX_STRING_SIZE=EXTENDED SID='*';
4.以SYSDBA使用者身份執行以下指令碼。
$ORACLE_HOME/rdbms/admin/utl32k.sql
5.正常關閉資料庫，然後以讀寫模式重啟資料庫。
shutdown immediate
startup
6.執行指令碼rdbms/admin/utlrp.sql編譯失效物件：
$ORACLE_HOME/rdbms/admin/utlrp.sql 
7.測試：
show parameter max_string
create table testa(name varchar2(32767));
create table testb(name varchar2(32768));
set line 80
desc testa
insert into testa values(lpad('a',32766,'x'));
commit;
SELECT length(name) FROM testa;
 
 
升級PDB$SEED資料庫：
SQL> startup mount
SQL> alter database open migrate;
Database altered.
SQL> select con_id, name, open_mode from v$pdbs;
  CON_ID NAME OPEN_MODE
---------- ------------------------------ ----------
  2 PDB$SEED MIGRATE
SQL> alter session set container=PDB$SEED;
Session altered.
SQL> alter system set max_string_size = EXTENDED;
System altered.
SQL> @?/rdbms/admin/utl32k.sql
PL/SQL procedure successfully completed.
 
 1.1.2  CDB庫
對於CDB的庫啟用擴充套件資料型別後，所有的PDB都可以使用擴充套件資料型別，而沒有必要對每個PDB都進行升級操作。但是，需要注意的是，根容器是不支援擴充套件的資料型別的。CDB庫啟用擴充套件資料型別的步驟：
1、SYS使用者執行：
ALTER SESSION SET CONTAINER=CDB$ROOT;
ALTER SYSTEM SET max_string_size=extended SCOPE=SPFILE;
2、關閉資料庫並以升級模式重啟資料庫
shutdown immediate
startup upgrade;
3、保證所有的pdb處於UPGRADE模式（包括pdb$seed）
ALTER PLUGGABLE DATABASE ALL OPEN UPGRADE;
show pdbs
4、執行指令碼
$ cd $ORACLE_HOME/rdbms/admin
$ mkdir -p /home/oracle/utl32k_output
$ $ORACLE_HOME/perl/bin/perl $ORACLE_HOME/rdbms/admin/catcon.pl -u SYS -d $ORACLE_HOME/rdbms/admin -l '/home/oracle/utl32k_output' -b utl32k_cdb_pdbs_output utl32k.sql
5、以OS認證方式登入CDB資料庫後，再關閉CDB，再正常啟動CDB，確保所有的pdb都處於READ WRITE狀態
sqlplus / as sysdba
shutdown immediate
startup 
ALTER PLUGGABLE DATABASE ALL OPEN READ WRITE;
show pdbs
6、執行指令碼utlrp.sql來編譯失效物件
$ cd $ORACLE_HOME/rdbms/admin
$ mkdir -p /home/oracle/utlrp_output
$ $ORACLE_HOME/perl/bin/perl $ORACLE_HOME/rdbms/admin/catcon.pl -u SYS -d $ORACLE_HOME/rdbms/admin -l '/home/oracle/utlrp_output' -b utl32k_cdb_pdbs_output utlrp.sql
7、校驗是否可以建立大於4000的字串型別的列
show parameter max_string
create table testa(name varchar2(32767));
create table testb(name varchar2(32768));
set line 80
desc testa
insert into testa values(lpad('a',32766,'x'));
commit;
SELECT length(name) FROM testa;
 
 
1.1.3  DG環境
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.

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 .

• 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

  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, where 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 bytes. You must specify size is: 32767 bytes if MAX_STRING_SIZE = EXTENDED 2000 bytes if MAX_STRING_SIZE = STANDARD Refer to 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='*'
  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:

for more information about the MAX_STRING_SIZE parameter





1. 關閉資料庫
2. 啟動資料庫到升級模式
3. 修改max_string_size為EXTENDED，預設是standard
4. 使用SYS使用者執行指令碼
5. 重新啟動資料庫
SYS@OCM12C >startup


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


SYS@OCM12C >create table t4(blog varchar2(32727)) tablespace users;


1. 關閉PDB資料庫
SYS@cdb > show pdbs;


---------- ------------------------------ ---------- ----------
3 PDB1 READ WRITE NO


Pluggable database altered.


SYS@cdb > alter pluggable database pdb1 open upgrade;


3. 修改max_string_size引數為extended
System altered.


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


...Database user "SYS", database schema "APEX_040200", user# "98" 10:49:54
...263 packages
...453 tables
...16 procedures
...458 triggers
...207 views
...6 types
...0 operators
...Begin key object existence check 10:49:54
...Setting DBMS Registry 10:49:54
...Exiting validate 10:49:54


5. 重新啟動PDB資料庫
Pluggable database altered.


Pluggable database altered.


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


常見錯誤：ORA-14696: MAX_STRING_SIZE migration is incomplete for pluggable database


如果將CDB執行以上操作，會在執行完指令碼rdbms/admin/utl32k.sql後重新啟動資料庫報錯。


ORACLE instance started.


Fixed Size 2288728 bytes
Database Buffers 603979776 bytes
Database mounted.
ORA-14696: MAX_STRING_SIZE migration is incomplete for pluggable database
Process ID: 20105
這段報錯的意識是說CDB資料庫已經是migrate狀態了（startup upgrade），但是PDB$SEED還不是。需要對PDB$SEED做寫操作。


SYS@cdb > startup mount;
我們需要PDB$SEED處於MIGRATE狀態
CON_ID     NAME                           OPEN_MODE
2          PDB$SEED                       MIGRATE
SYS@cdb > alter session set container=pdb$seed;


SYS@cdb > alter system set max_string_size = extended scope=both;


當PDB$SEED處理結束後，接下來可以繼續做PBD的擴充套件
SYS@cdb > alter pluggable database pdb1 open upgrade;


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


SYS@cdb > alter pluggable database open;







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

本文作者：小麥苗，只專注於資料庫的技術，更注重技術的運用

本文在itpub（ ）、部落格園 http://www.cnblogs.com/lhrbest 和個人微信公眾號（ ）上有同步更新

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

本文部落格園地址： http://www.cnblogs.com/lhrbest/p/6404355.html

本文pdf版 小麥苗雲盤地址： ● QQ ●  ，註明新增緣由

於   在上 ●  ●  微信客戶端 左邊 xiaomaimiaolhr，掃描 的二維碼加入小麥苗的QQ群，學習最實用的資料庫技術。



  

<1span style="font-family:;" "="">