Maybe you have wanted to use longer object names to be more descriptive. The Oracle RDBMS now supports up to 128 positions for both object and column names! The current naming length convention is 30. The other naming rules still apply such as:
- Starts with a character
- No spaces
- Underbars are acceptable
- Special characters are too…just be careful out there!
Please don’t get carried away. I know using Toad’s schema browser, I can filter objects by short acronyms perhaps.
We don’t need index names like ‘Index_on_EMP_table_drop_after_1_use_as_this_was_just_a_test’…and the index never gets renamed or dropped. I can see this happening.
So Oracle12.2 on, you can give your tables/indexes/materialized views/columns/PLSQL packages/procedures/functions much longer and more meaningful names.
Oracle Employee Chris Saxon shared this code with us at OOW16…to display indexes associated with tables and crop the names shorter to fit on your display.
Listagg(INDEX_NAME, ‘,’ on overflow truncate)
(order by INDEX_NAME) INDS
Group by TABLE_NAME;
Oracle employee Connor McDonald expanded on the topic with this code that would prevent longer object names if you do not have DBA privileges. Perhaps you can use this code to modify/monitor/enforce your company naming conventions.
create or replace trigger ddl_trigger
before create or alter on demo.SCHEMA
l_obj := ora_dict_obj_name;
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');