首页 » Cloud » 12C R2 new feature: 128 bytes for identifiers (表名长度可用128字节)

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

  • 2016/09/28
  • Cloud
  • 1,400 views
  • 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.

 

打赏

对不起，这篇文章暂时关闭评论。

上一篇：

下一篇：