Shulou(Shulou.com)05/31 Report--

This article mainly shows you "how to use flashback deletion to back DROP TABLE operation in database", the content is simple and easy to understand, organized clearly, I hope to help you solve doubts, let Xiaobian lead you to study and learn "how to use flashback deletion to back DROP TABLE operation in database" this article bar.

- Flashbacks.

SELECT * FROM AA AS OF TIMESTAMP(sysdate-1/24/60) --View data 2 hours ago

12 is 12 hours.

1/24 is 1 hour.

1/24/60 is 1 minute.

alter table tableName enable row movement;

This command allows you to modify the ROWID assigned to a row by Oracle and insert a row of data into oracle.

it's assigned a rowid, this row always has this ROWID, flashback table processing completes the Delete operation on EMP.

Add a new row, which assigns a new ROWID to the new row; otherwise, an ora-08189 error is reported.

flashback table tableName to scn timestamp_to_scn(sysdate-1/12);--Use time to convert to SCN number for data recovery.

SCN:

SCN(System Change Number): A serial number maintained internally by the system. Automatically when the system needs to be updated

It is an important symbol of maintaining data consistency and order recovery in the system.

--Recover deleted tables

DROP TABLE AA;

1. Query this table with the following statement to find that it is still in the Oracle Recycle Bin:

SELECT * FROM user_recyclebin WHERE original_name='AA';

2. Restore with the following statement:

FLASHBACK TABLE AA TO BEFORE DROP;

ORACLE 10G adds the function of recycle bin. In principle, it is a data dictionary to save the information of DROP objects.]

Objects that are actually DROP's are not deleted from the database and still occupy space. Unless the user manually purges and the data space is not enough to be cleared.

3. Clear data from Recycle Bin

purge table tableName

purge recycle bin;--Clear all

Reversing DROP TABLE operations using flashback delete

This section explains how to use the FLASHBACK TABLE…TO BEFORE DROP statement to get objects from the recycle bin.

About Flashback Delete

Flashback Delete can roll back the effects of DROP TABLE operations

Flashback deletes are faster than other recovery techniques that can be applied to the scenario, such as point-in-time recovery, without causing downtime or loss of recent transactions.

When you delete a table, the database does not immediately remove the space associated with that table.

Instead, the table is renamed and placed in the recycle bin along with other related objects.

The names of system-generated recycle bin objects are unique. You can query objects in the Recycle Bin just like you query other objects.

FLAHSBACK operation retrieves objects from the retrieval station, and when retrieving deleted tables,

You can specify either the original user-specified name or a system-generated name for the table.

When you delete a table, the table and all its dependent objects go to the recycle bin.

So when flashback deletion is performed, all objects are usually fetched together.

When tables are restored from the recycle bin, dependent objects, such as indexes, do not get their original names, retaining the names in the system-generated recycle bin.

Oracle fetches all indexes (except bitmap join indexes), triggers, and constraints (except referential integrity constraints that reference other tables) defined on the table

Some dependent objects, such as indexes, can be reclaimed due to space pressure. The recycled objects in this case are not retrieved from the Recycle Bin.

The above is "how to use flashback delete in the database back DROP TABLE operation" all the contents of this article, thank you for reading! I believe that everyone has a certain understanding, hope to share the content to help everyone, if you still want to learn more knowledge, welcome to pay attention to the industry information channel!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.