<div id="docs-body">
 <div class="section">
  <div class="titlepage">
   <div>
    <div>
     <h3 class="title">
      <a name="drop-tablespace">
      </a>
      15.1.33 DROP TABLESPACE Statement
     </h3>
    </div>
   </div>
  </div>
  <a class="indexterm" name="idm46045183298432">
  </a>
  <a class="indexterm" name="idm46045183296976">
  </a>
  <a class="indexterm" name="idm46045183295488">
  </a>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa78997018"><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">DROP</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></code></pre>
  </div>
  <p>
   This statement drops a tablespace that was previously created
      using
   <a class="link" href="create-tablespace.html" title="15.1.21 CREATE TABLESPACE Statement">
    <code class="literal">
     CREATE TABLESPACE
    </code>
   </a>
   . It is
      supported by the
   <code class="literal">
    NDB
   </code>
   and
   <code class="literal">
    InnoDB
   </code>
   storage engines.
  </p>
  <p>
   The
   <code class="literal">
    UNDO
   </code>
   keyword must be specified to drop an
      undo tablespace. Only undo tablespaces created using
   <a class="link" href="create-tablespace.html" title="15.1.21 CREATE TABLESPACE Statement">
    <code class="literal">
     CREATE UNDO
      TABLESPACE
    </code>
   </a>
   syntax can be dropped. An undo tablespace
      must be in an
   <code class="literal">
    empty
   </code>
   state before it can be
      dropped. 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>
   <code class="literal">
    <em class="replaceable">
     <code>
      tablespace_name
     </code>
    </em>
   </code>
   is a
      case-sensitive identifier in MySQL.
  </p>
  <p>
   For an
   <code class="literal">
    InnoDB
   </code>
   general tablespace, all tables
      must be dropped from the tablespace prior to a
   <code class="literal">
    DROP
      TABLESPACE
   </code>
   operation. If the tablespace is not empty,
   <code class="literal">
    DROP TABLESPACE
   </code>
   returns an error.
  </p>
  <p>
   An
   <code class="literal">
    NDB
   </code>
   tablespace to be dropped must not
      contain any data files; in other words, before you can drop an
   <code class="literal">
    NDB
   </code>
   tablespace, you must first drop each of its
      data files using
   <a class="link" href="alter-tablespace.html" title="15.1.10 ALTER TABLESPACE Statement">
    <code class="literal">
     ALTER TABLESPACE
      ... DROP DATAFILE
    </code>
   </a>
   .
  </p>
  <h4>
   <a name="idm46045183275856">
   </a>
   Notes
  </h4>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      A general
      <code class="literal">
       InnoDB
      </code>
      tablespace is not deleted
          automatically when the last table in the tablespace is
          dropped. The tablespace must be dropped explicitly using
      <code class="literal">
       DROP TABLESPACE
       <em class="replaceable">
        <code>
         tablespace_name
        </code>
       </em>
      </code>
      .
     </p>
    </li>
    <li class="listitem">
     <p>
      A
      <a class="link" href="drop-database.html" title="15.1.24 DROP DATABASE Statement">
       <code class="literal">
        DROP DATABASE
       </code>
      </a>
      operation can
          drop tables that belong to a general tablespace but it cannot
          drop the tablespace, even if the operation drops all tables
          that belong to the tablespace. The tablespace must be dropped
          explicitly using
      <code class="literal">
       DROP TABLESPACE
       <em class="replaceable">
        <code>
         tablespace_name
        </code>
       </em>
      </code>
      .
     </p>
    </li>
    <li class="listitem">
     <p>
      Similar to the system tablespace, truncating or dropping
          tables stored in a general tablespace creates free space
          internally in the general tablespace
      <a class="link" href="glossary.html#glos_ibd_file" title=".ibd file">
       .ibd data file
      </a>
      which can
          only be used for new
      <code class="literal">
       InnoDB
      </code>
      data. Space is
          not released back to the operating system as it is for
          file-per-table tablespaces.
     </p>
    </li>
   </ul>
  </div>
  <h4>
   <a name="idm46045183266112">
   </a>
   InnoDB Examples
  </h4>
  <p>
   This example demonstrates how to drop an
   <code class="literal">
    InnoDB
   </code>
   general tablespace. The general tablespace
   <code class="literal">
    ts1
   </code>
   is created with a single table. Before dropping the tablespace,
      the table must be dropped.
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa25238966"><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">CREATE</span> <span class="token keyword">TABLESPACE</span> <span class="token punctuation">`</span>ts1<span class="token punctuation">`</span> <span class="token keyword">ADD</span> <span class="token keyword">DATAFILE</span> <span class="token string">'ts1.ibd'</span> <span class="token keyword">Engine</span><span class="token operator">=</span>InnoDB<span class="token punctuation">;</span>

<span class="token prompt">mysql&gt;</span> <span class="token keyword">CREATE</span> <span class="token keyword">TABLE</span> t1 <span class="token punctuation">(</span>c1 <span class="token datatype">INT</span> <span class="token keyword">PRIMARY</span> <span class="token keyword">KEY</span><span class="token punctuation">)</span> <span class="token keyword">TABLESPACE</span> ts1 <span class="token keyword">Engine</span><span class="token operator">=</span>InnoDB<span class="token punctuation">;</span>

<span class="token prompt">mysql&gt;</span> <span class="token keyword">DROP</span> <span class="token keyword">TABLE</span> t1<span class="token punctuation">;</span>

<span class="token prompt">mysql&gt;</span> <span class="token keyword">DROP</span> <span class="token keyword">TABLESPACE</span> ts1<span class="token punctuation">;</span></code></pre>
  </div>
  <p>
   This example demonstrates dropping an undo tablespace. An undo
      tablespace must be in an
   <code class="literal">
    empty
   </code>
   state before it
      can be dropped. 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>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa8881016"><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">DROP</span> <span class="token keyword">UNDO</span> <span class="token keyword">TABLESPACE</span> <em class="replaceable">undo_003</em><span class="token punctuation">;</span></code></pre>
  </div>
  <h4>
   <a name="idm46045183255600">
   </a>
   NDB Example
  </h4>
  <p>
   This example shows how to drop an
   <code class="literal">
    NDB
   </code>
   tablespace
   <code class="literal">
    myts
   </code>
   having a data file named
   <code class="filename">
    mydata-1.dat
   </code>
   after first creating the
      tablespace, and assumes the existence of a log file group named
   <code class="literal">
    mylg
   </code>
   (see
   <a class="xref" href="create-logfile-group.html" title="15.1.16 CREATE LOGFILE GROUP Statement">
    Section 15.1.16, “CREATE LOGFILE GROUP Statement”
   </a>
   ).
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa13242680"><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">CREATE</span> <span class="token keyword">TABLESPACE</span> myts
<span class="token prompt">    -&gt;</span>     <span class="token keyword">ADD</span> <span class="token keyword">DATAFILE</span> <span class="token string">'mydata-1.dat'</span>
<span class="token prompt">    -&gt;</span>     <span class="token keyword">USE</span> <span class="token keyword">LOGFILE</span> <span class="token keyword">GROUP</span> mylg
<span class="token prompt">    -&gt;</span>     <span class="token keyword">ENGINE</span><span class="token operator">=</span><span class="token keyword">NDB</span><span class="token punctuation">;</span></code></pre>
  </div>
  <p>
   You must remove all data files from the tablespace using
   <a class="link" href="alter-tablespace.html" title="15.1.10 ALTER TABLESPACE Statement">
    <code class="literal">
     ALTER TABLESPACE
    </code>
   </a>
   , as shown here,
      before it can be dropped:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa58042153"><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">ALTER</span> <span class="token keyword">TABLESPACE</span> myts
<span class="token prompt">    -&gt;</span>     <span class="token keyword">DROP</span> <span class="token keyword">DATAFILE</span> <span class="token string">'mydata-1.dat'</span><span class="token punctuation">;</span>

<span class="token prompt">mysql&gt;</span> <span class="token keyword">DROP</span> <span class="token keyword">TABLESPACE</span> myts<span class="token punctuation">;</span></code></pre>
  </div>
 </div>
 <br/>
</div>
