Saturday, November 16, 2013

Oracle 12c new features - varchar2, nvarchar2 and raw size upto 32K

SQL> select * from v$version;

BANNER                                                                               CON_ID
-------------------------------------------------------------------------------- ----------
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production              0
PL/SQL Release 12.1.0.1.0 - Production                                                    0
CORE    12.1.0.1.0      Production                                                                0
TNS for Solaris: Version 12.1.0.1.0 - Production                                          0
NLSRTL Version 12.1.0.1.0 - Production                                                    0

SQL> create table t1 (name varchar(32767));
create table t1 (name varchar(32767))
                              *
ERROR at line 1:
ORA-00910: specified length too long for its datatype

SQL> show parameter string

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
max_string_size                      string      STANDARD

SQL> alter system set max_string_size=EXTENDED;
alter system set max_string_size=EXTENDED
*
ERROR at line 1:
ORA-02097: parameter cannot be modified because specified value is invalid
ORA-14694: database must in UPGRADE mode to begin MAX_STRING_SIZE migration

QL> conn / as sysdba
Connected.
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

oracle@s11:~$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.1.0 Production on Fri Nov 15 04:49:02 2013

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> startup upgrade
ORACLE instance started.

Total System Global Area 1570009088 bytes
Fixed Size                  2361496 bytes
Variable Size            1040189288 bytes
Database Buffers          520093696 bytes
Redo Buffers                7364608 bytes
Database mounted.
Database opened.
SQL> alter system set max_string_size=EXTENDED;
SQL> @?/rdbms/admin/utl32k.sql

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area 1570009088 bytes
Fixed Size                  2361496 bytes
Variable Size            1056966504 bytes
Database Buffers          503316480 bytes
Redo Buffers                7364608 bytes
Database mounted.
Database opened.

SQL> conn donghua/donghua
Connected.
SQL> create table t1 (name varchar(32767));

Table created.

SQL> create table t1 (name varchar(32768));
create table t1 (name varchar(32768))
                              *
ERROR at line 1:
ORA-00910: specified length too long for its datatype