ALTER TABLESPACE

Syntax:
ALTER [UNDO] TABLESPACE tablespace_name
NDB only:
{ADD | DROP} DATAFILE 'file_name'
[INITIAL_SIZE [=] size]
[WAIT]
InnoDB and NDB:
[RENAME TO tablespace_name]
InnoDB only:
[SET {ACTIVE | INACTIVE}]
[ENCRYPTION [=] {'Y' | 'N'}]
InnoDB and NDB:
[ENGINE [=] engine_name]
Reserved for future use:
[ENGINE_ATTRIBUTE [=] 'string']

This statement is used with NDB and InnoDB tablespaces. It can be used
to add a new data file to, or to drop a data file from an NDB
tablespace. It can also be used to rename an NDB Cluster Disk Data
tablespace, rename an InnoDB general tablespace, encrypt an InnoDB
general tablespace, or mark an InnoDB undo tablespace as active or
inactive.

The UNDO keyword, introduced in MySQL 8.0.14, is used with the SET
{ACTIVE | INACTIVE} clause to mark an InnoDB undo tablespace as active
or inactive. For more information, see
https://dev.mysql.com/doc/refman/8.0/en/innodb-undo-tablespaces.html.

The ADD DATAFILE variant enables you to specify an initial size for an
NDB Disk Data tablespace using an INITIAL_SIZE clause, where size is
measured in bytes; the default value is 134217728 (128 MB). You may
optionally follow size with a one-letter abbreviation for an order of
magnitude, similar to those used in my.cnf. Generally, this is one of
the letters M (megabytes) or G (gigabytes).

On 32-bit systems, the maximum supported value for INITIAL_SIZE is
4294967296 (4 GB). (Bug #29186)

INITIAL_SIZE is rounded, explicitly, as for CREATE TABLESPACE.

Once a data file has been created, its size cannot be changed; however,
you can add more data files to an NDB tablespace using additional ALTER
TABLESPACE ... ADD DATAFILE statements.

When ALTER TABLESPACE ... ADD DATAFILE is used with ENGINE = NDB, a
data file is created on each Cluster data node, but only one row is
generated in the INFORMATION_SCHEMA.FILES table. See the description of
this table, as well as
https://dev.mysql.com/doc/refman/8.0/en/mysql-cluster-disk-data-objects
.html, for more information. ADD DATAFILE is not supported with InnoDB
tablespaces.

Using DROP DATAFILE with ALTER TABLESPACE drops the data file
'file_name' from an NDB 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
https://dev.mysql.com/doc/refman/8.0/en/mysql-cluster-disk-data-objects
.html. In addition, any data file to be dropped must previously have
been added to the tablespace with CREATE TABLESPACE or ALTER
TABLESPACE. DROP DATAFILE is not supported with InnoDB tablespaces.

WAIT is parsed but otherwise ignored. It is intended for future
expansion.

The ENGINE clause, which specifies the storage engine used by the
tablespace, is deprecated and will be removed in a future release. The
tablespace storage engine is known by the data dictionary, making the
ENGINE clause obsolete. If the storage engine is specified, it must
match the tablespace storage engine defined in the data dictionary. The
only values for engine_name compatible with NDB tablespaces are NDB and
NDBCLUSTER.

RENAME TO operations are implicitly performed in autocommit mode,
regardless of the autocommit setting.

A RENAME TO operation cannot be performed while LOCK TABLES or FLUSH
TABLES WITH READ LOCK is in effect for tables that reside in the
tablespace.

Exclusive metadata locks are taken on tables that reside in a general
tablespace while the tablespace is renamed, which prevents concurrent
DDL. Concurrent DML is supported.

The CREATE TABLESPACE privilege is required to rename an InnoDB general
tablespace.

The ENCRYPTION clause enables or disables page-level data encryption
for an InnoDB general tablespace or the mysql system tablespace.
Encryption support for general tablespaces was introduced in MySQL
8.0.13. Encryption support for the mysql system tablespace was
introduced in MySQL 8.0.16.

A keyring plugin must be installed and configured before encryption can
be enabled.

As of MySQL 8.0.16, if the table_encryption_privilege_check variable is
enabled, the TABLE_ENCRYPTION_ADMIN privilege is required to alter a
general tablespace with an ENCRYPTION clause setting that differs from
the default_table_encryption setting.

Enabling encryption for a general tablespace fails if any table in the
tablespace belongs to a schema defined with DEFAULT ENCRYPTION='N'.
Similarly, disabling encryption fails if any table in the general
tablespace belongs to a schema defined with DEFAULT ENCRYPTION='Y'. The
DEFAULT ENCRYPTION schema option was introduced in MySQL 8.0.16.

If an ALTER TABLESPACE statement executed on a general tablespace does
not include an ENCRYPTION clause, the tablespace retains its current
encryption status, regardless of the default_table_encryption setting.

When a general tablespace or the mysql system tablespace is encrypted,
all tables residing in the tablespace are encrypted. Likewise, a table
created in an encrypted tablespace is encrypted.

The INPLACE algorithm is used when altering the ENCRYPTION attribute of
a general tablespace or the mysql system tablespace. The INPLACE
algorithm permits concurrent DML on tables that reside in the
tablespace. Concurrent DDL is blocked.

For more information, see
https://dev.mysql.com/doc/refman/8.0/en/innodb-data-encryption.html.

The ENGINE_ATTRIBUTE option (available as of MySQL 8.0.21) is used to
specify tablespace attributes for primary storage engines. The option
is reserved for future use.

Permitted values are a string literal containing a valid JSON document
or an empty string (''). Invalid JSON is rejected.

ALTER TABLESPACE ts1 ENGINE_ATTRIBUTE='{"key":"value"}';

ENGINE_ATTRIBUTE values can be repeated without error. In this case,
the last specified value is used.

ENGINE_ATTRIBUTE values are not checked by the server, nor are they
cleared when the table's storage engine is changed.

It is not permitted to alter an individual element of a JSON attribute
value. You can only add or replace an attribute.

URL: https://dev.mysql.com/doc/refman/8.0/en/alter-tablespace.html