[20170618]12c 支援更長的 identifiers

[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.

--//^_^,有點問題,並沒有限制欄位的寬度.僅僅做一個記錄也許以後有用!!