Recyclebin in oracle with example

The recycle bin in Oracle is a feature that allows you to recover dropped tables. When you drop a table, it isn't immediately removed from the database. Instead, it's renamed and stored in the recycle bin, where it can be restored if needed. This feature is particularly useful for accidental drops and provides an additional layer of data protection.

How the Recycle Bin Works

• When you drop a table, it is renamed and placed in the recycle bin.
• You can view the contents of the recycle bin.
• You can restore a dropped table from the recycle bin.
• You can permanently purge the table from the recycle bin if you need to free up space.

Examples

1. Creating a Table and Dropping It

First, create a table and insert some data into it.

CREATE TABLE employees (
    employee_id NUMBER PRIMARY KEY,
    name VARCHAR2(50),
    position VARCHAR2(50)
);

INSERT INTO employees (employee_id, name, position) VALUES (1, 'John Doe', 'Manager');

INSERT INTO employees (employee_id, name, position) VALUES (2, 'Jane Smith', 'Analyst');

SELECT * FROM employees;

 Now, drop the table.

DROP TABLE employees;

2. Viewing the Recycle Bin

After dropping the table, you can view the contents of the recycle bin to see the dropped table.

SELECT * FROM RECYCLEBIN;

SELECT * FROM RECYCLEBIN where original_name='EMPLOYEES'

3. Restoring a Table from the Recycle Bin

You can restore the dropped table from the recycle bin using the FLASHBACK TABLE statement.

FLASHBACK TABLE employees TO BEFORE DROP;

After restoring, you can check the table's contents again.

You should see the original data:

SELECT * FROM employees;

4. Permanently Removing a Table from the Recycle Bin

If you want to permanently remove the table from the recycle bin, you can use the PURGE statement.

Purging a Specific Table

PURGE TABLE employees;

Purging the Entire Recycle Bin

PURGE RECYCLEBIN;

Managing the Recycle Bin

• Viewing Recycle Bin for a Specific User:

• SELECT * FROM USER_RECYCLEBIN;

• Viewing Recycle Bin for All Users (as DBA):
• 
  
• SELECT * FROM DBA_RECYCLEBIN;

Practical Use Cases

1. Accidental Drop Recovery

If a user accidentally drops a table, the recycle bin allows quick and easy recovery without the need for backups.

2. Space Management

If you need to free up space, you can purge specific objects or the entire recycle bin.

Important Considerations

• The recycle bin is enabled by default in Oracle Database 10g and later versions.
• The recycle bin can consume significant space if not managed properly.
• Tables in the recycle bin are still subject to storage limits and quotas.
• Some operations, such as dropping a user or tablespace, will purge the associated objects from the recycle bin.

Disabling the Recycle Bin

If you want to disable the recycle bin for performance reasons or other considerations, you can do so by setting the RECYCLEBIN parameter.

Disabling the Recycle Bin for the Current Session

ALTER SESSION SET recyclebin = OFF;

Disabling the Recycle Bin for the Entire Database

ALTER SYSTEM SET recyclebin = OFF SCOPE = BOTH;

The recycle bin is a powerful feature that enhances data recovery capabilities in Oracle, providing a safety net for accidental drops and a way to manage database objects more effectively.