CODEWITHSUNDEEP
sqloracle-databaseparametersoracle12coracle-ebs
alexherm
alexherm

Reputation: 1362

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.

Upvotes: 1

Views: 15151

Answers (2)

alexherm
alexherm

Reputation: 1362

For anyone else considering this change, know that the option is not compatible with EBS. It causes some odd behavior, which does not go away even after setting max_string_size back to STANDARD. 

If you use EBS, as others have advised, do not apply this change to your system.

We were not able to find a way to eradicate the problem this change caused and ended up restoring the test system from backup.

Upvotes: 1

Mark Stewart
Mark Stewart

Reputation: 2098

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.

Upvotes: 4

Related Questions