DBMS_MAX_STRING_SIZE Package

The DBMS_MAX_STRING_SIZE package provides an interface for checking and changing the value of the DBMS_MAX_STRING_SIZE initialization parameter.

CHECK_MAX_STRING_SIZE Function

This function checks if the MAX_STRING_SIZE parameter can be updated to a given value and returns a list of violations that would prevent the parameter from being updated.

Syntax

DBMS_MAX_STRING_SIZE.CHECK_MAX_STRING_SIZE(
       new_value    IN VARCHAR2)
RETURN DBMS_MAX_STRING_SIZE_TBL;

Parameters

Parameter	Description
`new_value`	Specifies the new `MAX_STRING_SIZE` parameter value to be set. The only valid value is:`'STANDARD`’ .

Usage Notes

If the return list is empty, then there are no violations and the MAX_STRING_SIZE update can be performed.

Example

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

TYPE   OBJECT_OWNER OBJECT_NAME COLUMN_NAME    REASON
------ ------------ ----------- -------------- ---------------------------------------------
COLUMN ADMIN        SALES       CUST_NOTES     Physical column exceeds STANDARD length limit
1 rows selected.

MODIFY_MAX_STRING_SIZE Procedure

This procedure updates the value of the MAX_STRING_SIZE parameter to a given value.

Syntax

DBMS_MAX_STRING_SIZE.MODIFY_MAX_STRING_SIZE(
          new_value    IN VARCHAR2);

Where: user_account is the user account name (schema name).

Parameters

Parameter	Description
`new_value`	Specifies the new `MAX_STRING_SIZE` parameter value to be set. The only valid value is: `'STANDARD'`.

Usage Notes

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.
The error ORA-20000 is raised if any object exists that would prevent MAX_STRING_SIZE from being updated.
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.

Example

SELECT NAME, VALUE FROM V$PARAMETER WHERE NAME = 'max_string_size';

NAME                 VALUE
max_string_size      EXTENDED


   BEGIN
     DBMS_MAX_STRING_SIZE.MODIFY_MAX_STRING_SIZE('STANDARD');
   END;
/


PL/SQL procedure successfully completed.

SELECT NAME, VALUE FROM V$PARAMETER WHERE NAME = 'max_string_size';

NAME                 VALUE
max_string_size      STANDARD