Oracle not allowing large fields for common users after setting max_string_size=EXTENDED

All we need is an easy explanation of the problem, so here it is.

I’m using an Oracle 19.3 enterprise Docker container and enabling max_string_size=EXTENDED. When creating a table with a large field using the sys user, Oracle allows the table to be created. But when attempting to create a table with a large field using a common user, Oracle acts as if extended mode has not been enabled. Here are the reproduction steps:

  1. Navigate to the Oracle Container Registry.
  2. Click on "Database", then “enterprise“.
  3. Accept the license terms on the right side top. Sign-in is required.
  4. Ensure that Docker Desktop is installed and running on your local machine.
  5. In a command prompt, run the shell commands below to login, download, and run the docker image:
docker login -u <email> -p <password> container-registry.oracle.com
docker run -d -it --name oracle-19-db -p 1521:1521 -e ORACLE_PWD=Oradoc_db1 container-registry.oracle.com/database/enterprise:19.3.0.0
  1. Wait a few minutes for the Oracle database to startup – check the logs in Docker Desktop to view progress.
  2. Open a terminal window to the Docker container using the Docker Desktop icon.
  3. Run export ORACLE_SID=ORCLCDB
  4. Run sqlplus sys/Oradoc_db1 as sysdba
  5. Within sqlplus, run the following commands:
SHUTDOWN IMMEDIATE
STARTUP UPGRADE
ALTER SYSTEM SET max_string_size=EXTENDED scope=spfile;
START $ORACLE_HOME/rdbms/admin/utl32k.sql
SHUTDOWN IMMEDIATE
STARTUP
  1. Within sqlplus, verify that extended mode is working:
SHOW PARAMETER max_string_size
CREATE TABLE char_test(c1 varchar2(32767));
  1. Within sqlplus, create the common user:
CREATE USER C##COMMERCEDB PROFILE DEFAULT IDENTIFIED BY mypassword
DEFAULT TABLESPACE USERS
TEMPORARY TABLESPACE TEMP
QUOTA UNLIMITED ON USERS ACCOUNT UNLOCK;

GRANT CONNECT, CREATE TABLE, CREATE SEQUENCE, CREATE PROCEDURE TO C##COMMERCEDB;
GRANT EXECUTE ON DBMS_XA TO C##COMMERCEDB;
GRANT SELECT ON PENDING_TRANS$ TO C##COMMERCEDB;
GRANT SELECT ON DBA_2PC_PENDING TO C##COMMERCEDB;
GRANT SELECT ON DBA_PENDING_TRANSACTIONS TO C##COMMERCEDB;
  1. Exit sqlplus and reconnect as the common user:
quit
sqlplus C##COMMERCEDB/mypassword
  1. Within sqlplus, attempt to create a table with a large field:
CREATE TABLE char_test(c1 varchar2(32767));

The error occurs:

ERROR at line 1:
ORA-00910: specified length too long for its datatype

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

You have a container database. That is not how you enable Extended Datatypes in a CDB.

MAX_STRING_SIZE

I will not copy and format the steps here due to their length, just follow this section:

Increasing the Maximum Size of VARCHAR2, NVARCHAR2, and RAW Columns in a CDB

And you should work and create objects in a pluggable database, not the root container, that goes against the concept.

Update:

The above documentation specifically mentions this will not work in CDB root:

Note: The root continues to use STANDARD semantics even after MAX_STRING_SIZE is set to EXTENDED. The reason for setting
MAX_STRING_SIZE to EXTENDED in the root is so all the PDBs in the CDB
can inherit the EXTENDED setting from the root.

Here my output of making it work:

https://pastebin.com/UaMQJtJL

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply