content_image/661429.jpg
longer used by existing transactions. The innodb_undo_tablespaces  variable can be configured at runtime using a SET  statement or defined in a configuration file. 

Prior to MySQL 8.0.14, deactivated undo tablespaces cannot be removed. Manual removal of undo tablespace files is possible after a slow shutdown but is not recommended, as deactivated undo tablespaces may contain active undo logs for some time after the server is restarted if open transactions were present when shutting down the server. As of MySQL 8.0.14, undo tablespaces can be dropped using DROP UNDO TABALESPACE  syntax. See Dropping Undo Tablespaces. 

## Dropping Undo Tablespaces 

As of MySQL 8.0.14, undo tablespaces created using CREATE UNDO TABLESPACE  syntax can be dropped at runtime using DROP UNDO TABALESPACE  syntax. 

An undo tablespace must be empty before it can be dropped. To empty an undo tablespace, the undo tablespace must first be marked as inactive using ALTER UNDO TABLESPACE  syntax so that the tablespace is no longer used for assigning rollback segments to new transactions. 

ALTER UNDO TABLESPACE tablespace_name  SET INACTIVE; 

After an undo tablespace is marked as inactive, transactions currently using rollback segments in the undo tablespace are permitted to finish, as are any transactions started before those transactions are completed. After transactions are completed, the purge system frees the rollback segments in the undo tablespace, and the undo tablespace is truncated to its initial size. (The same process is used when truncating undo tablespaces. See Truncating Undo Tablespaces.) Once the undo tablespace is empty, it can be dropped. 

DROP UNDO TABLESPACE tablespace_name ; 

<img src='content_image/661429.jpg'>

The state of an undo tablespace can be monitored by querying the INFORMATION_SCHEMA.INNODB_TABLESPACES  table. 

SELECT NAME, STATE FROM INFORMATION_SCHEMA.INNODB_TABLESPACES WHERE NAME LIKE ’ tablespace_name ’; 

## Note 

Alternatively, the undo tablespace can be left in an empty state and reactivated later, if needed, by issuing an ALTER UNDO TABLESPACE tablespace_name SET ACTIVE  statement. 

An inactive  state indicates that rollback segments in an undo tablespace are no longer used by new transactions. An empty  state indicates that an undo tablespace is empty and ready to be dropped, or ready to be made active again using an ALTER UNDO TABLESPACE tablespace_name  SET ACTIVE statement. Attempting to drop an undo tablespace that is not empty returns an error. 

The default undo tablespaces ( innodb_undo_001  and innodb_undo_002 ) created when the MySQL instance is initialized cannot be dropped. They can, however, be made inactive using an ALTER UNDO TABLESPACE tablespace_name  SET INACTIVE  statement. Before a default undo tablespace can be made inactive, there must be an undo tablespace to take its place. A minimum of two active undo tablespaces are required at all times to support automated truncation of undo tablespaces. 

## Moving Undo Tablespaces 

Undo tablespaces created with CREATE UNDO TABLESPACE  syntax can be moved while the server is offline to any known directory. Known directories are those defined by the innodb_directories variable. Directories defined by innodb_data_home_dir , innodb_undo_directory , and 

