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

Question

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.

Answer 1

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.