12c 可扩展的字符类型MAX_STRING_SIZE , varchar2 到32k长度



SQL> show parameter compatible

NAME                                 TYPE
------------------------------------ ---------------------------------
VALUE
------------------------------
compatible                           string
12.1.0.2.0
noncdb_compatible                    boolean
FALSE

SQL>  show parameter max_sql_string_size;

SQL>  alter system set max_sql_string_size='EXTENDED' scope=spfile;
 alter system set max_sql_string_size='EXTENDED' scope=spfile
                  *
ERROR at line 1:
ORA-02065: illegal option for ALTER SYSTEM

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

SQL> startup upgrade;
ORACLE instance started.

Total System Global Area 1610612736 bytes
Fixed Size                  2924928 bytes
Variable Size            1224740480 bytes
Database Buffers          369098752 bytes
Redo Buffers               13848576 bytes
Database mounted.
Database opened.

SQL> alter system set max_string_size=EXTENDED scope=both;

System altered.

SQL> @?/rdbms/admin/utl32k.sql

Session altered.

DOC>#######################################################################
DOC>#######################################################################
DOC>   The following statement will cause an "ORA-01722: invalid number"
DOC>   error if the database has not been opened for UPGRADE.
DOC>
DOC>   Perform a "SHUTDOWN ABORT"  and
DOC>   restart using UPGRADE.
DOC>#######################################################################
DOC>#######################################################################
DOC>#

no rows selected

DOC>#######################################################################
DOC>#######################################################################
DOC>   The following statement will cause an "ORA-01722: invalid number"
DOC>   error if the database does not have compatible >= 12.0.0
DOC>
DOC>   Set compatible >= 12.0.0 and retry.
DOC>#######################################################################
DOC>#######################################################################
DOC>#

PL/SQL procedure successfully completed.

Session altered.

29 rows updated.

Commit complete.

System altered.

PL/SQL procedure successfully completed.

Commit complete.

System altered.

Session altered.

PL/SQL procedure successfully completed.

No errors.

Session altered.

PL/SQL procedure successfully completed.

Commit complete.

Package altered.

Package altered.

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

SQL> startup
ORACLE instance started.

Total System Global Area 1610612736 bytes
Fixed Size                  2924928 bytes
Variable Size            1224740480 bytes
Database Buffers          369098752 bytes
Redo Buffers               13848576 bytes
Database mounted.
Database opened.

SQL> show parameter max_string_size

NAME                                 TYPE
------------------------------------ ---------------------------------
VALUE
------------------------------
max_string_size                      string
EXTENDED