oracle12c 自增长,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;

CON_ID

CON_NAME OPEN

MODE RESTRICTED

----------

------------------------------ ---------- ----------

2

PDB$SEED READ

ONLY NO

3

ORA12CDBPDB MOUNTED

SQL> alter pluggable database

ORA12CDBPDB open upgrade;

Pluggable database

altered.

SQL> show pdbs;

CON_ID

CON_NAME OPEN

MODE RESTRICTED

----------

------------------------------ ---------- ----------

2

PDB$SEED READ

ONLY NO

3

ORA12CDBPDB MIGRATE YES

注意所有操作只针对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;

CON_ID

CON_NAME OPEN

MODE RESTRICTED

---------- ------------------------------

---------- ----------

3

ORA12CDBPDB MIGRATE YES

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;

INV_ID

INV_DESC

----------

------------------------------------------------------------

1

Book

2

tABLE

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作为标的主键，性能方面有一定的提升。