12c - Extended Data Types

No Oracle Datatbase 12c, foi introduzida uma nova funcionalidade chamada de Extended Data Types.

Antes do Oracle Database 12c, o tamanho máximo para os tipos de dados VARCHAR2, NVARCHAR2 and RAW em colunas de tabelas era:

• VARCHAR2 : 4000 bytes
• NVARCHAR2 : 4000 bytes
• RAW : 2000 bytes

No Oracle Database 12c, o tamanho máximo para estes tipos de dados pode ser expandido para:

• VARCHAR2 : 32767 bytes
• NVARCHAR2 : 32767 bytes
• RAW : 32767 bytes

Vale lembrar que para estes tipos de dados, quando utilizados em PL/SQL o limite suportado já era de 32767.

Este aumento é controlado por um parâmetro chamado MAX_STRING_SIZE, cujo valor default é STANDARD. Para aumentar o tamanho máximo, devemos setá-lo para EXTENDED. Após isso devemos rodar o script utl32k.sql.

Após aumentar o tamanho máximo dos tipos de dados, não é possível desfazer esta alteração.

Passos para realizar esta alteração:

SHUTDOWN IMMEDIATE;
STARTUP UPGRADE;
ALTER SYSTEM SET max_string_size=extended;
@?/rdbms/admin/utl32k.sql
SHUTDOWN IMMEDIATE;
STARTUP;


select property_name, property_value
from database_properties
where property_name in
('NLS_NCHAR_CHARACTERSET',
'NLS_CHARACTERSET',
'NLS_LENGTH_SEMANTICS');

PROPERTY_NAME           PROPERTY_VALUE
----------------------- ---------------
NLS_NCHAR_CHARACTERSET  AL16UTF16
NLS_LENGTH_SEMANTICS    BYTE
NLS_CHARACTERSET        WE8MSWIN1252

Com o NLS_NCHAR_CHARACTERSET=AL16UTF16 cada caracter consome 2 bytes, por isso o campo foi criado com o tamanho 16383.

CREATE TABLE tabela_teste (
  Coluna1  VARCHAR2(32767),
  Coluna2  NVARCHAR2(16383),
  Coluna3  RAW(32767)
);
 
Table created.



select table_name, column_name, data_type, data_length 
from user_tab_columns
where table_name='TABELA_TESTE';
 
TABLE_NAME  COLUMN_NAME DATA_TYPE  DATA_LENGTH
----------  ----------- ---------  -----------
TABELA_TESTE    COLUNA1  VARCHAR2       32767
TABELA_TESTE    COLUNA2 NVARCHAR2       32766
TABELA_TESTE    COLUNA3       RAW       32767


INSERT INTO tabela_teste
SELECT
       LPAD('X', 32767, 'X'),
       LPAD('X', 16383, 'X'),
       UTL_RAW.cast_to_raw(LPAD('X', 32767, 'X'))
FROM   dual;
 
SELECT coluna1,
       LENGTH(coluna2),
       LENGTHB(coluna2),
       LENGTH(coluna3),
       LENGTH(coluna4)
FROM   tabela_teste;
 
  LENGTH(COLUNA1)  LENGTH(COLUNA2)  LENGTHB(COLUNA2)  LENGTH(COLUNA3)
-----------------  ---------------- ---------------- ----------------
            32767             16383           32767            32767
 


Campos VARCHAR2 e NVARCHAR2 maiores que 4000 bytes, ou campos RAW
maiores que 2000 bytes, serão armazenados como Lobs out-of-line.

Trecho retirado da documentação:

A VARCHAR2 or NVARCHAR2 data type with a declared size of greater than 4000 bytes, or a RAW data type with a declared size of greater than 2000 bytes, is an extended data type. Extended data type columns are stored out-of-line, leveraging Oracle's LOB technology. The LOB storage is always aligned with the table. In tablespaces managed with Automatic Segment Space Management (ASSM), extended data type columns are stored as SecureFiles LOBs. Otherwise, they are stored as BasicFiles LOBs. The use of LOBs as a storage mechanism is internal only. Therefore, you cannot manipulate these LOBs using the DBMS_LOB package.

Extended data types are subject to the same rules and restrictions as LOBs.

Existe um parâmetro oculto, o “_scalar_type_lob_storage_threshold“ que controla o tamanho máximo em bytes dos tipos de dados VARCHAR2, NVARCHAR2, e RAW que serão armazenados “inline”, como tipos de dados simples, sem a criação de “lob segments”.

Lembre-se que é um hidden parameter, deve ser usado por conta e risco.

Existe também uma restrição na criação de índices em colunas do tipo Extended Data Type Column:

 http://docs.oracle.com/cd/E16655_01/server.121/e17209/statements_5013.htm#SQLRF56300

Em caso de bancos Multitenant, deve-se:

• Atualizar(utl32k.sql) o container database - CDB$ROOT

• Atualizar(utl32k.sql) todas as pluggable databases (PDB$SEED + others)

Referências:

http://docs.oracle.com/cd/E16655_01/server.121/e17209/sql_elements001.htm#SQLRF30020

Abraço

Alex Zaballa