加载中…Oracle12c可扩展字符类型及自增列
(2017-10-13 16:27:51)
标签：
可扩展字符类型自增列 |
分类： Oracle12c |
一、可扩展数据类型
Oracle12c之前，VARCHAR2和NVARCHAR2的最大长度为4000B，而RAW最大长度为2000B，从12c开始这些类型的最大长度已经扩展至32767B。
SQL> conn
system/oracle
Connected.
SQL> show parameter
max_string_size
NAME TYPE VALUE
------------------------------------
---------------------- ------------------------------
max_string_size string STANDARD
SQL> create table t1(name
varchar2(32727)) tablespace reg_data_tbs;
create table t1(name
varchar2(32727)) tablespace reg_data_tbs
ERROR at line 1:
ORA-00910: specified length too long
for its datatype
Elapsed: 00:00:00.00
SQL> !oerr ora 910
00910, 00000, "specified length too
long for its datatype"
// *Cause: for datatypes CHAR and
RAW, the length
specified was > 2000;
// otherwise,
the length specified was > 4000.
//
*Action: use a shorter length or
switch to a datatype permitting a
// longer
length such as a VARCHAR2, LONG CHAR, or LONG RAW
1、关闭数据库
SQL>shutdown
immediate
2、将数据库启动到升级模式
SQL>startup upgrade
SQL> show pdbs;
----------
------------------------------ ---------- ----------
SQL> alter pluggable database
ORA12CDBPDB open upgrade;
Pluggable database
altered.
SQL> show pdbs;
----------
------------------------------ ---------- ----------
注意所有操作只针对PDB,不要去操作CDB。待所有的PDB都处于UPGRADE模式。此时可以修改参数max_string_size，否则报如下错误：
SQL> SQL> alter system set
max_string_size=EXTENDED scope=both;
alter system set
max_string_size=EXTENDED scope=both
*
ERROR at line
1:
ORA-02097: parameter
cannot be modified because specified value is invalid
ORA-02095:
specified initialization parameter cannot be modified
3、将max_string_size设置为EXTENDED
登录到PDB中执行
[oracle:ora12cdb]$sqlplus
sys/oracle@ORA12CDBPDB as
sysdba
SQL> show pdbs;
---------- ------------------------------
---------- ----------
SQL> alter system set
max_string_size=EXTENDED scope=both;
System altered.
4、使用sys运行脚本utl32k.sql
SQL>@?/rdbms/admin/utl32k.sql
SP2-0042: unknown command "aRem" -
rest of line ignored.
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
--------------------------------------------------------------------------------
10/13/2017
16:05:46.461475000
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.
5、正常重启数据库
SQL>shutdown
immediate
SQL>startup
6、验证
SQL> create table t1(name
varchar2(32727)) tablespace reg_data_tbs;
Table created.
SQL> select
table_name,column_name,segment_name,tablespace_name,in_row from
dba_lobs where owner='SYS' and table_name='T1';
TABLE_NAME COLUMN_NAME SEGMENT_NAME TABLESPACE_NAME IN_ROW
------------------------------
-------------------- ------------------------------
---------------------
T1 NAME SYS_LOB0000073885C00001$$ REG_DATA_TBS YES
二、自增列
create table inv( inv_id
number generated as identity,inv_desc
varchar2(30 char));
SQL> create table inv( inv_id
number generated as identity,inv_desc varchar2(30
char));
Table created.
SQL>
SQL> alter table inv add
constraint inv_pk primary key(inv_id);
Table altered.
Elapsed: 00:00:00.05
SQL> insert into inv(inv_desc)
values('Book');
1 row created.
Elapsed: 00:00:00.00
SQL> insert into inv(inv_desc)
values('tABLE');
1 row created.
Elapsed: 00:00:00.00
SQL> commit;
Commit complete.
SQL> select * from
inv;
----------
------------------------------------------------------------
2 rows selected.
Elapsed: 00:00:00.00
SQL> select
sequence_name,min_value,increment_by from user_sequences where
sequence_name like 'ISE%';
SEQUENCE_NAME MIN_VALUE
INCREMENT_BY
------------------------------
---------- ------------
ISEQ$$_73888 1 1
SQL> select
table_name,identity_column from user_tab_columns where
identity_column='YES';
TABLE_NAME IDENTI
------------------------------
------
INV YES
还有如下有的实现方式：
create table inv( inv_id
number generated by default on null as
identity,inv_desc varchar2(30 char));
create table inv( inv_id
number generated
as identity(start with 50 increment by
2) ,inv_desc varchar2(30 char));
在Oracle12c之前，要实现自动增长，须通过Trigger+Sequence方式实现，一般很少有这种需求，但是在MySQL的应用场景自动增长ID作为标的主键，性能方面有一定的提升。
喜欢
0
赠金笔
前一篇：Oracle12c使用技巧