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:
- Navigate to the Oracle Container Registry.
- Click on "Database", then “enterprise“.
- Accept the license terms on the right side top. Sign-in is required.
- Ensure that Docker Desktop is installed and running on your local machine.
- 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:188.8.131.52
- Wait a few minutes for the Oracle database to startup – check the logs in Docker Desktop to view progress.
- Open a terminal window to the Docker container using the Docker Desktop icon.
sqlplus sys/Oradoc_db1 as sysdba
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
sqlplus, verify that extended mode is working:
SHOW PARAMETER max_string_size CREATE TABLE char_test(c1 varchar2(32767));
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;
sqlplusand reconnect as the common user:
quit sqlplus C##COMMERCEDB/mypassword
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.
You have a container database. That is not how you enable Extended Datatypes in a CDB.
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.
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:
Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂