Oracle:可扩展的字符类型
在Oracle Database 12c之前,关于字符类数据类型的长度的上限如下:
varchar2,最大为4000B。
RAW,最大为2000B。
从12c开始,它们的最大长度已经扩展到了:32767B。
默认情况下,最大字符长度的扩展是关闭的:
1 2 3 4 5 6 |
SQL> show parameter max_string_size NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ max_string_size string STANDARD SQL> |
这时候,如果你打算创建超过了12c之前的上限的数据长度,则会报错。
如下所示:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 |
SQL> create table beyond 2 ( 3 name varchar2(5000) 4 ); name varchar2(5000) * ERROR at line 3: ORA-00910: specified length too long for its datatype SQL> create table beyond 2 ( 3 name raw(3000) 4 ); name raw(3000) * ERROR at line 3: ORA-00910: specified length too long for its datatype SQL> |
要实现可扩展字符类型,需要将参数“max_string_size”置为:EXTENDED。
该设置需要在数据库处于UPGRADE模式下完成。
具体操作如下所示:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 |
SQL> select * from v$version; BANNER -------------------------------------------------------------------------------- CON_ID ---------- Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production 0 PL/SQL Release 12.1.0.2.0 - Production 0 CORE 12.1.0.2.0 Production 0 BANNER -------------------------------------------------------------------------------- CON_ID ---------- TNS for Linux: Version 12.1.0.2.0 - Production 0 NLSRTL Version 12.1.0.2.0 - Production 0 SQL> SQL> shutdown immediate; Database closed. Database dismounted. ORACLE instance shut down. SQL> SQL> startup upgrade ORACLE instance started. Total System Global Area 838860800 bytes Fixed Size 2929936 bytes Variable Size 562039536 bytes Database Buffers 268435456 bytes Redo Buffers 5455872 bytes Database mounted. Database opened. SQL> select instance_name,status from v$instance; INSTANCE_NAME STATUS ---------------- ------------ allah OPEN MIGRATE SQL> SQL> show parameter max_string_size NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ max_string_size string STANDARD SQL> 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 SQL> |
然后,使用SYS用户执行脚本:?/rdbms/admin/utl32k.sql
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 |
SQL> !ls -l $ORACLE_HOME/rdbms/admin/ | grep --color utl32k.sql -rw-r--r-- 1 oracle oinstall 9564 Dec 3 2013 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. 0 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. SQL> |
然后,关闭数据库,并以正常方式重新启动:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 |
SQL> select instance_name,status from v$instance; INSTANCE_NAME STATUS ---------------- ------------ allah OPEN MIGRATE SQL> shutdown immediate; Database closed. Database dismounted. ORACLE instance shut down. SQL> SQL> startup ORACLE instance started. Total System Global Area 838860800 bytes Fixed Size 2929936 bytes Variable Size 562039536 bytes Database Buffers 268435456 bytes Redo Buffers 5455872 bytes Database mounted. Database opened. SQL> SQL> select instance_name,status from v$instance; INSTANCE_NAME STATUS ---------------- ------------ allah OPEN SQL> |
再次尝试之前的建表:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 |
SQL> create table beyond_varchar2 2 ( 3 name varchar2(32757) 4 ); Table created. SQL> create table beyond_raw 2 ( 3 name raw(32757) 4 ); Table created. SQL> SQL> desc beyond_varchar2 Name Null? Type ----------------------------------------- -------- ---------------------------- NAME VARCHAR2(32757) SQL> desc beyond_raw Name Null? Type ----------------------------------------- -------- ---------------------------- NAME RAW(32757) SQL> |
可以看到,可扩展的字符类型生效了。
事实上,可扩展的字符类型,就像数据库内部实现的大对象。
你可以通过dba_lobs获得这方面的信息:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 |
SQL> desc dba_lobs Name Null? Type ----------------------------------------- -------- ---------------------------- OWNER VARCHAR2(128) TABLE_NAME VARCHAR2(128) COLUMN_NAME VARCHAR2(4000) SEGMENT_NAME VARCHAR2(128) TABLESPACE_NAME VARCHAR2(30) INDEX_NAME VARCHAR2(128) CHUNK NUMBER PCTVERSION NUMBER RETENTION NUMBER FREEPOOLS NUMBER CACHE VARCHAR2(10) LOGGING VARCHAR2(7) ENCRYPT VARCHAR2(4) COMPRESSION VARCHAR2(6) DEDUPLICATION VARCHAR2(15) IN_ROW VARCHAR2(3) FORMAT VARCHAR2(15) PARTITIONED VARCHAR2(3) SECUREFILE VARCHAR2(3) SEGMENT_CREATED VARCHAR2(3) RETENTION_TYPE VARCHAR2(7) RETENTION_VALUE NUMBER SQL> col table_name for a23 SQL> col column_name for a23 SQL> col segment_name for a32 SQL> col tablespace_name for a23 SQL> SQL> set linesize 300 SQL> set pagesize 300 SQL> SQL> select count(*) from dba_lobs; COUNT(*) ---------- 858 SQL> select table_name,column_name,segment_name,tablespace_name,in_row from dba_lobs where table_name like 'BEYOND%'; TABLE_NAME COLUMN_NAME SEGMENT_NAME TABLESPACE_NAME IN_ ----------------------- ----------------------- -------------------------------- ----------------------- --- BEYOND_RAW NAME SYS_LOB0000092661C00001$$ SYSTEM YES BEYOND_VARCHAR2 NAME SYS_LOB0000092658C00001$$ SYSTEM YES SQL> |
遵照标准的大对象存储规则:
Oracle会在表中,先设置4000B的内嵌存储空间,而稍后,任何多余4000B的数据都会被存储到大对象段中。
即上面LOG中的:
SYS_LOB0000092661C00001$$
SYS_LOB0000092658C00001$$
————————————————————
Ending。