Enable or disable the recycle bin

In OceanBase Database, you can set the recyclebin parameter 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. You can enable or disable the recycle bin in an appropriate way based on your business 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 user tenant, you can manage databases, tables, and indexes in the recycle bin.

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

Procedure

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

   Note

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

   Note that you must specify the corresponding fields in the following sample code based on your actual database configurations.

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

   For more information about how to connect to a database, see Overview (MySQL mode) or Overview (Oracle mode).

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

   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.

   The recyclebin variable is Boolean and can be set to one of the following values:

   • on, 1, or true: specifies to enable the recycle bin.

   • off, 0, or false: specifies to disable 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 and takes effect only on sessions established upon re-login.

     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 topics:

• Recycle bin overview

• View objects in the recycle bin

• Purge the recycle bin

• Restore objects from the recycle bin