Oracle Cloud Infrastructure Documentation

Data Types

Autonomous Database allows most of the data types available in Oracle Database. To ensure the security and the performance of Autonomous Database, some data types are restricted.

The following data types are not supported or have limited support in Autonomous Database:

  • Large Object (LOB) data types: only SecureFiles LOB storage is supported. BasicFiles LOBs are automatically converted to SecureFiles LOBs.

  • Media types are not supported (Oracle Multimedia is desupported)

Checking and Setting MAX_STRING_SIZE

By default Autonomous Database uses extended data types and the value of MAX_STRING_SIZE is set to the value EXTENDED. With this setting you can specify a maximum size of 32767 bytes for the VARCHAR2, NVARCHAR2, and RAW data types. The default, EXTENDED, is the recommended setting and allows Autonomous Database to take full advantage of database capabilities.

Use DBMS_MAX_STRING_SIZE subprograms to check usage of extended data types and to change the database to revert to the older style STANDARD, supporting a maximum size of 4000 bytes for VARCHAR2, NVARCHAR2, and RAW data types.

Note

Using DBMS_MAX_STRING_SIZE.MODIFY_MAX_STRING_SIZE is a one-way change that cannot be reverted. After a database is switched back to the STANDARD style of supporting a maximum length of 4000 bytes for the VARCHAR2, NVARCHAR2, and RAW data types, you cannot re-enable EXTENDED data types.

The ADMIN user is granted EXECUTE privilege WITH GRANT OPTION clause on DBMS_MAX_STRING_SIZE. Oracle recommends that you do not GRANT EXECUTE on this package to other users.

  1. Check whether your environment can be reverted to the old style, STANDARD behavior: 

    SELECT * FROM TABLE(DBMS_MAX_STRING_SIZE.CHECK_MAX_STRING_SIZE('STANDARD'));

    See CHECK_MAX_STRING_SIZE Function for more information.

  2. Check and correct all reported violations from Step 1, if applicable.

  3. After fixing any reported violations found in Step 1, if you want to revert to a maximum length of 4000 bytes for VARCHAR2, NVARCHAR2, and RAW data types, use DBMS_MAX_STRING_SIZE.MODIFY_MAX_STRING_SIZE as follows: 

    EXEC DBMS_MAX_STRING_SIZE.MODIFY_MAX_STRING_SIZE('STANDARD');

    See MODIFY_MAX_STRING_SIZE Procedure for more information.

See Extended Data Types for details on extended data types.

For a list of Oracle data types see Oracle Database SQL Language Reference.