Long identifiers Oracle Database 12C release 2 (12.2)

New release 12.2 introduces possibility to generate long identifiers up to 128 characters for name like tables, views, triggers, columns and so on. In previous release it was limited to 30 characters.

The change is reflected in dictionary tables

desc DBA_TABLES

Name                      Null     Type           
------------------------- -------- -------------- 
OWNER                     NOT NULL VARCHAR2(128)  
TABLE_NAME                NOT NULL VARCHAR2(128)  
..   
CLUSTER_NAME                       VARCHAR2(128)  
IOT_NAME                           VARCHAR2(128)  
...  
CLUSTER_OWNER                      VARCHAR2(128)  

SELECT DISTINCT column_name 
  FROM dba_tab_columns
 WHERE data_length=128 AND data_type='VARCHAR2'
   AND owner='SYS'
ORDER BY 1;

COLUMN_NAME                   
------------------------------
ACL                           
ACL#                          
ACL_MVIEW_NAME                
ACL_NAME                      
ACL_OWNER                     
ACL_STATUS                    
ACTION_ARGUMENT_1             
ACTION_ARGUMENT_2             
ACTION_ARGUMENT_3             
ACTION_ARGUMENT_4             
ACTION_ARGUMENT_5 
...
XS_DATASEC_POLICY_NAME             
XS_ENABLED_ROLE                    
XS_NS_NAME                         
XS_PACKAGE_NAME                    
XS_PROCEDURE_NAME                  
XS_PROXY_USER_NAME                 
XS_SCHEMA_NAME                     
XS_TARGET_PRINCIPAL_NAME           
XS_USER_NAME                       
XTYPENAME                          
XTYPESCHEMANAME                    
ZMNAME                             
ZMOWNER                            
ZONEMAP_NAME                       

 1 323 rows selected

so here is simple example

CREATE TABLE test_table_with_very_very_long_name_122
(
  column_name_with_very_very_long_name_122 NUMBER,
  CONSTRAINT primary_key_with_very_very_long_name_122 PRIMARY KEY
  (column_name_with_very_very_long_name_122)
);

Have a fun 🙂

Tomasz

Leave a Reply

Your email address will not be published. Required fields are marked *