Enable or disable the recycle bin

In OceanBase Database, you can use the system variable recyclebin to enable or disable the recycle bin. By default, the recycle bin is disabled. If the recycle bin is enabled, when you perform the DROP operation on a database object or tenant, the object or tenant is moved to the recycle bin.

Background information

The system variables of OceanBase Database can be categorized into global variables and session-level variables. When enabling or disabling the recycle bin, you can choose the appropriate setting method based on your needs.

• A global variable is used to implement a global modification. Different users of the same database tenant share the settings of global variables. Modification to global variables remains effective after you exit the session. In addition, modification to a global variable does not take effect on the currently open session and takes effect only after a new session is established.

• A session-level variable is used to implement a session modification. When a client is connected to the database, the database copies global variables to automatically generate session-level variables. Modifications made to session-level variables apply to the current session only.

In OceanBase Database, you can manage different objects in the recycle bins of the sys tenant and user tenants.

• After the recycle bin is enabled in the sys tenant, you can manage databases, tables, indexes, and tenants in the recycle bin.

• After the recycle bin is enabled in a MySQL-compatible user tenant, you can manage databases, tables, and indexes in the recycle bin.

• After the recycle bin is enabled in an Oracle-compatible user tenant, you can manage tables and indexes in the recycle bin.

Procedure

1. Log in to the database as a tenant administrator of the sys tenant or a user tenant.

   Note

   The administrator user of a MySQL-compatible tenant is root, and that of an Oracle-compatible tenant is SYS.

   Below is a connection example. Use your actual environment when connecting to the database.

   obclient -h10.xx.xx.xx -P2883 -uroot@sys#obdemo -p***** -A
   

   For more detailed guidance on connecting to the database, see Overview of connection methods (MySQL-compatible mode) and Overview of connection methods (Oracle-compatible mode).

2. Execute the following statement to view the recycle bin status.

   obclient [(none)]> SHOW VARIABLES LIKE 'recyclebin';
   +---------------+-------+
   | Variable_name | Value |
   +---------------+-------+
   | recyclebin    | ON    |
   +---------------+-------+
   1 row in set
   

3. Choose an appropriate method to enable or disable the recycle bin based on your business needs.

   Because the recyclebin variable is of Boolean type, it supports the following values:

   • on, 1, or true: Enables the recycle bin.

   • off, 0, or false: Disables the recycle bin.

   • Enable or disable the recycle bin for the entire tenant

     The global setting applies to the entire tenant. It does not take effect on the current session. You need to log in again to establish a new session for it to take effect.

     The syntax is as follows:

     obclient [(none)]> SET GLOBAL recyclebin = on;
     
     obclient [(none)]> SET GLOBAL recyclebin = off;
     

     Or

     obclient [(none)]> SET GLOBAL recyclebin = 1;
     
     obclient [(none)]> SET GLOBAL recyclebin = 0;
     

     Or

     obclient [(none)]> SET GLOBAL recyclebin = true;
     
     obclient [(none)]> SET GLOBAL recyclebin = false;
     

   • Enable or disable the recycle bin for the current session

     The session-level setting applies only to the current session.

     obclient [(none)]> SET @@recyclebin = on;
     
     obclient [(none)]> SET @@recyclebin = off;
     

     Or

     obclient [(none)]> SET @@recyclebin = 1;
     
     obclient [(none)]> SET @@recyclebin = 0;
     

     Or

     obclient [(none)]> SET @@recyclebin = true;
     
     obclient [(none)]> SET @@recyclebin = false;
     

References

For more information about the recycle bin, see the following:

• Recycle bin overview

• View objects in the recycle bin

• Purge the recycle bin

• Restore objects from the recycle bin