<div id="docs-body">
 <div class="section">
  <div class="titlepage">
   <div>
    <div>
     <h3 class="title">
      <a name="information-schema-innodb-tablespaces-table">
      </a>
      28.4.24 The INFORMATION_SCHEMA INNODB_TABLESPACES Table
     </h3>
    </div>
   </div>
  </div>
  <a class="indexterm" name="idm46045076461376">
  </a>
  <p>
   The
   <a class="link" href="information-schema-innodb-tablespaces-table.html" title="28.4.24 The INFORMATION_SCHEMA INNODB_TABLESPACES Table">
    <code class="literal">
     INNODB_TABLESPACES
    </code>
   </a>
   table provides
      metadata about
   <code class="literal">
    InnoDB
   </code>
   file-per-table, general,
      and undo tablespaces.
  </p>
  <p>
   For related usage information and examples, see
   <a class="xref" href="innodb-information-schema-system-tables.html" title="17.15.3 InnoDB INFORMATION_SCHEMA Schema Object Tables">
    Section 17.15.3, “InnoDB INFORMATION_SCHEMA Schema Object Tables”
   </a>
   .
  </p>
  <div class="note" style="margin-left: 0.5in; margin-right: 0.5in;">
   <div class="admon-title">
    Note
   </div>
   <p>
    The
    <code class="literal">
     INFORMATION_SCHEMA
    </code>
    <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 reports metadata for
    <code class="literal">
     InnoDB
    </code>
    tablespace types including
        file-per-table tablespaces, general tablespaces, the system
        tablespace, the global temporary tablespace, and undo
        tablespaces.
   </p>
  </div>
  <p>
   The
   <a class="link" href="information-schema-innodb-tablespaces-table.html" title="28.4.24 The INFORMATION_SCHEMA INNODB_TABLESPACES Table">
    <code class="literal">
     INNODB_TABLESPACES
    </code>
   </a>
   table has
      these columns:
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      <code class="literal">
       SPACE
      </code>
     </p>
     <p>
      The tablespace ID.
     </p>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       NAME
      </code>
     </p>
     <p>
      The schema (database) and table name.
     </p>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       FLAG
      </code>
     </p>
     <p>
      A numeric value that represents bit-level information about
          tablespace format and storage characteristics.
     </p>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       ROW_FORMAT
      </code>
     </p>
     <p>
      The tablespace row format (
      <code class="literal">
       Compact or
          Redundant
      </code>
      ,
      <code class="literal">
       Dynamic
      </code>
      or
      <code class="literal">
       Compressed
      </code>
      , or
      <code class="literal">
       Undo
      </code>
      ).
          The data in this column is interpreted from the tablespace
          flag information that resides in the data file.
     </p>
     <p>
      There is no way to determine from this flag information if the
          tablespace row format is
      <code class="literal">
       Redundant
      </code>
      or
      <code class="literal">
       Compact
      </code>
      , which is why one of the possible
      <code class="literal">
       ROW_FORMAT
      </code>
      values is
      <code class="literal">
       Compact or
          Redundant
      </code>
      .
     </p>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       PAGE_SIZE
      </code>
     </p>
     <p>
      The tablespace page size. The data in this column is
          interpreted from the tablespace flags information that resides
          in the
      <a class="link" href="glossary.html#glos_ibd_file" title=".ibd file">
       <code class="filename">
        .ibd
       </code>
       file
      </a>
      .
     </p>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       ZIP_PAGE_SIZE
      </code>
     </p>
     <p>
      The tablespace zip page size. The data in this column is
          interpreted from the tablespace flags information that resides
          in the
      <a class="link" href="glossary.html#glos_ibd_file" title=".ibd file">
       <code class="filename">
        .ibd
       </code>
       file
      </a>
      .
     </p>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       SPACE_TYPE
      </code>
     </p>
     <p>
      The type of tablespace. Possible values include
      <code class="literal">
       General
      </code>
      for general tablespaces,
      <code class="literal">
       Single
      </code>
      for file-per-table tablespaces,
      <code class="literal">
       System
      </code>
      for the system tablespace, and
      <code class="literal">
       Undo
      </code>
      for undo tablespaces.
     </p>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       FS_BLOCK_SIZE
      </code>
     </p>
     <p>
      The file system block size, which is the unit size used for
          hole punching. This column pertains to the
      <code class="literal">
       InnoDB
      </code>
      <a class="link" href="innodb-page-compression.html" title="17.9.2 InnoDB Page Compression">
       transparent page
          compression
      </a>
      feature.
     </p>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       FILE_SIZE
      </code>
     </p>
     <p>
      The apparent size of the file, which represents the maximum
          size of the file, uncompressed. This column pertains to the
      <code class="literal">
       InnoDB
      </code>
      <a class="link" href="innodb-page-compression.html" title="17.9.2 InnoDB Page Compression">
       transparent page
          compression
      </a>
      feature.
     </p>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       ALLOCATED_SIZE
      </code>
     </p>
     <p>
      The actual size of the file, which is the amount of space
          allocated on disk. This column pertains to the
      <code class="literal">
       InnoDB
      </code>
      <a class="link" href="innodb-page-compression.html" title="17.9.2 InnoDB Page Compression">
       transparent page
          compression
      </a>
      feature.
     </p>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       AUTOEXTEND_SIZE
      </code>
     </p>
     <p>
      The auto-extend size of the tablespace.
     </p>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       SERVER_VERSION
      </code>
     </p>
     <p>
      The MySQL version that created the tablespace, or the MySQL
          version into which the tablespace was imported, or the version
          of the last major MySQL version upgrade. The value is
          unchanged by a release series upgrade, such as an upgrade from
          MySQL 8.4.
      <em class="replaceable">
       <code>
        x
       </code>
      </em>
      to
          8.4.
      <em class="replaceable">
       <code>
        y
       </code>
      </em>
      . The value can be considered
          a
      <span class="quote">
       “
       <span class="quote">
        creation
       </span>
       ”
      </span>
      marker or
      <span class="quote">
       “
       <span class="quote">
        certified
       </span>
       ”
      </span>
      marker for the tablespace.
     </p>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       SPACE_VERSION
      </code>
     </p>
     <p>
      The tablespace version, used to track changes to the
          tablespace format.
     </p>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       ENCRYPTION
      </code>
     </p>
     <p>
      Whether the tablespace is encrypted.
     </p>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       STATE
      </code>
     </p>
     <p>
      The tablespace state.
     </p>
     <p>
      For file-per-table and general tablespaces, states include:
     </p>
     <div class="itemizedlist">
      <ul class="itemizedlist" style="list-style-type: circle; ">
       <li class="listitem">
        <p>
         <code class="literal">
          normal
         </code>
         : The tablespace is normal and
              active.
        </p>
       </li>
       <li class="listitem">
        <p>
         <code class="literal">
          discarded
         </code>
         : The tablespace was discarded
              by an
         <a class="link" href="alter-table.html" title="15.1.9 ALTER TABLE Statement">
          <code class="literal">
           ALTER
              TABLE ... DISCARD TABLESPACE
          </code>
         </a>
         statement.
        </p>
       </li>
       <li class="listitem">
        <p>
         <code class="literal">
          corrupted
         </code>
         : The tablespace is identified
              by
         <code class="literal">
          InnoDB
         </code>
         as corrupted.
        </p>
       </li>
      </ul>
     </div>
     <p>
      For undo tablespaces, states include:
     </p>
     <div class="itemizedlist">
      <ul class="itemizedlist" style="list-style-type: circle; ">
       <li class="listitem">
        <p>
         <code class="literal">
          active
         </code>
         : Rollback segments in the undo
              tablespace can be allocated to new transactions.
        </p>
       </li>
       <li class="listitem">
        <p>
         <code class="literal">
          inactive
         </code>
         : Rollback segments in the undo
              tablespace are no longer used by new transactions. The
              truncate process is in progress. The undo tablespace was
              either selected by the purge thread implicitly or was made
              inactive by an
         <a class="link" href="alter-tablespace.html" title="15.1.10 ALTER TABLESPACE Statement">
          <code class="literal">
           ALTER
              UNDO TABLESPACE ... SET INACTIVE
          </code>
         </a>
         statement.
        </p>
       </li>
       <li class="listitem">
        <p>
         <code class="literal">
          empty
         </code>
         : The undo tablespace was
              truncated and is no longer active. It is ready to be
              dropped or made active again by an
         <a class="link" href="alter-tablespace.html" title="15.1.10 ALTER TABLESPACE Statement">
          <code class="literal">
           ALTER
              UNDO TABLESPACE ... SET INACTIVE
          </code>
         </a>
         statement.
        </p>
       </li>
      </ul>
     </div>
    </li>
   </ul>
  </div>
  <h4>
   <a name="idm46045076386512">
   </a>
   Example
  </h4>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa25839018"><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 prompt">mysql&gt;</span> <span class="token keyword">SELECT</span> <span class="token operator">*</span> <span class="token keyword">FROM</span> INFORMATION_SCHEMA<span class="token punctuation">.</span>INNODB_TABLESPACES <span class="token keyword">WHERE</span> SPACE <span class="token operator">=</span> <span class="token number">26</span>\G
<span class="token output"><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span> 1. row <span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span>
         SPACE<span class="token punctuation">:</span> 26
          NAME<span class="token punctuation">:</span> test/t1
          FLAG<span class="token punctuation">:</span> 0
    ROW_FORMAT<span class="token punctuation">:</span> Compact or Redundant
     PAGE_SIZE<span class="token punctuation">:</span> 16384
 ZIP_PAGE_SIZE<span class="token punctuation">:</span> 0
    SPACE_TYPE<span class="token punctuation">:</span> Single
 FS_BLOCK_SIZE<span class="token punctuation">:</span> 4096
     FILE_SIZE<span class="token punctuation">:</span> 98304
ALLOCATED_SIZE<span class="token punctuation">:</span> 65536
AUTOEXTEND_SIZE<span class="token punctuation">:</span> 0
SERVER_VERSION<span class="token punctuation">:</span> 8.4.0
 SPACE_VERSION<span class="token punctuation">:</span> 1
    ENCRYPTION<span class="token punctuation">:</span> N
         STATE<span class="token punctuation">:</span> normal</span></code></pre>
  </div>
  <h4>
   <a name="idm46045076383760">
   </a>
   Notes
  </h4>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      You must have the
      <a class="link" href="privileges-provided.html#priv_process">
       <code class="literal">
        PROCESS
       </code>
      </a>
      privilege to query this table.
     </p>
    </li>
    <li class="listitem">
     <p>
      Use the
      <code class="literal">
       INFORMATION_SCHEMA
      </code>
      <a class="link" href="information-schema-columns-table.html" title="28.3.8 The INFORMATION_SCHEMA COLUMNS Table">
       <code class="literal">
        COLUMNS
       </code>
      </a>
      table or the
      <a class="link" href="show-columns.html" title="15.7.7.6 SHOW COLUMNS Statement">
       <code class="literal">
        SHOW COLUMNS
       </code>
      </a>
      statement to view
          additional information about the columns of this table,
          including data types and default values.
     </p>
    </li>
   </ul>
  </div>
 </div>
 <br/>
</div>
