[20170618]12c 支持更长的 identifiers.txt

--//12c 支持更长的索引与表名,带来的问题就是sqlplus显示混乱.太长了.
--//必须有一个方法改变显示列的宽度.

SCOTT@test01p> @ ver1
PORT_STRING                    VERSION        BANNER                                                                               CON_ID
------------------------------ -------------- -------------------------------------------------------------------------------- ----------
IBMPC/WIN_NT64-9.1.0           12.1.0.1.0     Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production              0

--//我当前的版本还不支持长表名.参考链接:http://blog.itpub.net/267265/viewspace-1991846/
--//在那个链接里,我已经提到oracle将来会支持更长的表名.

--//实际上这个带来的问题还有许多,比如目前使用的一些工具比如toad,PLSQLDev等等.还有一些执行计划的显示在sqlplus(居然没有人测试这样的情况^_^).

--//为了目前在sqlplus能很好的显示,我建立一个文本重新定义长度20.

SELECT DISTINCT 'column ' || column_name || ' format a20;' result
    FROM dba_tab_cols
   WHERE data_type = 'VARCHAR2' AND data_length = 128
ORDER BY 1;

--//把输出保存下来128.txt,修改glogin.sql文件,加入如下:
@@128.txt

--//执行前:
SCOTT@test01p> select index_name, status,orphaned_entries from dba_indexes where owner=user and index_name='INDEX1';
INDEX_NAME                                                                                                                       STATUS   ORP
-------------------------------------------------------------------------------------------------------------------------------- -------- ---
INDEX1                                                                                                                           VALID    NO

--//前后对比就很明显了.

SCOTT@test01p> select index_name, status,orphaned_entries from dba_indexes where owner=user and index_name='INDEX1';
INDEX_NAME           STATUS               ORP
-------------------- -------------------- ---
INDEX1               VALID                NO

--//不过也带来另外的问题,明显status加宽了,暂时凑合吧.

--//找到一个链接,可以限制identifiers长度不要大于30.
--//www.toadworld.com/platforms/oracle/b/weblog/archive/2017/02/07/oracle-12-2-new-feature-longer-object-column-names
create or replace trigger ddl_trigger
before create or alter on demo.SCHEMA
declare
   l_obj   varchar2(128);
   l_dba   int;
begin
   l_obj := ora_dict_obj_name;
   select count(*)
   into l_dba
   from dba_role_privs
   where grantee = USER
   and granted_role = 'DBA';
   if l_dba = 0 and length(l_obj) > 30 then
      raise_application_error(-20000,'Identifier "'||l_obj||'" is too long');
   end if;
end;

--//测试看看:(这里测试>20,另外我的scott用户有dba权限)
create or replace trigger ddl_trigger
before create or alter on scott.SCHEMA
declare
   l_obj   varchar2(128);
   l_dba   int;
begin
   l_obj := ora_dict_obj_name;
   if length(l_obj) > 20 then
      raise_application_error(-20000,'Identifier "'||l_obj||'" is too long');
   end if;
end;
/

SCOTT@book> create table t1234567890123456789011 (a number);
create table t1234567890123456789011 (a number)
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-20000: Identifier "T1234567890123456789011" is too long
ORA-06512: at line 7

SCOTT@book> create table t123 (a number);
Table created.

SCOTT@book> create index i_t12345678901234567890 on t123(a);
create index i_t12345678901234567890 on t123(a)
                                        *
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-20000: Identifier "I_T12345678901234567890" is too long
ORA-06512: at line 7

SCOTT@book> alter table t123 rename column a to a12345678901234567890;
Table altered.

--//^_^,有点问题,并没有限制字段的宽度.仅仅做一个记录也许以后有用!!