昨日见老张提到了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.