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

You must change your file "TNSNAMES.ORA" to connect by PDB. I was with the same problem. I have solved with the information of link bellow.

https://dba.stackexchange.com/questions/240761/in-oracle-12c-tryiyng-to-create-table-with-columns-greater-than-4000

The reason for that behaviour is that you are in a multi-tenant environment, i.e. a master container called the CDB ("Container Database"), and any number of PDBs ("Pluggable Databases").

The CDB ("container") is a kind of "system" database that is there to contain the actual customer databases ("pluggable databases" or PDBs). The CDB is not intended to receive any customer data whatsoever. Everything goes into one or more PDBs.

When you connect without specifying any service, you are automatically placed in the CDB. The extended strings parameter is ignored for the CDB: the limit remains 4000 bytes. The following connects to the CDB. Creating a table with a long string is rejected, just like in your case:

Answer 2

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.