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

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 on 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.

   Example (the command can vary in practice):

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

   For more information, see Database connection overview.

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-logon.

     Sample statement:

     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;
     

More information

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

• Recycle bin

• View objects in the recycle bin

• Purge the recycle bin

• Restore objects from the recycle bin