0

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

1 Answer 1

2

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

4
  • Thanks Balazs, I just tried following the steps described in that article, however, I'm still getting the same error when running the CREATE TABLE command as the common user.
    – Templar
    Commented Aug 1, 2022 at 19:11
  • @Templar Did you connect to a pluggable database to create the table? The above documentation specifically mentions this: 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. Commented Aug 1, 2022 at 20:29
  • @Templar See my update in my answer. Commented Aug 1, 2022 at 20:44
  • Thank you, that worked! As you said, the trick was to connect to the PDB using sqlplus C##COMMERCEDB/mypassword@localhost:1521/ORCLPDB1 instead of just sqlplus C##COMMERCEDB/mypassword.
    – Templar
    Commented Aug 1, 2022 at 21:57

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.