Menu
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.
646
The maximum length for VARCHAR2 is 32672 BYTE or 8168 CHAR which is the same as the maximum length for VARCHAR of 32672 OCTETS or 8168 CODEUNITS32. Similarly, when the NVARCHAR2 data type is explicitly encountered in SQL statements, it is implicitly mapped following the same rules as the NVARCHAR data type.
You must specify size for VARCHAR2 . Minimum size is 1 byte or 1 character. Maximum size is: 32767 bytes or characters if MAX_STRING_SIZE = EXTENDED.
Maximum size is 4000 bytes or characters, and minimum is 1 byte or 1 character. You must specify size for VARCHAR2 . BYTE indicates that the column will have byte length semantics; CHAR indicates that the column will have character semantics.
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.
179
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
