Restore A Table accidentally deleted by flashback drop and perform operations on the table in the recycle bin

Source: Internet
Author: User

Restore A Table accidentally deleted by flashback drop and perform operations on the table in the recycle bin. delete the table by mistake. Use the flashback drop function to retrieve SQL> select * from tab; TNAME TABTYPE CLUSTERID ---------------------------- ------- ---------- TEST TABLESQL> select * from test; A ---------- 1 2 3 4 5 6 7 8 0. SQL> show parameter recyclebin;-verify whether the recycle bin is open -- if not open, you cannot use the flashback DROP--11G to open NAME TYPE VALUE defaults -------------------------------- recyclebin string o NSQL> show recyclebin; SQL> drop table test; Table dropped. query the recycle BIN, delete the table, and run the command -- BIN $ to start with a long string. From the original name column, you can see that the table was originally named testSQL> show recyclebin; original name recyclebin name object type drop time ------------------ hour ------------------------- test bin $387 tHPzSqO/hour = $0 TABLE 2013-06-23: 17: 29: 01SQL> select * from tab; tname tabtype clusterid ------------------- ----------- ------- ---------- BIN $387 tHPzSqO/gQKjA3QEnzg = $0 TABLE use the BIN $ TABLE name in the recycle BIN to query TABLE data SQL> select * from "BIN $387 tHPzSqO/gQKjA3QEnzg = = $0 "; A ---------- 1 2 3 4 5 6 7 8 0 execute the flash back command to delete the table and check whether the data is complete: SQL> flashback table test to before drop; Flashback complete. SQL> select * from "BIN $387 tHPzSqO/gQKjA3QEnzg = $0"; select * from "BIN $387 tHPzSqO/gQKjA3QEnzg = $0" * ERROR at line 1: ORA-00942: table or view does not ExistSQL> select * from tab; tname tabtype clusterid ------------------------------ ------- ---------- test tablesql> select * from test; A ---------- 1 2 3 4 5 6 7 8 02. flash back to delete and rename the table BYS @ bys1> select * from test5; id name ---------- 2 B BYS @ bys1> drop Table test5; table dropped. BYS @ bys1> flashback table test5 to before drop rename to test6; Flashback complete. BYS @ bys1> select * from test6; id name- --------- ---------- 2 b3. view the deleted tables in the recycle bin, delete all the tables in the recycle bin, and clear the recycle bin. SQL> create table test2 as select * fromtest1; the table has been created. SQL> insert into test2values ('20140901', 'baishan2'); 1 row has been created. SQL> select * from test2; ID NAME ----- ---------- 00001 baishan00002 baishan2SQL> show recyclebinSQL> drop table test1; the table has been deleted. You can view the deleted table name in the recycle bin after deleting the table, and deletion time SQL> showrecyclebinORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME =--------------------------- TEST1 BIN $ partition = $0 TABLE 2013-02-10: 12: 17: 11SQL> select * from cat; TABLE_NAME TABLE_TYPE ------------------------------ ----------- BIN $ kUfBJ3qcTSiLxqU6hmoEcw = $0 TABLETEST TABLETEST2 table SQL> create table t Est3 as select * fromtest2; the table has been created. SQL> select * from cat; TABLE_NAME TABLE_TYPE ------------------------------ ----------- BIN $ tables = $0 TABLETEST TABLETEST2 TABLETEST3 TABLEDROP the table deleted using the purge parameter is directly deleted and not stored in the recycle BIN. SQL> drop table test2 purge; the table has been deleted. SQL> show recyclebinORIGINAL name recyclebin name object type drop time =--------------------------- TEST1 BIN $ tables ==$ 0 TABLE 2013-02-10: 12: 17: 11 Clear the recycle bin SQL> purge recyclebin; the Recycle Bin has been cleared. SQL> drop table test2; the table has been deleted. SQL> show recyclebinORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME ----------------------------- TEST2 BIN $ UabVC8wiRx + records = $0 TABLE 2013-02-12: 17: 10: 46SQL> drop table test3; TABLE deleted. SQL> show recyclebinORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME when ------------ --------------------- TEST2 BIN $ UabVC8wiRx + GdsN4VT5bRw ==$ 0 TABLE 2013-02-12: 17: 10: 46 TEST3 BIN $ n8CZgrwoSRO16TFB/dXOcA = $0 TABLE 2013-02-12: 17: 11: 35 Clear the TABLE test2 in the recycle BIN. SQL> purge tabletest2; the table has been cleared. SQL> show recyclebinORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME =------------ export TEST3 BIN $ export/dXOcA ==$ 0 TABLE 2013-02-12: 17: 11: 35SQL> flashback table test3 to before drop; flash back complete. SQL> show recyclebin SQL> select count (*) from test3; COUNT (*) ---------- 2

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.