Oracle 12c extended to support varchar2 > 4000 bytes doesn't work for user who is not sysdba

Question

On oracle 12c compatible 12.0.0, changed to extended with sysdba privileges. I can create a table with varchar2(16000) as column now and insert a string > 4000 bytes; but only when connected as sysdba. When connected as a normal user rather than sysdba, I cannot play with varchar2 >4000 bytes, an error ORA-60019 is thrown. Can anyone explain why? the param max_string_size= extended and compatible=12.0.0 when logged in as a user who is not a sysdba.

Answer 1

Do following steps and let me know if the issue is resolved. I am asking to set the parameter again just to make sure everything is in order.

1) Back up your spfile ( get location of spfile)

sqlplus / as sysdba
show parameter spfile;

2) Shut down the database.

sqlplus / as sysdba
shutdown immediate

3) Restart the database in UPGRADE mode.

startup upgrade

4) Change the setting of MAX_STRING_SIZE to EXTENDED.

alter system set MAX_STRING_SIZE ='EXTENDED' scope=spfile;

5)

sqlplus / as sysdba
@%ORACLE_HOME%\RDBMS\ADMIN\utl32k.sql
 @%ORACLE_HOME%\RDBMS\ADMIN\utlrp.sql

Note: The utl32k.sql script increases the maximum size of the VARCHAR2, NVARCHAR2, and RAW columns for the views where this is required. The script does not increase the maximum size of the VARCHAR2, NVARCHAR2, and RAW columns in some views because of the way the SQL for those views is written.

rdbms/admin/utlrp.sql script helps to recompile invalid objects. You must be connected AS SYSDBA to run the script.

6) Restart the database in NORMAL mode.

sqlplus / as sysdba
shutdown immediate
startup;
show parameter MAX_STRING_SIZE; 

7) create new table with column datatype varchar2 having more than 4000 size.