Geek Answers Handbook

Oracle 12c extended to support varchar2> 4000 bytes not working for user who is not sysdba

In oracle 12c compatible 12.0.0, changed to extended with sysdba privileges. I can create a table with varchar2 (16000) as the column currently and insert a row> 4000 bytes; but only when connecting as sysdba. When connecting as a regular user, not sysdba, I can not play with varchar2> 4000 bytes, an ORA-60019 error is thrown. Can anyone explain why? The max_string_size = extended and compatible = 12.0.0 parameter when logging in as a user who is not sysdba.

+4
source share
1 answer

Follow these steps and let me know if the problem is resolved. I ask you to set the parameter again to make sure everything is in order.

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

sqlplus / as sysdba
show parameter spfile;

2) Close the database.

sqlplus / as sysdba
shutdown immediate

3) Reboot 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: utl32k.sql script increases the maximum size of VARCHAR2, NVARCHAR2 and RAW for views where this is required. The script does not increase the maximum size of VARCHAR2, NVARCHAR2, and RAW in some views because of the way SQL is written for these views.

The rdbms / admin / utlrp.sql script helps recompile invalid objects. You need to connect AS SYSDBA to run the script.

6) Reboot the database in NORMAL mode.

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

7) varchar2, 4000.

0

More articles:


All Articles