| General |
| Related Data Dictionary Objects |
| recyclebin$ |
dba_recyclebin |
recyclebin |
user_recyclebin |
|
Related Startup Parameters |
col name format a30
col value format a30
SELECT name, value
FROM v$parameter
WHERE name LIKE 'recyc%'; |
| |
| Recyclebin Management |
Starting and stopping the recyclebin |
ALTER SYSTEM SET recyclebin=<OFF | ON>
SCOPE=<BOTH | MEMORY | SPFILE>; |
conn uwclass/uwclass
col name format a30
col value format a30
SELECT name, value
FROM v$parameter
WHERE name LIKE 'recyc%';
desc recyclebin
desc use_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 / as sysdba
ALTER SYSTEM SET recyclebin=off SCOPE=SPFILE;
SHUTDOWN IMMEDIATE;
STARTUP;
conn uwclass/uwclass
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 <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; |
| |
| Purging the Recyclebin |
Remove A Recycle Bin Object By Name |
PURGE TABLE "<recycle_bin_name>"; |
conn / as sysdba
GRANT unlimited tablespace TO uwclass;
conn uwclass/uwclass
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 / 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>; |
|
PURGE TABLESPACE uwdata USER uw; |
| Empty The Recycle Bin |
PURGE recyclebin; |
| Empty Everything In All Recycle Bins |
PURGE
dba_recyclebin; |
| |
| |
