[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.
--//^_^,有点问题,并没有限制字段的宽度.仅仅做一个记录也许以后有用!!