[SOLVED] How to set max_string_size='EXTENDED' in Oracle database?

How to set max_string_size='EXTENDED' in Oracle database?

What is the appropriate syntax to set max_string_size = 'EXTENDED' in v$parameter?

I tried:

ALTER SYSTEM set value='EXTENDED',display_value='EXTENDED' 
WHERE NAME='max_string_size';

But I get:

ORA-02065: illegal option for ALTER SYSTEM

Thanks.

UPDATE: After this change, we get errors on Concurrent Request form when we go to View Details. FRM-41072: Cannot create Group job_notify and FRM-41076: Error populating Group. Anyone else seen this and have resolved? Per Metalink ticket the change is irreversible, the only way to fix is to restore from backup.

Solution

You are mixing a SQL query syntax into the ALTER SYSTEM command; you need to use this format:

alter system set max_string_size='EXTENDED';

See https://docs.oracle.com/database/121/SQLRF/statements_2017.htm#i2282157

Adding note from William's comment: This is a fundamental change to the database; so you need to test this thoroughly. A full backup before changing this would be important. And this is why you cannot change the setting to be effective immediately. There may be PL/SQL code that may need to be reviewed, such as triggers, etc.