Oracle Recycle Bin Usage

Source: Internet
Author: User

--Time: January 27, 2016
--author:zhenxing Yu

--Query Recycle Bin
SELECT * from RecycleBin;
SELECT * from User_recyclebin; --user_recyclebing and RecycleBin are synonyms, the fields are exactly the same


--Turn the Recycle Bin on or off
--A: Session level on/off
ALTER SESSION SET recyclebin = off/on

--b: System level on/off
ALTER SYSTEM SET recyclebin = off/on scope=spfile; --(need to restart the database)

--Query the table data in the Recycle Bin
SELECT * from "bin$klm0cy+qg0jgu3avgawe9w==$0";

--Empty the data in the Recycle Bin
PURGE TABLE bin$klm0cy+qg0jgu3avgawe9w==$0; --Clear according to object_name name
PURGE TABLE EMPLOYEES; --Clear according to Original_name name
PURGE RecycleBin; --Emptying the Recycle Bin data
PURGE recyclebin EXAMPLE USER SCOTT; --Empty the Recycle Bin data, but keep the Scott user's data

--Restore the data table in the Recycle Bin
FLASHBACK TABLE EMPLOYEES to before DROP; --Revert to the original table name
FLASHBACK TABLE EMPLOYEES to before DROP RENAME to Employees_new; --Revert to a new table name

--objects attached to the membership table
- Note : The table in the Recycle Bin, when the table is restored, the associated index will also be restored, but the name of the index will still be used in the Recycle Bin name
--so you need to rename the index so you know what each index means

--1, querying the index name of the recovered table
SELECT index_name, Index_type, TABLE_NAME, uniqueness
From User_indexes
WHERE table_name = ' EMP1 ';
--2, renaming an index
ALTER INDEX "bin$klnywcbgg1lgu3avgazi7q==$0" RENAME to Emp_n1;

Oracle Recycle Bin Usage

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.