Oracle data Flash-back functionality (Recover mistakenly deleted table information) __oracle

Source: Internet
Author: User
Tags dba
1 method of Rollback after Oracle submits data with Pl/sql

1, if the database table, does not support the flash-back function
ALTER TABLE A enable row movement;
2, query Delete data point of time data (that is, flash back to the point before the time data)
SELECT * from A as of timestamp to_timestamp (' 2016-02-25 17:10:00 ', ' yyyy-mm-dd hh24:mi:ss '); (if not, continue to narrow the scope)
3. Recover deleted and submitted data
Flashback table A to timestamp to_timestamp (' 2016-02-25 17:10:00 ', ' yyyy-mm-dd hh24:mi:ss ')
Note: If the above statement is executed, an error occurs. You can attempt to execute the ALTER TABLE name enable row movement; Allow Change time stamp


1, if the database table, does not support the FLASHBACK function

ALTER TABLE wms_out_end_product_item enable row movement;

2. Query the data at the point-in-time of the deletion data (that is, the data before the Point-in-time)

select * from wms_test as of timestamp to_timestamp (' 2016-02-26 12:10:00 ', ' yyyy-m M-dd hh24:mi:ss ');  (if not, continue to narrow the scope)

3. Recover deleted and submitted data

Flashback table Wms_out_end_product_item to timestamp to_timestamp (' 2016-04-1 14:30:00 ', '    Yyyy-mm-dd hh24:mi:ss ');

Note: If the above statement is executed, an error occurs. You can attempt to execute the ALTER TABLE name enable row movement; Allow Change time stamp


2 Recovery table

View the tables in the Recycle Bin

Recovery table
Sql>flashback table Test_drop to before drop;
Sql>flashback table "Bin$b+xkko1rs5k10uko9bfmua==$0" to before drop;


Note: You must support version 9i or 10g, flashback cannot recover Full-text indexing


The following are resources


With the flash-back table feature in Oracle Database 10g, you can easily recover a table that was accidentally deleted


Here's what happens when it doesn't happen: the user deletes a very important table--of course accidentally deleted--and needs to be recovered as quickly as possible. (At some point, this unfortunate user may be the DBA.) )


Oracle9i database introduces the concept of a flashback query option to retrieve data from a point in the past, but it cannot flash back to DDL operations, such as deleting a table. The only way to recover is to use a point-in-time restore of the tablespace in another database, and then recreate the table in the current database using export/import or other methods. This process requires a lot of work and valuable time for DBAs, not to mention the use of another database for cloning.


Use the Flash Back table feature in Oracle Database 10g, which makes the recovery of the deleted table as simple as executing several statements. Let's see how the feature works.


Delete that table.


First, let's look at the tables in the current pattern.


Sql> select * from tab;


Tname


Tabtype


Clusterid


--------------------- - -- -- --- ------


Recycletest


TABLE


Now, we accidentally deleted the table:


sql> drop table recycletest;


Table dropped.


Now let's look at the status of the table.


Sql> select * from tab;


Tname


Tabtype


Clusterid


--------------------------- - -- -- --- ------


Bin$04lhcpndanfgmaaaaaanpw==$0 TABLE


Table Recycletest no longer exists, but note that a new table bin$04lhcpndanfgmaaaaaanpw==$0 appears. That's what happened: the deleted table Recycletest not completely disappeared, but was renamed to a system-defined name. It exists in the same table space and has the same structure as the original table. If indexes or triggers are defined on the table, they are also renamed, using the same naming conventions as the table. Any related sources, such as procedures, are invalidated; the triggers and indexes of the original table are placed on the renamed table bin$04lhcpndanfgmaaaaaanpw==$0, preserving the complete object structure of the deleted table.


Tables and their related objects are placed in a logical container called the Recycle Bin, similar to the Recycle Bin in your PC. However, the objects are not removed from the table space they were originally in, and they still occupy the space there. The Recycle Bin is simply a logical structure that lists the directories of the deleted objects. Use the following command at the Sql*plus prompt to view its contents (you need to use Sql*plus 10.1来 to do this):


Sql> Show RecycleBin


ORIGINAL NAME


RecycleBin NAME


OBJECT TYPE


DROP time


------------- - -- ----------------------- - -- ----- - -- --------------


Recycletest


Bin$04lhcpndanfgmaaaaaanpw==$0 TABLE


2004-02-16:21:13:31


The result shows the original name of the table Recycletest and shows the new name in the Recycle Bin, which is the same name as the new table we saw after the deletion. (Note: The exact name may vary depending on the platform.) To recover the table, all you have to do is use the Flashback Table command:


Sql> Flashback TABLE Recycletest to before DROP;


Flashback COMPLETE.


Sql> SELECT * from TAB;


Tname


Tabtype


Clusterid


--------------------------- - -- -- --- ------


Recycletest


TABLE


Look. The table recovered without difficulty. If you look at the Recycle Bin now, it will be empty.


Remember, putting a table in the Recycle Bin does not free up space in the original tablespace. To free up space, you need to empty the Recycle Bin with the following command:


PURGE RecycleBin;


But what to do if you want to completely delete the table without using the Flashback feature. In this case, you can permanently delete the table by using the following command:


DROP TABLE recycletest PURGE;


This command does not rename the table to the name in the Recycle Bin, but instead permanently deletes the table, just like the version before 10g.


Manage Recycle Bin


If there is no actual deletion of the table in the process-and therefore no tablespace freed-then what happens when the deleted object occupies all the space.


The answer is simple: this will not happen at all. When the tablespace is fully occupied by the Recycle Bin data so that the data file must be extended to accommodate more data, it can be said that the tablespace is under "Space pressure". At this point, the object is automatically purged from the Recycle Bin in a first-in, first-out manner. Related objects, such as indexes, are deleted before the table is deleted.


Similarly, space pressure may be caused by a user limit defined by a particular table space. The tablespace may have enough free space, but the user may run out of the portion of the table space that it allocates. In this case, Oracle automatically clears the object belonging to that user in the tablespace.


In addition, there are several ways to manually control the Recycle Bin. If you need to clear it from the Recycle Bin after deleting a particular table named TEST, you can perform


PURGE TABLE TEST;


Or use the name in its Recycle Bin:


PURGE TABLE "bin$04lhcpndanfgmaaaaaanpw==$0";


This command removes table TEST and all related objects, such as indexes, constraints, and so on from the Recycle Bin, thus saving space. However, if you want to permanently delete an index from the Recycle Bin, you can do so by using the following command:


Purge index in_test1_01;


This command will simply delete the index and leave a copy of the table in the Recycle Bin.


Sometimes it may be useful to purge at a higher level. For example, you might want to clear all objects in the Recycle Bin for tablespace USERS. Can perform:


PURGE tablespace USERS;


You may want to empty the Recycle Bin for only specific users in the tablespace. In a data warehouse type environment, a user creates and deletes many temporary tables, which can be useful at this point. You can change the above command to restrict the removal of only specific users:


PURGE tablespace USERS USER SCOTT;


Users such as SCOTT can use the following command to empty their Recycle Bin


PURGE RecycleBin;


The DBA can use the following command to clear all objects in any table space


PURGE Dba_recyclebin;


You can see that the Recycle Bin can be managed in a number of different ways to meet specific needs.


Table versioning and Flashback features


Users may often create and delete the same table multiple times, such as:


CREATE TABLE TEST (COL1 number);


INSERT into TEST VALUES (1);


Commit


DROP TABLE TEST;


CREATE TABLE TEST (COL1 number);


INSERT into TEST VALUES (2);


Commit


DROP TABLE TEST;


CREATE TABLE TEST (COL1 number);


INSERT into TEST VALUES (3);


Commit


DROP TABLE TEST;


At this point, if you want to perform a flashback operation on table TEST, what the value of the column COL1 should be. The general idea may be that the first version of the table is retrieved from the Recycle Bin, and the value of column COL1 is 1. In fact, the third version of the table is retrieved, not the first. So the value of column COL1 is 3, not 1.


You can also retrieve other versions of the deleted table at this time. However, the presence of table TEST does not allow this to occur. You have two options:


To use the Rename option:


Flashback TABLE TEST to before DROP RENAME to TEST2;


Flashback TABLE TEST to before DROP RENAME to TEST1;


These statements restore the first version of the table to TEST1, restoring the second version to TEST2. The values of columns COL1 in TEST1 and TEST2 will be 1 and 2, respectively. Or


Use a specific Recycle Bin name for the table to recover. To do this, first identify the Recycle Bin name for the table, and then execute:


Flashback TABLE "Bin$04lhcpnoanfgmaaaaaanpw==$0" to before DROP RENAME to TEST2;


Flashback TABLE "Bin$04lhcpnqanfgmaaaaaanpw==$0" to before DROP RENAME to TEST1;


These statements will restore the two versions of the deleted table.


Warning......


Canceling the delete attribute causes the table to revert to its original name, but related objects such as indexes and triggers do not restore the original name, and they still use the Recycle Bin name. Sources that are defined on a table, such as views and procedures, are not recompiled and remain in an invalid state. These original names must be manually obtained and applied to the flashback table.


The information is kept in a view named User_recyclebin. Use the following query to retrieve the original name before the table is flashed back.


SELECT object_name, Original_name, TYPE


From User_recyclebin


WHERE Base_object = (SELECT base_object from User_recyclebin


WHERE original_name = ' recycletest ')


and Original_name!= ' recycletest ';


object_name


Original_n TYPE


--------------------------- - -- --- - -- ----


Bin$04lhcpnianfgmaaaaaanpw==$0 in_rt_01


INDEX


Bin$04lhcpnganfgmaaaaaanpw==$0 Tr_rt


TRIGGER


After the table has a flashback operation, the indexes and triggers on the table recycletest are named as shown in the Object_name column. Based on the above query, you can rename the object by using the original name, as follows:


ALTER INDEX "bin$04lhcpnianfgmaaaaaanpw==$0" RENAME to in_rt_01;


ALTER TRIGGER "bin$04lhcpnganfgmaaaaaanpw==$0" RENAME to Tr_rt;


A notable exception is the bitmap index. When you delete a bitmap index, they are not placed in the Recycle Bin--so they cannot be retrieved. The constraint name cannot be retrieved from the view either. They must be renamed from other sources.
Other uses of the flash-back table


The Flash-back Delete table feature is not limited to recovering table deletions. Like a flashback query, you can also use it to restore a table to a different point in time, shaped like flashback table tmm2076 to TIMESTAMP to_timestamp (' 2007-05-22
12:00:00 ', ' yyyy-mm-dd hh24:mi:ss ')
Pop-up ORA-08189 error, you need to execute the following command:
ALTER TABLE tmm2076 enable row movement The function of this command is to allow Oracle to modify the ROWID assigned to the row.


And then flashback, the data is restored to completion.


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.