Oracle 参数 MAX_STRING_SIZE 官方解释，作用，如何配置最优化建议

网络编程 Oracle 参数 MAX_STRING_SIZE 官方解释，作用，如何配置最优化建议 10-19

本站中文解释

MAX_STRING_SIZE用于指定VARCHAR2，NVARCHAR2及RAW数据类型单个值(single value)允许的最大字符长度;默认值为VARCHAR2：4000， NVARCHAR2：2000；RAW：2000.

可设置为EXTENDED，那么VARCHAR2，NVARCHAR2及RAW将可以储存最大长度为32767字节的值，只有在升级到12.1版本以上才可以设置MAX_STRING_SIZE为EXTENDED。

正确设置：

使用ALTER SYSTEM设置语句对MAX_STRING_SIZE参数进行设置：

ALTER SYSTEM SET MAX_STRING_SIZE = ‘EXTENDED’ SCOPE = BOTH;

运行以上语句，完成参数设置，然后需要重启数据库实例便可以生效：

SHUTDOWN IMMEDIATE;
STARTUP;

官方英文解释

window.name=’MAX_STRING_SIZE’
function footdisplay(footnum,footnote) {
var msg = window.open(‘about:blank’, ‘NewWindow’ + footnum,
‘directories=no,height=100,location=no,menubar=no,resizable=yes,’ +
‘scrollbars=yes,status=no,toolbar=no,width=598’);
msg.document.open(‘text/html’);
msg.document.write(”);
msg.document.write(‘‘);</p><p> msg.document.write(‘Footnote ‘ + footnum);<br /> msg.document.write(”);<br /> msg.document.write(”);<br /> msg.document.write(‘ <![CDATA[ ');<br /> msg.document.write('h1 {text-align: center; font-size: 14pt;}');<br /> msg.document.write('fieldset {border: none;}');<br /> msg.document.write('form {text-align: center;}');<br /> msg.document.write(' ]]\u003e ‘);<br /> msg.document.write(‘</p><h1>Footnote ‘ + footnum + ‘</p><p>‘);<br /> msg.document.write(footnote);<br /> msg.document.write(‘</p><fieldset>‘);<br /> msg.document.write(”);<br /> msg.document.write(”);<br /> msg.document.close();<br /> setTimeout(function() {<br /> var height = msg.document.getElementById(‘footnote’).offsetHeight;<br /> msg.resizeTo(598, height + 100);<br /> }<br /> , 100);<br /> msg.focus();<br />}</p><p>The script content on this page is for navigation purposes only and does not alter the content in any way.</p><p><code class="codeph">MAX_STRING_SIZE</code> controls the maximum size of <code class="codeph">VARCHAR2</code>, <code class="codeph">NVARCHAR2</code>, and <code class="codeph">RAW</code> data types in SQL. </p><p> </p><table cellpadding="4" cellspacing="0" class="Formal" title="" summary="This table describes the properties for this initialization parameter" width="100%" border="1" rules="rows"><thead><tr align="left" valign="top"><th align="left" valign="bottom" width="25%" id="d139386e132">Property</th><th align="left" valign="bottom" width="75%" id="d139386e135">Description</th></tr></thead><tbody><tr align="left" valign="top"><td align="left" valign="top" width="25%" id="d139386e140" headers="d139386e132 "><p><span class="bold">Parameter type</span></p></td><td align="left" valign="top" width="75%" headers="d139386e140 d139386e135 "><p>String</p></td></tr><tr align="left" valign="top"><td align="left" valign="top" width="25%" id="d139386e148" headers="d139386e132 "><p><span class="bold">Syntax</span></p></td><td align="left" valign="top" width="75%" headers="d139386e148 d139386e135 "><p><code class="codeph">MAX_STRING_SIZE = { STANDARD | EXTENDED }</code></p></td></tr><tr align="left" valign="top"><td align="left" valign="top" width="25%" id="d139386e157" headers="d139386e132 "><p><span class="bold">Default value</span></p></td><td align="left" valign="top" width="75%" headers="d139386e157 d139386e135 "><p><code class="codeph">STANDARD</code></p></td></tr><tr align="left" valign="top"><td align="left" valign="top" width="25%" id="d139386e166" headers="d139386e132 "><p><span class="bold">Modifiable</span></p></td><td align="left" valign="top" width="75%" headers="d139386e166 d139386e135 "><p><code class="codeph">ALTER SYSTEM</code> … SID=’*’<sup>Foot 1</sup></p></td></tr><tr align="left" valign="top"><td align="left" valign="top" width="25%" id="d139386e179" headers="d139386e132 "><p><span class="bold">Modifiable in a PDB</span></p></td><td align="left" valign="top" width="75%" headers="d139386e179 d139386e135 "><p>Yes</p></td></tr><tr align="left" valign="top"><td align="left" valign="top" width="25%" id="d139386e187" headers="d139386e132 "><p><span class="bold">Basic</span></p></td><td align="left" valign="top" width="75%" headers="d139386e187 d139386e135 "><p>No</p></td></tr><tr align="left" valign="top"><td align="left" valign="top" width="25%" id="d139386e195" headers="d139386e132 "><p><span class="bold">Oracle RAC</span></p></td><td align="left" valign="top" width="75%" headers="d139386e195 d139386e135 "><p>Multiple instances must use the same value.</p></td></tr></tbody></table><p> </p><p class="tablefootnote"><sup class="tablefootnote">Footnote 1 </sup></p><p>Use ALTER SYSTEM only when the database is in UPGRADE mode, and run the utl32k.sql script afterward, as explained in this section.</p><p><code class="codeph">STANDARD</code> means that the length limits for Oracle Database releases prior to Oracle Database 12<span class="italic">c</span> apply (for example, 4000 bytes for <code class="codeph">VARCHAR</code>2 and <code class="codeph">NVARCHAR2</code>, and 2000 bytes for <code class="codeph">RAW</code>). </p><p><code class="codeph">EXTENDED</code> means that the 32767 byte limit introduced in Oracle Database 12<span class="italic">c</span> applies. </p><p>The <code class="codeph">COMPATIBLE</code> initialization parameter must be set to <code class="codeph">12.0.0.0</code> or higher to set <code class="codeph">MAX_STRING_SIZE</code> = <code class="codeph">EXTENDED</code>. </p><p>You can change the value of <code class="codeph">MAX_STRING_SIZE</code> from <code class="codeph">STANDARD</code> to <code class="codeph">EXTENDED</code>. However, you cannot change the value of <code class="codeph">MAX_STRING_SIZE</code> from <code class="codeph">EXTENDED</code> to <code class="codeph">STANDARD</code>. </p><p>By setting <code class="codeph">MAX_STRING_SIZE</code> = <code class="codeph">EXTENDED</code>, users are taking an explicit action that could introduce application incompatibility in their database. Applications that do not want to use the expanded data types can be rewritten for compatibility with either setting; for example, these applications could use explicit CASTs to fix the length of <code class="codeph">VARCHAR2</code> expressions during <code class="codeph">CREATE TABLE AS SELECT</code>. </p><p>Altering <code class="codeph">MAX_STRING_SIZE</code> will update database objects and possibly invalidate them, as follows: </p><ul style="list-style-type: disc"><li><p>Tables with virtual columns will be updated with new data type metadata for virtual columns of <code class="codeph">VARCHAR2(4000)</code>, 4000-byte <code class="codeph">NVARCHAR2</code>, or <code class="codeph">RAW(2000)</code> type. </p><ul style="list-style-type: disc"><li><p>Functional indexes will become unusable if a change to their associated virtual columns causes the index key to exceed index key length limits. Attempts to rebuild such indexes will fail with <code class="codeph">ORA-01450: maximum key length exceeded</code>. </p></li></ul></li><li><p>Views will be invalidated if they contain <code class="codeph">VARCHAR2(4000)</code>, 4000-byte <code class="codeph">NVARCHAR2</code>, or <code class="codeph">RAW(2000)</code> typed expression columns. </p></li><li><p>Materialized views will be updated with new metadata <code class="codeph">VARCHAR2(4000)</code>, 4000-byte <code class="codeph">NVARCHAR2</code>, and <code class="codeph">RAW(2000)</code> typed expression columns </p></li></ul><p> </p><p class="subhead1" id="GUID-D424D23B-0933-425F-BC69-9C0E6724693C__INCREASINGTHEMAXIMUMSIZEOFVARCHAR2N-D9FCEFCD" role="heading">Increasing the Maximum Size of VARCHAR2, NVARCHAR2, and RAW Columns in a CDB</p><p>To increase the maximum size of <code class="codeph">VARCHAR2</code>, <code class="codeph">NVARCHAR2</code>, and <code class="codeph">RAW</code> columns in a CDB and in all the PDBs in the CDB: </p><ol><li><p>Connect to the CDB AS SYSDBA.</p></li><li><p>In the root, change the setting of <code class="codeph">MAX_STRING_SIZE</code> to <code class="codeph">EXTENDED</code>: </p><pre class="pre codeblock precomplete hljs"><button class="copy-btn copy-btn-enabled" aria-label="Copy the following example to clipboard"><span class="copy-btn-text">Copy</span></button><code>ALTER SESSION SET CONTAINER=CDB$ROOT;ALTER SYSTEM SET max_string_size=extended SCOPE=SPFILE;</code></pre><p class="notep1">Note:</p><p>The root continues to use <code class="codeph">STANDARD</code> semantics even after <code class="codeph">MAX_STRING_SIZE</code> is set to <code class="codeph">EXTENDED</code>. The reason for setting <code class="codeph">MAX_STRING_SIZE</code> to <code class="codeph">EXTENDED</code> in the root is so all the PDBs in the CDB can inherit the <code class="codeph">EXTENDED</code> setting from the root. </p></p></li><li><p>Shut down the CDB.</p></li><li><p>Restart the CDB in UPGRADE mode.</p><pre class="pre codeblock precomplete hljs"><button class="copy-btn copy-btn-enabled" aria-label="Copy the following example to clipboard"><span class="copy-btn-text">Copy</span></button><code>startup upgrade;</code></pre></li><li><p>Use the <code class="codeph">catcon.pl</code> script to run the <code class="codeph">rdbms/admin/utl32k.sql</code> script in the root and in all the PDBs in the CDB to increase the maximum size of the <code class="codeph">VARCHAR2</code>, <code class="codeph">NVARCHAR2</code>, and <code class="codeph">RAW</code> columns. The –force_pdb_mode ‘UPGRADE’ option is used to ensure that all PDBs, including application root clones, are opened in migrate mode. Enter the SYS password when prompted: </p><pre class="pre codeblock precomplete hljs"><button class="copy-btn copy-btn-enabled" aria-label="Copy the following example to clipboard"><span class="copy-btn-text">Copy</span></button><code>$ cd $ORACLE_HOME/rdbms/admin$ mkdir /scratch/mydir/utl32k_cdb_pdbs_output$ $ORACLE_HOME/perl/bin/perl $ORACLE_HOME/rdbms/admin/catcon.pl -u SYS --force_pdb_mode 'UPGRADE' -d $ORACLE_HOME/rdbms/admin -l '/scratch/mydir/utl32k_cdb_pdbs_output' -b utl32k_cdb_pdbs_output utl32k.sqlcatcon: ALL catcon-related output will be written to [/scratch/mydir/utl32k_cdb_pdbs_output/utl32k_cdb_pdbs_output_catcon_23172.lst]catcon: See [/scratch/mydir/utl32k_cdb_pdbs_output/utl32k_cdb_pdbs_output*.log] files for output generated by scriptscatcon: See [/scratch/mydir/utl32k_cdb_pdbs_output/utl32k_cdb_pdbs_output_*.lst] files for spool files, if anyEnter Password: catcon.pl: completed successfully$</code></pre><p class="notep1">Note:</p><p>The <code class="codeph">utl32k.sql</code> script increases the maximum size of the <code class="codeph">VARCHAR2</code>, <code class="codeph">NVARCHAR2</code>, and <code class="codeph">RAW</code> columns for the views where this is required. The script does not increase the maximum size of the <code class="codeph">VARCHAR2</code>, <code class="codeph">NVARCHAR2</code>, and <code class="codeph">RAW</code> columns in some views because of the way the SQL for those views is written. </p></p></li><li><p>Connect to the CDB AS SYSDBA and shut down the database.</p></li><li><p>Restart the CDB in NORMAL mode.</p><pre class="pre codeblock precomplete hljs"><button class="copy-btn copy-btn-enabled" aria-label="Copy the following example to clipboard"><span class="copy-btn-text">Copy</span></button><code>startup;</code></pre></li><li><p>Use the <code class="codeph">catcon.pl</code> script to run the <code class="codeph">rdbms/admin/utlrp.sql</code> script to recompile invalid objects in the root and in all the PDBs in the CDB. The –force_pdb_mode ‘READ WRITE’ option is used to ensure that all the PDBs (including application root clones) are opened in read write mode. Enter the SYS password when prompted: </p><pre class="pre codeblock precomplete hljs"><button class="copy-btn copy-btn-enabled" aria-label="Copy the following example to clipboard"><span class="copy-btn-text">Copy</span></button><code>$ cd $ORACLE_HOME/rdbms/admin$ mkdir /scratch/mydir/utlrp_cdb_pdbs_output$ $ORACLE_HOME/perl/bin/perl $ORACLE_HOME/rdbms/admin/catcon.pl -u SYS --force_pdb_mode 'READ WRITE' -d $ORACLE_HOME/rdbms/admin -l '/scratch/mydir/utlrp_cdb_pdbs_output' -b utlrp_cdb_pdbs_output utlrp.sqlcatcon: ALL catcon-related output will be written to [/scratch/mydir/utlrp_cdb_pdbs_output/utlrp_cdb_pdbs_output_catcon_24271.lst]catcon: See [/scratch/mydir/utlrp_cdb_pdbs_output/utlrp_cdb_pdbs_output*.log] files for output generated by scriptscatcon: See [/scratch/mydir/utlrp_cdb_pdbs_output/utlrp_cdb_pdbs_output_*.lst] files for spool files, if anyEnter Password: catcon.pl: completed successfully$</code></pre></li></ol><p class="notep1">See Also:</p><p><span><cite>Oracle Multitenant<br /> Administrator’s Guide</cite></span> for information about using the <code class="codeph">catcon.pl</code> script to run Oracle-supplied scripts in a CDB and PDBs. </p></p></p><p> </p><p class="subhead1" id="GUID-D424D23B-0933-425F-BC69-9C0E6724693C__GUID-5A3E3AA3-554C-4C20-B08F-88EBF372365D" role="heading">Increasing the Maximum Size of VARCHAR2, NVARCHAR2, and RAW Columns in a PDB</p></p><p> </p><p>To increase the maximum size of <code class="codeph">VARCHAR2</code>, <code class="codeph">NVARCHAR2</code>, and <code class="codeph">RAW</code> columns in a PDB: </p><ol><li><p>Shut down the PDB.</p></li><li><p>Reopen the PDB in migrate mode.</p><p class="notep1">Note:</p><p>The following SQL statement can be used to reopen a PDB in migrate mode when the current container is the PDB:</p><p><code class="codeph">ALTER PLUGGABLE DATABASE</code> <span class="italic">pdb-name</span> <code class="codeph">OPEN UPGRADE;</code></p></p></li><li><p>Change the setting of <code class="codeph">MAX_STRING_SIZE</code> in the PDB to <code class="codeph">EXTENDED</code>. </p></li><li><p>Run the <code class="codeph">rdbms/admin/utl32k.sql</code> script in the PDB. You must be connected <code class="codeph">AS SYSDBA</code> to run the <code class="codeph">utl32k.sql</code> script. </p></li><li><p>Reopen the PDB in <code class="codeph">NORMAL</code> mode. </p><p class="notep1">Note:</p><p>The <code class="codeph">utl32k.sql</code> script increases the maximum size of the <code class="codeph">VARCHAR2</code>, <code class="codeph">NVARCHAR2</code>, and <code class="codeph">RAW</code> columns for the views where this is required. The script does not increase the maximum size of the <code class="codeph">VARCHAR2</code>, <code class="codeph">NVARCHAR2</code>, and <code class="codeph">RAW</code> columns in some views because of the way the SQL for those views is written. </p></p></li><li><p>Run the <code class="codeph">rdbms/admin/utlrp.sql</code> script in the PDB to recompile invalid objects. You must be connected <code class="codeph">AS SYSDBA</code> to run the script. </p></li></ol><p class="notep1">See Also:</p><p><span><cite>Oracle Multitenant<br /> Administrator’s Guide</cite></span> for more information about modifying the open mode of PDBs. </p></p></p><p> </p><p class="subhead1" id="GUID-D424D23B-0933-425F-BC69-9C0E6724693C__GUID-4948B680-0FC7-4DCB-BFFF-EE8098B87332" role="heading">Increasing the Maximum Size of VARCHAR2, NVARCHAR2, and RAW Columns in an Oracle RAC Database</p></p><p> </p><p>To increase the maximum size of <code class="codeph">VARCHAR2</code>, <code class="codeph">NVARCHAR2</code>, and <code class="codeph">RAW</code> columns in an Oracle RAC database: </p><ol><li><p>Shut down all of the Oracle RAC database instances, except one.</p></li><li><p>Restart the Oracle RAC database instance in <code class="codeph">UPGRADE</code> mode. </p></li><li><p>Change the setting of <code class="codeph">MAX_STRING_SIZE</code> to <code class="codeph">EXTENDED</code>. </p></li><li><p>Run the <code class="codeph">rdbms/admin/utl32k.sql</code> script in the Oracle RAC database instance. You must be connected <code class="codeph">AS SYSDBA</code> to run the script. </p></li><li><p>Restart all Oracle RAC database instances in <code class="codeph">NORMAL</code> mode. </p><p class="notep1">Note:</p><p>The <code class="codeph">utl32k.sql</code> script increases the maximum size of the <code class="codeph">VARCHAR2</code>, <code class="codeph">NVARCHAR2</code>, and <code class="codeph">RAW</code> columns for the views where this is required. The script does not increase the maximum size of the <code class="codeph">VARCHAR2</code>, <code class="codeph">NVARCHAR2</code>, and <code class="codeph">RAW</code> columns in some views because of the way the SQL for those views is written. </p></p></li><li><p>Run the <code class="codeph">rdbms/admin/utlrp.sql</code> script to recompile invalid objects. You must be connected <code class="codeph">AS SYSDBA</code> to run the script. </p></li></ol><p> </p><p class="subhead1" id="GUID-D424D23B-0933-425F-BC69-9C0E6724693C__GUID-22C170F0-AAC8-48A4-BA1D-10F24BBB7234" role="heading">Increasing the Maximum Size of VARCHAR2, NVARCHAR2, and RAW Columns in an Oracle Data Guard Logical Standby Database</p></p><p> </p><p>To increase the maximum size of <code class="codeph">VARCHAR2</code>, <code class="codeph">NVARCHAR2</code>, and <code class="codeph">RAW</code> columns in an Oracle Data Guard logical standby database: </p><ol><li><p>Shut down the Oracle Data Guard primary database and logical standby database.</p></li><li><p>Restart the primary database and logical standby database in <code class="codeph">UPGRADE</code> mode. </p></li><li><p>Change the setting of <code class="codeph">MAX_STRING_SIZE</code> to <code class="codeph">EXTENDED</code> on the primary database and logical standby database. </p></li><li><p>Run the <code class="codeph">rdbms/admin/utl32k.sql</code> script on both the primary database and the logical standby database. You must be connected <code class="codeph">AS SYSDBA</code> to run the script. </p></li><li><p>Restart the primary database and logical standby database in <code class="codeph">NORMAL</code> mode. </p><p class="notep1">Note:</p><p>The <code class="codeph">utl32k.sql</code> script increases the maximum size of the <code class="codeph">VARCHAR2</code>, <code class="codeph">NVARCHAR2</code>, and <code class="codeph">RAW</code> columns for the views where this is required. The script does not increase the maximum size of the <code class="codeph">VARCHAR2</code>, <code class="codeph">NVARCHAR2</code>, and <code class="codeph">RAW</code> columns in some views because of the way the SQL for those views is written. </p></p></li><li><p>Run the <code class="codeph">rdbms/admin/utlrp.sql</code> script on the primary database and logical standby database to recompile invalid objects. You must be connected <code class="codeph">AS SYSDBA</code> to run the script. </p></li><li><p>Restart SQL Apply.</p></li></ol><p class="notep1">See Also:</p><p><span><cite>Oracle Database<br /> Globalization Support Guide</cite></span> for more information about the <code class="codeph">MAX_STRING_SIZE</code> parameter </p></p></p><p> <hr> <p class="source">编辑：<a href="http://www.wxasp.cn/"><span>编程语言</span></a></p> <p class="original-tit mt30"> 标签：<span>语句,英文,便可,才可以,升级到</span> </p> <div class="share-title"> <span class="txt">分享:</span> <a href="javascript:void(0);" class="getQrcode" title="请使用微信扫此码"> <i class="icon layui-icon layui-icon-login-wechat"></i> </a> <a href="javascript:void(0);" class="getQrcode" title="请使用微博扫此码"> <i class="icon layui-icon layui-icon-login-weibo"></i> </a> <a href="javascript:void(0);" class="getQrcode" title="请使用QQ扫此码"> <i class="icon layui-icon layui-icon-login-qq"></i> </a> <button class="layui-btn Collection"> ❤<span>收藏</span> </button> </div> </div> </div> <div class="layui-col-xs12 layui-col-sm12 layui-col-md12"> <div class="popular-info popular-info-tog"> <div class="layui-card">  <div class="layui-card-body"> <ul class="list-box"> <li class="list"><a href="http://www.wxasp.cn/news/01163998627606171648.html">Oracle数据库中增加字段的方法（oracle中标增加字段）</a></li> <li class="list"><a href="http://www.wxasp.cn/news/01163998614100512768.html">Oracle9i安装遇到困难求救（oracle92安装出错）</a></li> <li class="list"><a href="http://www.wxasp.cn/news/01163998600032817152.html">谁能给出Oracle的答案（oracle全是问号）</a></li> <li class="list"><a href="http://www.wxasp.cn/news/01163998581129089024.html">最长不超过30位Oracle表名长度限制（oracle中的表名长度）</a></li> <li class="list"><a href="http://www.wxasp.cn/news/01163998567304663040.html">MySQL表同步之跨库之路（mysql表同步不同库）</a></li> <li class="list"><a href="http://www.wxasp.cn/news/01163998554163908608.html">Oracle 11 ASM解决数据存储需求的高效管理方案（oracle11 asm）</a></li> <li class="list"><a href="http://www.wxasp.cn/news/01163998538200387584.html">Oracle表空间管理：实现最佳数据库性能（oracle表和表空间）</a></li> <li class="list"><a href="http://www.wxasp.cn/news/01163998525516812288.html">Linux下查看Oracle数据库状态指南（linux 查看oracle）</a></li> <li class="list"><a href="http://www.wxasp.cn/news/01163998510262128640.html">Oracle中的集合之间的比较（oracle中的两个集合）</a></li> <li class="list"><a href="http://www.wxasp.cn/news/01163998500355182592.html">Oracle中编译出的魔法单词（oracle中编译的单词）</a></li> </ul> </div> </div> </div> </div> </div> </div> </div> </div>  <div class="micronews-footer-wrap"> <div class="micronews-footer w1000"> <div class="ft-nav"> </div> <div class="Copyright"> <span>© 祺平科技 • 版权所有</span> <span><a href="https://beian.miit.gov.cn" target="_blank">粤ICP备15012827号</a></span> <span> 技术支持 </span> <span><a href="https://zmcms.com" target="_blank">ZMCMS.COM</a></span> </div>  </div> </div> <script src="https://global.cnd.aidufei.com/cms/lib/layui/layui.js"></script><script src="https://global.cnd.aidufei.com/cms/lib/js/jquery.qrcode.min.js"></script> <script> layui.config({ base: 'https://global.cnd.aidufei.com/cms/web/default/js/' }).use('index',function(){ var index = layui.index,$ = layui.$; //index.EnterMessage(); //index.Page('micronews-details-test',50); var collOff = true; $('.Collection').on('click',function(){ if(collOff){ $(this).addClass('active') }else{ $(this).removeClass('active') } collOff = !collOff; layer.alert('请按 Ctrl + D 收藏此页面'); }); //index.seachBtn(); //index.onInput(); index.arrowutil(); $('.getQrcode').on('click',function(){ var title = $(this).attr('title'); getQR(window.location.href, title); }); }); proxyImage('.article'); adjustImageSize('.article'); $(window).resize(function() {adjustImageSize('.article');}); </script> <script type="text/javascript" src="//js.users.51.la/21848999.js"></script> </body> </html>