12cR2, TBS, DB Name 외 DB Object Name 30자 제한 해제됨

12cR2, TBS, DB Name 외 DB Object Name 30자 제한 해제됨

: 정확히는 compatible parameter 값이 12.2.0 이상인 경우

 링크 1. 

 1. https://docs.oracle.com/en/database/oracle/oracle-database/12.2/sqlrf/Database-Object-Names-and-Qualifiers.html#GUID-75337742-67FD-4EC0-985F-741C93D918DA

 2. https://sqlmaria.com/2017/03/08/longer-identifiers-in-oracle-database-12c-release-2/

가.. COMPATIBLE >= 12.2.0 

   : DBNAME, DISKGROUP NAME, PDB, ROLLBACK SEGMENT, TABLESPACE, TABLESPACE SET 제외 하고, 

     30 BYTES 제한 해제  --> 128 Bytes 까지 가능함 !!!

나.  COMPATIBLE <  12.2.0 

   : DB OBJECT NAME 은 30 Bytest 제한 

다. 주의 및 참고 사항

### [주의] compatible 파라미터를 올린 후에, 다시 내릴수 없음  

     --> 대안으로 optimizer_features_enable 검토

### [참고] compatible 파라미터 내린 후, 재기동 시 오류 발생 함 

       ORA-00201: control file version 12.2.0.0.0 incompatible with ORACLE version 12.1.0.2.0

       ORA-00202: control file: '/u01/ORACLE/TGDB/c01/controlTGDB01.ctl'

- 12.1 -> 12.2 Upgrade 이후 
SQL> alter system set compatible='12.2.0' scope=spfile ;
System altered.
 
SQL> shutdown immediate ;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
 
Total System Global Area 4294967296 bytes
Fixed Size                  8628936 bytes
Variable Size            1325401400 bytes
Database Buffers         2952790016 bytes
Redo Buffers                8146944 bytes
Database mounted.
Database opened.
 
SQL> select * from v$version ;
 
BANNER                                                                               CON_ID
-------------------------------------------------------------------------------- ----------
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production              0
PL/SQL Release 12.2.0.1.0 - Production                                                    0
CORE    12.2.0.1.0      Production                                                        0
TNS for Linux: Version 12.2.0.1.0 - Production                                            0
NLSRTL Version 12.2.0.1.0 - Production                                                    0
 
SQL> show parameters compatible
 
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
compatible                           string      12.2.0
SQL> select length('a123456789012345678901234567890') from dual ;
 
LENGTH('A123456789012345678901234567890')
-----------------------------------------
                                       31
 
SQL> create table A123456789012345678901234567890 ( a1 number ) ;
 
Table created.
 
SQL> col owner for a30
SQL> col table_name for a50
SQL> select owner, table_name, length(table_name) from dba_tables                                 
  2  where owner = user
  3  and   table_name ='A123456789012345678901234567890';
 
 
OWNER                          TABLE_NAME                                         LENGTH(TABLE_NAME)
------------------------------ -------------------------------------------------- ------------------
SYS                            A123456789012345678901234567890