Simple example
Empty the recycle bin for this example:
purge recyclebin;
Make sure the recycle bin functionality is enabled for this session:
alter session set recyclebin = on;
Create a table with an index (primary key) …
create table tq84_recyclebin_test (
id integer generated always as identity primary key,
val number(6,2)
);
… and fill a few values:
insert into tq84_recyclebin_test (val) values (7.01);
insert into tq84_recyclebin_test (val) values (5.83);
insert into tq84_recyclebin_test (val) values (5.26);
commit;
Drop table:
drop table tq84_recyclebin_test;
Table is not actually dropped, but moved to the recycle bin:
select
object_name,
original_name,
type,
can_undrop,
operation
from
user_recyclebin;
Undrop the table:
flashback table tq84_recyclebin_test to before drop;
The recycle bin is empty again …
select count(*) from user_recyclebin;
… and the original table is restored:
select * from tq84_recyclebin_test;
Drop the table again, this time, use the purge clause so that the table does not go to the recycle bin again:
drop table tq84_recyclebin_test purge;