<div id="docs-body">
 <div class="section">
  <div class="titlepage">
   <div>
    <div>
     <h3 class="title">
      <a name="alter-tablespace">
      </a>
      15.1.10 ALTER TABLESPACE Statement
     </h3>
    </div>
   </div>
  </div>
  <a class="indexterm" name="idm46045188454400">
  </a>
  <a class="indexterm" name="idm46045188452944">
  </a>
  <a class="indexterm" name="idm46045188451456">
  </a>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa67950580"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token keyword">ALTER</span> <span class="token punctuation">[</span><span class="token keyword">UNDO</span><span class="token punctuation">]</span> <span class="token keyword">TABLESPACE</span> <em class="replaceable">tablespace_name</em>
  <span class="token keyword"></span><em><span class="token keyword">NDB</span> <span class="token keyword">only</span>:</em>
    {<span class="token keyword">ADD</span> <span class="token operator">|</span> <span class="token keyword">DROP</span>} <span class="token keyword">DATAFILE</span> <span class="token string">'<em class="replaceable">file_name</em>'</span>
    <span class="token punctuation">[</span><span class="token keyword">INITIAL_SIZE</span> <span class="token punctuation">[</span><span class="token operator">=</span><span class="token punctuation">]</span> size<span class="token punctuation">]</span>
    <span class="token punctuation">[</span><span class="token keyword">WAIT</span><span class="token punctuation">]</span>
  <em>InnoDB <span class="token operator">and</span> <span class="token keyword">NDB</span>:</em>
    <span class="token punctuation">[</span><span class="token keyword">RENAME</span> <span class="token keyword">TO</span> <em class="replaceable">tablespace_name</em><span class="token punctuation">]</span>
  <em>InnoDB <span class="token keyword">only</span>:</em>
    <span class="token punctuation">[</span><span class="token keyword">AUTOEXTEND_SIZE</span> <span class="token punctuation">[</span><span class="token operator">=</span><span class="token punctuation">]</span> <span class="token string">'<em class="replaceable">value</em>'</span><span class="token punctuation">]</span>
    <span class="token punctuation">[</span><span class="token keyword">SET</span> {<span class="token keyword">ACTIVE</span> <span class="token operator">|</span> <span class="token keyword">INACTIVE</span>}<span class="token punctuation">]</span>
    <span class="token punctuation">[</span><span class="token keyword">ENCRYPTION</span> <span class="token punctuation">[</span><span class="token operator">=</span><span class="token punctuation">]</span> {<span class="token string">'Y'</span> <span class="token operator">|</span> <span class="token string">'N'</span>}<span class="token punctuation">]</span>
  <em>InnoDB <span class="token operator">and</span> <span class="token keyword">NDB</span>:</em>
    <span class="token punctuation">[</span><span class="token keyword">ENGINE</span> <span class="token punctuation">[</span><span class="token operator">=</span><span class="token punctuation">]</span> <em class="replaceable">engine_name</em><span class="token punctuation">]</span>
  <em>Reserved <span class="token keyword">for</span> future <span class="token keyword">use</span>:</em>
    <span class="token punctuation">[</span><span class="token keyword">ENGINE_ATTRIBUTE</span> <span class="token punctuation">[</span><span class="token operator">=</span><span class="token punctuation">]</span> <span class="token string">'<em class="replaceable">string</em>'</span><span class="token punctuation">]</span></code></pre>
  </div>
  <p>
   This statement is used with
   <code class="literal">
    NDB
   </code>
   and
   <code class="literal">
    InnoDB
   </code>
   tablespaces. It can be used to add a new
      data file to, or to drop a data file from an
   <code class="literal">
    NDB
   </code>
   tablespace. It can also be used to rename
      an NDB Cluster Disk Data tablespace, rename an
   <code class="literal">
    InnoDB
   </code>
   general tablespace, encrypt an
   <code class="literal">
    InnoDB
   </code>
   general tablespace, or mark an
   <code class="literal">
    InnoDB
   </code>
   undo tablespace as active or inactive.
  </p>
  <p>
   The
   <code class="literal">
    UNDO
   </code>
   keyword is used with the
   <code class="literal">
    SET
      {ACTIVE | INACTIVE}
   </code>
   clause to mark an
   <code class="literal">
    InnoDB
   </code>
   undo tablespace as active or inactive.
      For more information, see
   <a class="xref" href="innodb-undo-tablespaces.html" title="17.6.3.4 Undo Tablespaces">
    Section 17.6.3.4, “Undo Tablespaces”
   </a>
   .
  </p>
  <p>
   The
   <code class="literal">
    ADD DATAFILE
   </code>
   variant enables you to specify
      an initial size for an
   <code class="literal">
    NDB
   </code>
   Disk Data tablespace
      using an
   <code class="literal">
    INITIAL_SIZE
   </code>
   clause, where
   <em class="replaceable">
    <code>
     size
    </code>
   </em>
   is measured in bytes; the default
      value is 134217728 (128 MB). You may optionally follow
   <em class="replaceable">
    <code>
     size
    </code>
   </em>
   with a one-letter abbreviation for
      an order of magnitude, similar to those used in
   <code class="filename">
    my.cnf
   </code>
   . Generally, this is one of the letters
   <code class="literal">
    M
   </code>
   (megabytes) or
   <code class="literal">
    G
   </code>
   (gigabytes).
  </p>
  <p>
   On 32-bit systems, the maximum supported value for
   <code class="literal">
    INITIAL_SIZE
   </code>
   is 4294967296 (4 GB). (Bug #29186)
  </p>
  <p>
   <code class="literal">
    INITIAL_SIZE
   </code>
   is rounded, explicitly, as for
   <a class="link" href="create-tablespace.html" title="15.1.21 CREATE TABLESPACE Statement">
    <code class="literal">
     CREATE TABLESPACE
    </code>
   </a>
   .
  </p>
  <p>
   Once a data file has been created, its size cannot be changed;
      however, you can add more data files to an
   <code class="literal">
    NDB
   </code>
   tablespace using additional
   <code class="literal">
    ALTER TABLESPACE ... ADD
      DATAFILE
   </code>
   statements.
  </p>
  <p>
   When
   <code class="literal">
    ALTER TABLESPACE ... ADD DATAFILE
   </code>
   is used
      with
   <code class="literal">
    ENGINE = NDB
   </code>
   , a data file is created on
      each Cluster data node, but only one row is generated in the
      Information Schema
   <a class="link" href="information-schema-files-table.html" title="28.3.15 The INFORMATION_SCHEMA FILES Table">
    <code class="literal">
     FILES
    </code>
   </a>
   table. See
      the description of this table, as well as
   <a class="xref" href="mysql-cluster-disk-data-objects.html" title="25.6.11.1 NDB Cluster Disk Data Objects">
    Section 25.6.11.1, “NDB Cluster Disk Data Objects”
   </a>
   , for more
      information.
   <code class="literal">
    ADD DATAFILE
   </code>
   is not supported with
   <code class="literal">
    InnoDB
   </code>
   tablespaces.
  </p>
  <p>
   Using
   <code class="literal">
    DROP DATAFILE
   </code>
   with
   <a class="link" href="alter-tablespace.html" title="15.1.10 ALTER TABLESPACE Statement">
    <code class="literal">
     ALTER TABLESPACE
    </code>
   </a>
   drops the data
      file '
   <em class="replaceable">
    <code>
     file_name
    </code>
   </em>
   ' from an
   <code class="literal">
    NDB
   </code>
   tablespace. You cannot drop a data file
      from a tablespace which is in use by any table; in other words,
      the data file must be empty (no extents used). See
   <a class="xref" href="mysql-cluster-disk-data-objects.html" title="25.6.11.1 NDB Cluster Disk Data Objects">
    Section 25.6.11.1, “NDB Cluster Disk Data Objects”
   </a>
   . In addition,
      any data file to be dropped must previously have been added to the
      tablespace with
   <a class="link" href="create-tablespace.html" title="15.1.21 CREATE TABLESPACE Statement">
    <code class="literal">
     CREATE TABLESPACE
    </code>
   </a>
   or
   <a class="link" href="alter-tablespace.html" title="15.1.10 ALTER TABLESPACE Statement">
    <code class="literal">
     ALTER TABLESPACE
    </code>
   </a>
   .
   <code class="literal">
    DROP
      DATAFILE
   </code>
   is not supported with
   <code class="literal">
    InnoDB
   </code>
   tablespaces.
  </p>
  <p>
   <code class="literal">
    WAIT
   </code>
   is parsed but otherwise ignored. It is
      intended for future expansion.
  </p>
  <p>
   The
   <code class="literal">
    ENGINE
   </code>
   clause, which specifies the storage
      engine used by the tablespace, is deprecated, since the tablespace
      storage engine is known by the data dictionary, making the
   <code class="literal">
    ENGINE
   </code>
   clause obsolete. In MySQL
      8.4, it is supported in the following two cases only:
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <div class="copytoclipboard-wrapper">
      <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa46514406"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token keyword">ALTER</span> <span class="token keyword">TABLESPACE</span> <em class="replaceable">tablespace_name</em> <span class="token keyword">ADD</span> <span class="token keyword">DATAFILE</span> <span class="token string">'<em class="replaceable">file_name</em>'</span>
    <span class="token keyword">ENGINE</span><span class="token operator">=</span>{<span class="token keyword">NDB</span><span class="token operator">|</span><span class="token keyword">NDBCLUSTER</span>}</code></pre>
     </div>
    </li>
    <li class="listitem">
     <div class="copytoclipboard-wrapper">
      <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa30279608"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token keyword">ALTER</span> <span class="token keyword">UNDO</span> <span class="token keyword">TABLESPACE</span> <em class="replaceable">tablespace_name</em> <span class="token keyword">SET</span> {<span class="token keyword">ACTIVE</span><span class="token operator">|</span><span class="token keyword">INACTIVE</span>}
    <span class="token keyword">ENGINE</span><span class="token operator">=</span>INNODB</code></pre>
     </div>
    </li>
   </ul>
  </div>
  <p>
   You should expect the eventual removal of
   <code class="literal">
    ENGINE
   </code>
   from these statements as well, in a
      future version of MySQL.
  </p>
  <p>
   <code class="literal">
    RENAME TO
   </code>
   operations are implicitly performed
      in autocommit mode, regardless of the value of
   <a class="link" href="server-system-variables.html#sysvar_autocommit">
    <code class="literal">
     autocommit
    </code>
   </a>
   .
  </p>
  <p>
   A
   <code class="literal">
    RENAME TO
   </code>
   operation cannot be performed while
   <a class="link" href="lock-tables.html" title="15.3.6 LOCK TABLES and UNLOCK TABLES Statements">
    <code class="literal">
     LOCK TABLES
    </code>
   </a>
   or
   <a class="link" href="flush.html" title="15.7.8.3 FLUSH Statement">
    <code class="literal">
     FLUSH TABLES WITH READ
      LOCK
    </code>
   </a>
   is in effect for tables that reside in the
      tablespace.
  </p>
  <p>
   Exclusive
   <a class="link" href="glossary.html#glos_metadata_lock" title="metadata lock">
    metadata locks
   </a>
   are taken on tables that reside in a general tablespace while the
      tablespace is renamed, which prevents concurrent DDL. Concurrent
      DML is supported.
  </p>
  <p>
   The
   <a class="link" href="privileges-provided.html#priv_create-tablespace">
    <code class="literal">
     CREATE TABLESPACE
    </code>
   </a>
   privilege is
      required to rename an
   <code class="literal">
    InnoDB
   </code>
   general
      tablespace.
  </p>
  <p>
   The
   <code class="literal">
    AUTOEXTEND_SIZE
   </code>
   option defines the amount
      by which
   <code class="literal">
    InnoDB
   </code>
   extends the size of a
      tablespace when it becomes full. The setting must be a multiple of
      4MB. The default setting is 0, which causes the tablespace to be
      extended according to the implicit default behavior. For more
      information, see
   <a class="xref" href="innodb-tablespace-autoextend-size.html" title="17.6.3.9 Tablespace AUTOEXTEND_SIZE Configuration">
    Section 17.6.3.9, “Tablespace AUTOEXTEND_SIZE Configuration”
   </a>
   .
  </p>
  <p>
   The
   <code class="literal">
    ENCRYPTION
   </code>
   clause enables or disables
      page-level data encryption for an
   <code class="literal">
    InnoDB
   </code>
   general tablespace or the
   <code class="literal">
    mysql
   </code>
   system
      tablespace.
  </p>
  <p>
   A keyring plugin must be installed and configured before
      encryption can be enabled.
  </p>
  <p>
   If the
   <a class="link" href="server-system-variables.html#sysvar_table_encryption_privilege_check">
    <code class="literal">
     table_encryption_privilege_check
    </code>
   </a>
   variable is enabled, the
   <a class="link" href="privileges-provided.html#priv_table-encryption-admin">
    <code class="literal">
     TABLE_ENCRYPTION_ADMIN
    </code>
   </a>
   privilege is
      required to alter a general tablespace with an
   <code class="literal">
    ENCRYPTION
   </code>
   clause setting that differs from the
   <a class="link" href="server-system-variables.html#sysvar_default_table_encryption">
    <code class="literal">
     default_table_encryption
    </code>
   </a>
   setting.
  </p>
  <p>
   Enabling encryption for a general tablespace fails if any table in
      the tablespace belongs to a schema defined with
   <code class="literal">
    DEFAULT
      ENCRYPTION='N'
   </code>
   . Similarly, disabling encryption fails if
      any table in the general tablespace belongs to a schema defined
      with
   <code class="literal">
    DEFAULT ENCRYPTION='Y'
   </code>
   .
  </p>
  <p>
   If an
   <a class="link" href="alter-tablespace.html" title="15.1.10 ALTER TABLESPACE Statement">
    <code class="literal">
     ALTER TABLESPACE
    </code>
   </a>
   statement
      executed on a general tablespace does not include an
   <code class="literal">
    ENCRYPTION
   </code>
   clause, the tablespace retains its
      current encryption status, regardless of the
   <a class="link" href="server-system-variables.html#sysvar_default_table_encryption">
    <code class="literal">
     default_table_encryption
    </code>
   </a>
   setting.
  </p>
  <p>
   When a general tablespace or the
   <code class="literal">
    mysql
   </code>
   system
      tablespace is encrypted, all tables residing in the tablespace are
      encrypted. Likewise, a table created in an encrypted tablespace is
      encrypted.
  </p>
  <p>
   The
   <code class="literal">
    INPLACE
   </code>
   algorithm is used when altering the
   <code class="literal">
    ENCRYPTION
   </code>
   attribute of a general tablespace or
      the
   <code class="literal">
    mysql
   </code>
   system tablespace. The
   <code class="literal">
    INPLACE
   </code>
   algorithm permits concurrent DML on
      tables that reside in the tablespace. Concurrent DDL is blocked.
  </p>
  <p>
   For more information, see
   <a class="xref" href="innodb-data-encryption.html" title="17.13 InnoDB Data-at-Rest Encryption">
    Section 17.13, “InnoDB Data-at-Rest Encryption”
   </a>
   .
  </p>
  <p>
   The
   <code class="literal">
    ENGINE_ATTRIBUTE
   </code>
   option is used to specify
      tablespace attributes for primary storage engines. The option is
      reserved for future use.
  </p>
  <p>
   The value assigned to this option is a string literal containing a
      valid JSON document or an empty string (''). Invalid JSON is
      rejected.
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa91544638"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token keyword">ALTER</span> <span class="token keyword">TABLESPACE</span> ts1 <span class="token keyword">ENGINE_ATTRIBUTE</span><span class="token operator">=</span><span class="token string">'{"<em class="replaceable">key</em>":"<em class="replaceable">value</em>"}'</span><span class="token punctuation">;</span></code></pre>
  </div>
  <p>
   <code class="literal">
    ENGINE_ATTRIBUTE
   </code>
   values can be repeated without
      error. In this case, the last specified value is used.
  </p>
  <p>
   <code class="literal">
    ENGINE_ATTRIBUTE
   </code>
   values are not checked by the
      server, nor are they cleared when the table's storage engine
      is changed.
  </p>
  <p>
   It is not permitted to alter an individual element of a JSON
      attribute value. You can only add or replace an attribute.
  </p>
 </div>
 <br/>
</div>
