oracle12c 表名长度,12C R2 new feature: 128 bytes for identifiers (表名长度可用128字节)

昨日见老张提到了12C R2这个新特性,表、列名可以使用长度有原来的30扩到了128字节， 拿来亲测一下。

[oracle@anbob ~]$ sqlplus sys/oracle as sysdba

SQL*Plus: Release 12.2.0.0.0 Beta on Wed Sep 28 09:37:02 2016

Copyright (c) 1982, 2015, Oracle. All rights reserved.

Connected to:

Oracle Database 12c Enterprise Edition Release 12.2.0.0.1 - 64bit Beta

With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

sys@pdborcl:orcl> show pdbs

CON_ID CON_NAME OPEN MODE RESTRICTED

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

3 PDBORCL READ WRITE NO

Note:

使用的测试版本是12.2 beta1，有没有发现使用SQLPLUS SYS直接登录后是指定的PDB而不是cdb$root? 🙂

sys@pdborcl:orcl> CREATE table anbob_com_copyright_anbob_com_copyright_anbob_com_copyright_anbob_com_copyright_t(id int, col_name_col_name_col_name_col_name_col_name_col_name_col_name_col_name_col_name_largecolumn varchar2(5000));

Table created.

sys@pdborcl:orcl> insert into anbob_com_copyright_anbob_com_copyright_anbob_com_copyright_anbob_com_copyright_t values(1,'anbob.com');

1 row created.

Note:

注意到表名长度，列名长度都超过了老版本养成的共识30 字符 长度限制， 请原谅的我表名起这么自恋，实在是有些人转载原文不动都不注明出处。

sys@pdborcl:orcl> alter session set container=cdb$root;

Session altered.

sys@pdborcl:orcl> show pdbs

CON_ID CON_NAME OPEN MODE RESTRICTED

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

2 PDB$SEED READ ONLY NO

3 PDBORCL READ WRITE NO

sys@pdborcl:orcl> select lengthb('anbob_com_copyright_anbob_com_copyright_anbob_com_copyright_anbob_com_copyright_t') from dual;

LENGTHB('ANBOB_COM_COPYRIGHT_ANBOB_COM_COPYRIGHT_ANBOB_COM_COPYRIGHT_ANBOB_COM_COPYRIGHT_T')

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

81

sys@pdborcl:orcl> select lengthb('anbob_com_copyright_anbob_com_copyright_anbob_com_copyright_anbob_com_copyright_anbob_com_copyright_anbob_com_copyright_anbob_com') LENGTHBC from dual;

LENGTHBC

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

129

1 row selected.

sys@pdborcl:orcl> CREATE table anbob_com_copyright_anbob_com_copyright_anbob_com_copyright_anbob_com_copyright_anbob_com_copyright_anbob_com_copyright_anbob_comid int);

CREATE table anbob_com_copyright_anbob_com_copyright_anbob_com_copyright_anbob_com_copyright_anbob_com_copyright_anbob_com_copyright_anbob_comid int)

*

ERROR at line 1:

ORA-00972: identifier is too long

[oracle@anbob ~]$ oerr ora 972

00972, 00000, "identifier is too long"

// *Cause: An identifier with more than 128 bytes was specified,

// or a password identifier longer than 128 bytes was specified.

// *Action: Specify at most 128 bytes for identifiers,

// and at most 128 bytes for password identifiers.

# 12cr1 11g and below version

[oracle@kdzwd1:/home/oracle]> oerr ora 972

00972, 00000, "identifier is too long"

// *Cause: An identifier with more than 30 characters was specified.

// *Action: Specify at most 30 characters.

[oracle@anbob ~]$ exit

exit

sys@pdborcl:orcl> CREATE table anbob_com_copyright_anbob_com_copyright_anbob_com_copyright_anbob_com_copyright_anbob_com_copyright_anbob_com_copyright_anbob_co(id int);

CREATE table anbob_com_copyright_anbob_com_copyright_anbob_com_copyright_anbob_com_copyright_anbob_com_copyright_anbob_com_copyright_anbob_co(id int)

*

ERROR at line 1:

ORA-65023: active transaction exists in container PDBORCL

sys@pdborcl:orcl> show con_name

CON_NAME

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

CDB$ROOT

sys@pdborcl:orcl> alter session set container=pdborcl;

Session altered.

sys@pdborcl:orcl> commit;

Commit complete.

sys@pdborcl:orcl> alter session set container=cdb$root;

Session altered.

sys@pdborcl:orcl> CREATE table anbob_com_copyright_anbob_com_copyright_anbob_com_copyright_anbob_com_copyright_anbob_com_copyright_anbob_com_copyright_anbob_co(id int);

Table created.

Note:

从12c r2 Release起表名和列名的长度限制为128 bytes.

打赏

微信扫一扫，打赏作者吧～