Use the Oracle recycle bin and the oracle Recycle Bin

Use the Oracle recycle bin and the oracle Recycle Bin
 

-- Date: January 1, January 27, 2016
-- Author: zhenxing yu

Query the recycle bin

SELECT * from recyclebin; SELECT * FROM USER_RECYCLEBIN; -- USER_RECYCLEBING and RECYCLEBIN are synonyms, with identical Fields

 

-- Enable or disable the recycle bin
-- A: enable/disable A session

ALTER SESSION SET RECYCLEBIN = OFF/ON;

 

-- B: system-level enabling/disabling

Alter system set recyclebin = OFF/on scope = SPFILE; -- (you need to restart the database)

 

-- Query table data in the recycle bin

SELECT * FROM "BIN$KlM0Cy+QG0jgU3AVGawe9w==$0";

 

-- Clear data in the recycle bin

Purge table bin $ KlM0Cy + q1_jgu3avgawe9w = $0; -- purge table employees based on OBJECT_NAME; -- purge recyclebin Based on ORIGINAL_NAME name; -- purge recyclebin example user scott; -- clear the recycle bin data, but keep the data of scott.

 

-- Restore the data table in the recycle bin

Flashback table employees to before drop; -- restore TO the original TABLE name flashback table employees to before drop rename to EMPLOYEES_NEW; -- restore TO the new TABLE name

 

-- Objects attached to the member table
--Note:: The table in the recycle bin. After the table is restored, the related indexes are also restored, but the index name will still be used in the recycle bin.
-- You need to rename the index to understand the meaning of each index.

-- 1. query the index name of the recovered table

SELECT INDEX_NAME, INDEX_TYPE, TABLE_NAME, UNIQUENESSFROM USER_INDEXESWHERE TABLE_NAME = 'EMP1';

 

-- 2. Rename the index

ALTER INDEX "BIN$KlNYwcBGG1LgU3AVGazI7Q==$0" RENAME TO EMP_N1;