LightDB 24.2 introduces a recycle bin mechanism similar to Oracle's functionality, where dropped tables are temporarily preserved rather than immediately deleted. This feature allows for table recovery within a configurable retention period.

Configuration

In Oracle-compatible mode with enable_recyclebin praameter active (default), dropped tables are retained for recyclebin_clean_interval seconds (default: 30 days).

CREATE DATABASE recovery_test LIGHTDB_SYNTAX_COMPATIBLE_TYPE ORACLE;
\c recovery_test
SHOW enable_recyclebin;
SHOW recyclebin_clean_interval;
PURGE RECYCLEBIN; -- Clear existing entries

Basic Operations

Create and drop a table:

CREATE TABLE sample_data(id INT);
INSERT INTO sample_data VALUES (100);
DROP TABLE sample_data;
SHOW RECYCLEBIN;

Query recycle bin metadata:

SELECT * FROM user_recyclebin WHERE original_name = 'SAMPLE_DATA';

Restore the table:

FLASHBACK TABLE sample_data TO BEFORE DROP;
SELECT * FROM sample_data;
DROP TABLE sample_data PURGE; -- Bypass recycle bin

Advnaced Scenarios

Handling constraints and indexes:

CREATE TABLE parent(pk INT PRIMARY KEY);
CREATE TABLE child(
  id INT, 
  parent_ref INT CONSTRAINT fk_parent REFERENCES parent(pk)
);
DROP TABLE parent CASCADE CONSTRAINTS;
FLASHBACK TABLE parent TO BEFORE DROP;

Function-based indexes:

CREATE FUNCTION custom_upper(text) RETURNS text AS $$
BEGIN RETURN upper($1); END;
$$ LANGUAGE plpgsql IMMUTABLE;

CREATE TABLE text_data(content TEXT);
CREATE INDEX idx_upper_content ON text_data (custom_upper(content));
DROP TABLE text_data;
FLASHBACK TABLE text_data TO BEFORE DROP;

Maintenance

Periodically clean the recycle bin:

PURGE RECYCLEBIN;