DBA Hub

📋Steps in this guide1/3

Long Identifiers in Oracle Database 12c Release 2 (12.2)

Oracle 12.2 increases the maximum size of most identifiers from 30 to 128 bytes, which makes migration from other database engines easier.

oracle 12cconfigurationintermediate
by OracleDba
17 views
1

Long Identifiers

At some point every DBA or developer will have hit a point where the 30 character limit for object names has caused a problem. This limit can be extremely painful when doing migration projects from SQL Server or MySQL to Oracle. In Oracle Database 12.2, the maximum length of most identifiers is now 128 bytes. In this example we create a table with a long name, long column names, a long constraint name, which references one of the long column names. Using excessively long identifiers can make code look rather clumsy. In the following example we interact with the table. Admittedly the table and column names are ridiculous, but it does show how clumsy excessively long identifiers can be.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
--drop table this_is_a_table_to_hold_employees_please_dont_put_customers_in_it purge;

create table this_is_a_table_to_hold_employees_please_dont_put_customers_in_it (
  this_is_the_primary_key_column_which_uniquely_identifies_the_row  number,
  this_is_for_the_employee_name_so_dont_put_other_crap_in_it        varchar2(100),
  constraint this_is_a_table_to_hold_employees_please_dont_put_customers_in_it_pk
    primary key (this_is_the_primary_key_column_which_uniquely_identifies_the_row)
);

Table created.

SQL>

insert into this_is_a_table_to_hold_employees_please_dont_put_customers_in_it (
  this_is_the_primary_key_column_which_uniquely_identifies_the_row,
  this_is_for_the_employee_name_so_dont_put_other_crap_in_it)
values (1, 'Tim');


select this_is_the_primary_key_column_which_uniquely_identifies_the_row,
       this_is_for_the_employee_name_so_dont_put_other_crap_in_it
from   this_is_a_table_to_hold_employees_please_dont_put_customers_in_it
order by this_is_the_primary_key_column_which_uniquely_identifies_the_row;

THIS_IS_THE_PRIMARY_KEY_COLUMN_WHICH_UNIQUELY_IDENTIFIES_THE_ROW
----------------------------------------------------------------
THIS_IS_FOR_THE_EMPLOYEE_NAME_SO_DONT_PUT_OTHER_CRAP_IN_IT
--------------------------------------------------------------------------------
                                                               1
Tim

1 row selected.

SQL>


update this_is_a_table_to_hold_employees_please_dont_put_customers_in_it
set    this_is_for_the_employee_name_so_dont_put_other_crap_in_it = 'Tim Hall'
where  this_is_the_primary_key_column_which_uniquely_identifies_the_row = 1;


delete from this_is_a_table_to_hold_employees_please_dont_put_customers_in_it
where  this_is_the_primary_key_column_which_uniquely_identifies_the_row = 1;
2

Views

This change has had quite an impact on internal tables, dynamic performance views and dictionary views. For example, describing the view shows several of the columns now support 128 byte identifiers. This is true of other views containing identifier names.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
SQL> desc dba_tables
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 OWNER                                     NOT NULL
VARCHAR2(128)
TABLE_NAME                                NOT NULL
VARCHAR2(128)
TABLESPACE_NAME                                    VARCHAR2(30)
 CLUSTER_NAME
VARCHAR2(128)
IOT_NAME
VARCHAR2(128)
STATUS                                             VARCHAR2(8)
 .
 .
 .
 CONTAINER_MAP_OBJECT                               VARCHAR2(3)

SQL>
3

Miscellaneous

- This feature was introduced in the Oracle database 12.1 beta, but was pulled before that release became generally available. If you check out the dictionary views in 12.1, most of them appear to support 128 byte identifier names. This is a throwback to the beta version. The first supported release that includes 128 byte identifiers is Oracle database 12.2. - As mentioned previously, using excessively long identifiers can make code look rather clumsy. You may want to exercise some control over the length of the identifiers you use. - Since some other database engines already support long identifiers, this feature makes migrations to Oracle from those engines much simpler. - If you are in a position where you are supporting multiple versions of the database, you will need to avoid using long identifier names, or you will prevent backwards compatibility. For more information see: - Database Object Naming Rules - Long Identifiers in Oracle Database 12.2 Onward Hope this helps. Regards Tim...

Comments (0)

Please to add comments

No comments yet. Be the first to comment!