Rechercher dans le manuel MySQL
13.1.33 DROP TABLESPACE Syntax
This statement drops a tablespace that was previously created
using CREATE TABLESPACE
. It is
supported by the NDB
and
InnoDB
storage engines.
The UNDO
keyword, introduced in MySQL 8.0.14,
must be specified to drop an undo tablespace. Only undo
tablespaces created using
CREATE UNDO
TABLESPACE
syntax can be dropped. An undo tablespace
must be in an empty
state before it can be
dropped. For more information, see
Section 15.6.3.4, “Undo Tablespaces”.
ENGINE
sets the storage engine that uses the
tablespace, where engine_name
is the
name of the storage engine. Currently, the values
InnoDB
and NDB
are
supported. If not set, the value of
default_storage_engine
is used.
If it is not the same as the storage engine used to create the
tablespace, the DROP TABLESPACE
statement
fails.
is a
case-sensitive identifier in MySQL.
tablespace_name
For an InnoDB
general tablespace, all tables
must be dropped from the tablespace prior to a DROP
TABLESPACE
operation. If the tablespace is not empty,
DROP TABLESPACE
returns an error.
An NDB
tablespace to be dropped must not
contain any data files; in other words, before you can drop an
NDB
tablespace, you must first drop each of its
data files using
ALTER TABLESPACE
... DROP DATAFILE
.
Notes
A general
InnoDB
tablespace is not deleted automatically when the last table in the tablespace is dropped. The tablespace must be dropped explicitly usingDROP TABLESPACE
.tablespace_name
A
DROP DATABASE
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 usingDROP TABLESPACE
.tablespace_name
Similar to the system tablespace, truncating or dropping tables stored in a general tablespace creates free space internally in the general tablespace .ibd data file which can only be used for new
InnoDB
data. Space is not released back to the operating system as it is for file-per-table tablespaces.
InnoDB Examples
This example demonstrates how to drop an InnoDB
general tablespace. The general tablespace ts1
is created with a single table. Before dropping the tablespace,
the table must be dropped.
This example demonstrates dropping an undo tablespace. An undo
tablespace must be in an empty
state before it
can be dropped. For more information, see
Section 15.6.3.4, “Undo Tablespaces”.
NDB Example
This example shows how to drop an NDB
tablespace myts
having a data file named
mydata-1.dat
after first creating the
tablespace, and assumes the existence of a log file group named
mylg
(see
Section 13.1.16, “CREATE LOGFILE GROUP Syntax”).
- -> USE LOGFILE GROUP mylg
You must remove all data files from the tablespace using
ALTER TABLESPACE
, as shown here,
before it can be dropped:
Document created the 26/06/2006, last modified the 26/10/2018
Source of the printed document:https://www.gaudry.be/en/mysql-rf-drop-tablespace.html
The infobrol is a personal site whose content is my sole responsibility. The text is available under CreativeCommons license (BY-NC-SA). More info on the terms of use and the author.
References
These references and links indicate documents consulted during the writing of this page, or which may provide additional information, but the authors of these sources can not be held responsible for the content of this page.
The author This site is solely responsible for the way in which the various concepts, and the freedoms that are taken with the reference works, are presented here. Remember that you must cross multiple source information to reduce the risk of errors.