ACE Director Alum Daniel Morgan, founder of Morgan's Library, is scheduling
complimentary technical Workshops on Database Security for the first 30
Oracle Database customers located anywhere in North America, EMEA, LATAM, or
APAC that send an email to
asra_us@oracle.com. Request a Workshop for
your organization today.
Data Dictionary Objects
DBA_RECYCLEBIN
RECYCLEBIN$
USER_RECYCLEBIN
RECYCLEBIN
Related Startup Parameters
col name format a30
col value format a30
SELECT name, value
FROM v$parameter
WHERE name LIKE 'recyc%';
System Privileges
PURGE DBA_RECYCLEBIN
Recyclebin Management
Starting and stopping the recyclebin
ALTER SYSTEM SET recyclebin=<OFF | ON> SCOPE=<BOTH | MEMORY | SPFILE>;
conn uwclass/uwclass@pdbdev
col name format a30
col value format a30
SELECT name, value
FROM v$parameter
WHERE name LIKE 'recyc%';
desc recyclebin
desc user_recyclebin
desc dba_recyclebin
SELECT object_name, original_name
FROM user_recyclebin;
CREATE TABLE t1 (
testcol DATE);
DROP TABLE t1;
SELECT object_name, original_name
FROM user_recyclebin;
conn sys@pdbdev as sysdba
ALTER SYSTEM SET recyclebin=off SCOPE=SPFILE;
SHUTDOWN IMMEDIATE;
STARTUP;
conn uwclass/uwclass@pdbdev
SELECT name, value
FROM v$parameter
WHERE name LIKE 'recyc%';
CREATE TABLE t2 (
testcol DATE);
DROP TABLE t2;
SELECT object_name, original_name
FROM user_recyclebin;
conn / as sysdba
ALTER SYSTEM SET recyclebin=on SCOPE=SPFILE;
SHUTDOWN IMMEDIATE;
STARTUP;
Flashback Dropped Objects
Flashback from the Recycle Bin using the Recyclebin object name
FLASHBACK TABLE <schema_name.object_name> TO BEFORE DROP
[RENAME TO <new_table_name>];
PURGE RECYCLEBIN;
SELECT object_name, original_name, type, related, base_object
FROM user_recyclebin;
CREATE TABLE t (
col1 NUMBER(3),
col2 VARCHAR2(3));
ALTER TABLE t
ADD CONSTRAINT pk_t
PRIMARY KEY (col1)
USING INDEX;
ALTER TABLE t
ADD CONSTRAINT cc_t_col2
CHECK (col2 IN ('ABC', 'DEF'));
CREATE OR REPLACE TRIGGER bi_t
BEFORE INSERT
ON t
DECLARE
BEGIN
NULL;
END bi_t;
/
DROP TABLE t;
SELECT object_name, original_name, type
FROM user_recyclebin;
FLASHBACK TABLE t TO BEFORE DROP;
SELECT object_name, original_name, type
FROM user_recyclebin;
SELECT table_name FROM user_tables;
SELECT constraint_name, constraint_type
FROM user_constraints
WHERE table_name = 'T';
SELECT index_name, uniqueness
FROM user_indexes
WHERE table_name = 'T';
SELECT trigger_name
FROM user_triggers
WHERE table_name = 'T';
ALTER TABLE t RENAME CONSTRAINT "BIN$A08sxN1NQIGxVdIaARKePA==$0"
TO pk_t;
ALTER TABLE t RENAME CONSTRAINT "BIN$KKMjMoJYRLu7A2ugWCmnPQ==$0"
TO cc_t_col2;
SELECT constraint_name, constraint_type
FROM user_constraints
WHERE table_name = 'T';
ALTER INDEX "BIN$lmaYVjyCS/2DrZpLpjJIcQ==$0" RENAME TO pk_t;
SELECT index_name, uniqueness
FROM user_indexes
WHERE table_name = 'T';
ALTER TRIGGER "BIN$kDcWB1a6Sa2jJL7zZVPdzQ==$0" RENAME TO bi_t;
SELECT trigger_name
FROM user_triggers
WHERE table_name = 'T';
DROP TABLE t PURGE;
SELECT object_name, original_name, type
FROM user_recyclebin;
Flashback from the Recycle Bin using the original object name
FLASHBACK TABLE <original_table_name> TO BEFORE DROP {RENAME TO <new_table_name>};
CREATE TABLE test (
testcol VARCHAR2(20));
INSERT INTO test VALUES ('ABC');
COMMIT;
SELECT * FROM test;
DROP TABLE test;
CREATE TABLE test (
testcol VARCHAR2(20));
INSERT INTO test VALUES ('DEF');
COMMIT;
SELECT * FROM test;
DROP TABLE test;
CREATE TABLE test (
testcol VARCHAR2(20));
INSERT INTO test VALUES ('GHI');
COMMIT;
SELECT * FROM test;
DROP TABLE test;
SELECT object_name, original_name, droptime
FROM recyclebin;
FLASHBACK TABLE test TO BEFORE DROP;
FLASHBACK TABLE test TO BEFORE DROP RENAME TO test2;
FLASHBACK TABLE test TO BEFORE DROP RENAME TO test1;
SELECT * FROM test;
SELECT * FROM test2;
SELECT * FROM test1;
Recyclebin Purging
Remove A Recycle Bin Object By Name
PURGE TABLE "<recycle_bin_name>";
conn sys@pdbdev as sysdba
GRANT unlimited tablespace TO uwclass;
conn uwclass/uwclass@pdbdev
SELECT tablespace_name
FROM user_tablespaces;
CREATE TABLE test1
TABLESPACE uwdata AS
SELECT * FROM user_objects;
CREATE TABLE test2
TABLESPACE example AS
SELECT * FROM user_objects;
CREATE TABLE test3
TABLESPACE users AS
SELECT * FROM user_objects;
SELECT object_name, original_name
FROM user_recyclebin;
DROP TABLE test1;
DROP TABLE test2;
DROP TABLE test3;
SELECT object_name, original_name, ts_name
FROM user_recyclebin;
PURGE TABLE "BIN$V3zj9aOWRImOT89aKyyVSg==$0";
SELECT object_name, original_name, ts_name
FROM user_recyclebin;
PURGE TABLE TEST1;
SELECT object_name, original_name, ts_name
FROM user_recyclebin;
conn sys@pdbdev as sysdba
REVOKE unlimited tablespace FROM uwclass;
Remove Recycle Bin Objects By Tablespace
PURGE TABLESPACE <tablespace_name>;
CREATE TABLE test1
TABLESPACE uwdata AS
SELECT * FROM user_objects;
CREATE TABLE test3
TABLESPACE example AS
SELECT * FROM user_objects;
DROP TABLE test1;
DROP TABLE test3;
SELECT object_name, original_name, ts_name
FROM user_recyclebin;
PURGE TABLESPACE example;
SELECT object_name, original_name, ts_name
FROM user_recyclebin;
Remove Recycle Bin Objects By Tablespace And User
PURGE TABLESPACE <tablespace_name> USER <schema_name>;
