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 。

 
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.

與早期版本相比，諸如 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 擴充套件字元型別存在以下的限制：

 

 

 

  
			  
			  
			當然我們也可以將已有的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

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:

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