1

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.

alexherm
  • 1,362
  • 2
  • 18
  • 31

2 Answers2

4

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.

Mark Stewart
  • 2,046
  • 4
  • 22
  • 32
  • Thanks Mark. This looks like the right syntax, but when I run it i get: ```ORA-02097: parameter cannot be modified because specified value is invalid ORA-14694: database must in UPGRADE mode to begin MAX_STRING_SIZE migration ``` – alexherm Oct 09 '19 at 17:38
  • 1
    Looks like database will need to be shutdown in order to process this change. – alexherm Oct 09 '19 at 17:49
  • 1
    Note you can't easily undo this change, so make sure you have backups and test first in an environment you don't care too much about. – William Robertson Oct 09 '19 at 22:25
  • We were able to apply while database was shut down. After, we were able to run ```alter table table1 modify field1 varchar2(32767);```. I am curious about your experience with having to undo the change. What trouble did you run into that provoked need to undo? Is the difficultly with undoing around parsing the extended length strings or something else? How were you able to undo? – alexherm Oct 10 '19 at 14:24
  • In our database the parameter change is effective ```IMMEDIATE``` – alexherm Oct 10 '19 at 14:32
  • Hmmm interesting the change was immediate; I knew I should have queried `V$PARAMETER` column `ISSYS_MODIFIABLE` before posting -- https://docs.oracle.com/database/121/REFRN/GUID-C86F3AB0-1191-447F-8EDF-4727D8693754.htm#REFRN30176 – Mark Stewart Oct 10 '19 at 15:01
  • 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 resolved? – alexherm Oct 10 '19 at 20:39
1

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.

alexherm
  • 1,362
  • 2
  • 18
  • 31