Oracle数据库管理-32KB字段PDB调整

NOTE! As per note 2142799.1,

E-Business Suite currently can only use the default value 'STANDARD' for max_string_size.
As MAX_STRING_SIZE controls the maximum size of  VARCHAR2 ,  NVARCHAR2 , and  RAW data types in SQL.
MAX_STRING_SIZE = STANDARD  means that the length limits for Oracle Database releases prior to Oracle Database 12c apply
(for example, 4000 bytes for  VARCHAR 2 and  NVARCHAR2 , and 2000 bytes for  RAW ).
MAX_STRING_SIZE = EXTENDED means that the 32767 byte limit introduced in Oracle Database 12c applies.

You can change the value of  MAX_STRING_SIZE from  STANDARD to  EXTENDED .

However, you cannot change the value of MAX_STRING_SIZE from  EXTENDED to  STANDARD .

Example of how to set  MAX_STRING_SIZE =  EXTENDED :
Steps:
Note: database vault should be disabled before performing the steps
For non-CDB:

To increase the maximum size of  VARCHAR2 ,  NVARCHAR2 , and  RAW columns in a non-CDB:
1. Shut down the database.
2. Restart the database in  UPGRADE mode.
3. Change the setting of  MAX_STRING_SIZE to  EXTENDED .
4. Run the  rdbms/admin/utl32k.sql script. You must be connected  AS SYSDBA to run the script.

For non-CDB:

SHUTDOWN IMMEDIATE;
STARTUP UPGRADE;
alter system set MAX_STRING_SIZE=EXTENDED;
@?/rdbms/admin/utl32k.sql
SHUTDOWN IMMEDIATE;
STARTUP;
Note: If using pfile, ensure that max_string_size=extended is added to pfile before a normal
startup is attempted after executing the utl32k.sql script.
For RAC system:
alter system set cluster_database = false scope = spfile;
SHUTDOWN IMMEDIATE; ===> shutdown all the instances
STARTUP UPGRADE;
alter system set MAX_STRING_SIZE=EXTENDED;
@?/rdbms/admin/utl32k.sql
alter system set cluster_database = true scope = spfile;
SHUTDOWN IMMEDIATE;
STARTUP ===========> or you can use srvctl to start the database.

For PDB:

To increase the maximum size of  VARCHAR2 ,  NVARCHAR2 , and  RAW columns in a PDB:
1. Shut down the PDB.
2. Restart the PDB in  UPGRADE mode.
3. Change the setting of  MAX_STRING_SIZE in the PDB to  EXTENDED .
4. Run the  rdbms/admin/utl32k.sql script in the PDB. You must be connected  AS SYSDBA to run the  utl32k.sql script.
5. Restart the PDB in  NORMAL mode.
1. Restart the database in  NORMAL mode.
1. If you are using a cluster you first have to disable it.
cluster_database=False
then start the DB in startup upgrade mode
2. PDB$SEED should be upgraded as well, refer to note 1610329.1

Notes:
1-The  utl32k.sql script increases the maximum size of the  VARCHAR2 ,  NVARCHAR2 , and  RAW columns for the views where this
is required. The script does not increase the maximum size of the  VARCHAR2 ,  NVARCHAR2 , and  RAW columns in some views
because of the way the SQL for those views is written.
2-The  COMPATIBLE initialization parameter must be set to  12.0.0.0 or higher to set  MAX_STRING_SIZE =  EXTENDED .
3-When the 32k varchar2 feature is enabled, then any function based indexes that rely on PL/SQL functions returning a
varchar2 will become unusable when the utl32k.sql script is run. Such indexes will subsequently have to be dropped and it
will not be possible to re-create them. Any attempt to recreate such an index will fail with ORA-1450. The reason for this is
that such functional indexes will have a "key" size of 32k and this is larger than the maximum allowed key size. Prior to
enabling the 32k varchar2 feature, such indexes would have had a key size of 4000 bytes, which is within the allowed limit
for an index key.
4-In some cases
Running the rdbms/admin/utl32k.sql script may cause the below error:
a-ORA-14415: index in partially dropped state, submit DROP INDEX
This is caused by BUG 21450985 - ORA-14415: INDEX IN PARTIALLY DROPPED STATE, SUBMIT DROP INDEXSev 1 24X7
SR
b-ORA-30556: either functional or bitmap join index is defined on the column to be modified
This is caused by Bug 20539050 - ORA-30556 ON USING /RDBMS/ADMIN/UTL32K
Both the bugs are fixed in 12.2 version.For earlier versions one off patch can be requested by creating a SR to Oracle
Support.
c-ORA-02019 WHEN RUNNING @?/RDBMS/ADMIN/UTL32K.SQL ON mview with private DB link
This is caused by BUG 19063812 - ORA-02019 WHEN RUNNING @?/RDBMS/ADMIN/UTL32K.SQL ON MV WITH PRIVATE
DB LINK
It is fixed in 12.2.
REFERENCES
NOTE:1610329.1 - Database Won't Start After Varchar2(32k) Upgrade