Tablespaces 1. Default and user-defined undo tablespaces that exceed the  innodb_max_undo_log_size  setting are marked for truncation. Selection of an undo tablespace for truncation is performed in a circular fashion to avoid truncating the same undo tablespace each time. 2. Rollback segments residing in the selected undo tablespace are made inactive so that they are not assigned to new transactions. Existing transactions that are currently using rollback segments are permitted to finish. 3. The purge system empties rollback segments by freeing undo logs that are no longer in use. 4. After all rollback segments in the undo tablespace are freed, the truncate operation runs and truncates the undo tablespace to its initial size. 5. Rollback segments are reactivated so that they can be assigned to new transactions. 3064 To check the state of an undo tablespace, query the  INFORMATION_SCHEMA.INNODB_TABLESPACES table. When an  ALTER UNDO TABLESPACE  tablespace_name  SET INACTIVE statement deactivates an undo tablespace, the purge thread looks for that undo tablespace at the next opportunity. Once the undo tablespace is found and marked for truncation, the purge thread returns with increased frequency to quickly empty and truncate the undo tablespace. After the 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, the undo tablespace is truncated to its initial size, and the undo tablespace state changes from  inactive to  empty . Manual truncation of undo tablespaces requires a minimum of three active undo tablespaces. Two active undo tablespaces are required at all times to support the possibility that automated truncation is enabled. A minimum of three undo tablespaces satisfies this requirement while permitting an undo tablespace to be taken offline manually. To manually initiate truncation of an undo tablespace, deactivate the undo tablespace by issuing the following statement: The  innodb_undo_directory  variable defines the location of default undo tablespace files. If the  innodb_undo_directory  variable is undefined, default undo tablespaces reside in the data directory. The location of all undo tablespace files including user-defined undo tablespaces created using  CREATE UNDO TABLESPACE  syntax can be determined by querying the INFORMATION_SCHEMA.FILES  table: The size of an undo tablespace after a truncate operation may be larger than the initial size due to immediate use following the completion of the operation. SELECT TABLESPACE_NAME, FILE_NAME FROM INFORMATION_SCHEMA.FILES WHERE FILE_TYPE LIKE ’UNDO LOG’; ALTER UNDO TABLESPACE  tablespace_name  SET INACTIVE; SELECT NAME, STATE FROM INFORMATION_SCHEMA.INNODB_TABLESPACES WHERE NAME LIKE ’ tablespace_name ’; Once the undo tablespace is in an  empty  state, it can be reactivated by issuing the following statement: Note Manual Truncation