When operating an Oracle Database, you may encounter a situation where you accidentally delete important records and commit the transaction. While restoring from a backup is a major undertaking, the FLASHBACK TABLE feature allows you to quickly revert a specific table to a past state.
In this guide, we will provide a thorough explanation of how to perform restoration using the SCN (System Change Number), which can be thought of as Oracle’s internal timestamp.
What is FLASHBACK TABLE?
FLASHBACK TABLE is a feature that “rewinds” an entire table to a specific point in the past (defined by SCN or time).
Behind the scenes, Oracle uses “before-image” data stored in the UNDO tablespace to automatically perform reverse operations (e.g., an INSERT for a DELETE, or a reverse UPDATE for an UPDATE). This ensures the table is restored while maintaining logical consistency.
What is the Key “SCN (System Change Number)”?
The SCN acts like a “clock” inside the Oracle Database. It is a unique, ever-increasing number assigned every time a commit occurs, strictly managing the logical order of the database. Because it is more precise than a standard clock time (TIMESTAMP), it is highly effective when you want to revert to the exact moment immediately following a specific transaction.
Prerequisites for Executing Flashback
Before execution, always verify the following three points:
| Item | Content / Condition |
| Enable ROW MOVEMENT | Since the physical location of rows (ROWID) will change, this permission must be enabled. |
| UNDO Information Retention | Change information for the specified SCN must still exist in the UNDO tablespace (depends on the UNDO_RETENTION setting). |
| DDL Restrictions | No structural changes, such as DROP or TRUNCATE, must have been performed between the target recovery point and the present. |
Practice: Flashback Procedures Using SCN
Let’s walk through the flow of creating a table, deleting data, and then restoring it.
1. Preparation (Table Creation and Configuration)
The most important point is to enable ROW MOVEMENT to allow the flashback operation.
-- Create table
CREATE TABLE emp (
empno NUMBER PRIMARY KEY,
ename VARCHAR2(50)
);
-- Enable row movement (Required setting)
ALTER TABLE emp ENABLE ROW MOVEMENT;
2. Data Insertion and Recording the “Recovery Point”
Check the current SCN and make a note of it.
-- Register initial data
INSERT INTO emp VALUES (1, 'Suzuki');
COMMIT;
-- Check current SCN (Keep this number handy)
SELECT CURRENT_SCN FROM V$DATABASE;
-- Example: Assume 2275956 is displayed
3. Simulating an Accidental Operation
Delete the data and commit. At this point, a standard rollback cannot save the data.
-- Accidental operation: Delete all records
DELETE FROM emp;
COMMIT;
-- Verify (Data is gone)
SELECT * FROM emp;
4. Executing FLASHBACK TABLE
Specify the SCN (2275956) you noted earlier to execute the restoration.
-- Revert the table to the point of SCN 2275956
FLASHBACK TABLE emp TO SCN 2275956;
-- Verify
SELECT * FROM emp;
-- 'Suzuki' is back!
Execution Log Example:
SQL> CREATE TABLE emp (
2 empno NUMBER PRIMARY KEY,
3 ename VARCHAR2(50)
4 );
Table created.
SQL> ALTER TABLE emp ENABLE ROW MOVEMENT;
Table altered.
SQL> INSERT INTO emp VALUES (1, 'Suzuki');
1 row created.
SQL> col ename for a30
SQL> SELECT * FROM emp;
EMPNO ENAME
---------- ------------------------------
1 Suzuki
SQL> commit;
Commit complete.
SQL> SELECT CURRENT_SCN FROM V$DATABASE;
CURRENT_SCN
-----------
2275956 ★
SQL> DELETE FROM emp;
1 row deleted.
SQL> SELECT * FROM emp;
no rows selected
SQL> commit;
Commit complete.
SQL> FLASHBACK TABLE emp TO SCN 2275956; ★Specify SCN before deletion
Flashback complete.
SQL> SELECT * FROM emp;
EMPNO ENAME
---------- ------------------------------
1 Suzuki
Application: Specifying by TIMESTAMP (Time)
If the SCN is unknown, you can also restore data by specifying a time.
-- Revert to the state 5 minutes ago
FLASHBACK TABLE emp TO TIMESTAMP (SYSTIMESTAMP - INTERVAL '5' MINUTE);
Note: Internally, Oracle converts the TIMESTAMP to an SCN to process the request.
Troubleshooting: Common Errors
| Error Code | Cause | Solution |
| ORA-08189 | ROW MOVEMENT is disabled | Execute ALTER TABLE emp ENABLE ROW MOVEMENT;. |
| ORA-01466 | Data too old or DDL executed | Check if table structure was changed after the specified SCN. |
| ORA-08186 | UNDO info not found | Restoration is impossible because the data has exceeded the UNDO_RETENTION period. |
FAQ
Q: Can other users access the table while FLASHBACK TABLE is running?
A: No. An exclusive lock is placed on the target table during execution, so other DML operations will enter a wait state.
Q: What happens to indexes and triggers?
A: Indexes are maintained automatically. Triggers are disabled by default, though they can be set to fire using specific options.
Q: Can I restore only specific “rows”?
A: FLASHBACK TABLE reverts the entire table. If you only need specific rows, it is common to use a “Flashback Query” to extract them, such as:
INSERT INTO ... SELECT * FROM emp AS OF SCN ...
Summary
- Flashback Table is a “time machine” feature that rescues data from accidental operations.
ALTER TABLE ... ENABLE ROW MOVEMENT;is essential for execution.- Using SCN allows for extremely high-precision restoration to a specific point in time.
- Execution must happen within the UNDO tablespace retention period, making swift action the key to success.
While preventing mistakes is always best, mastering SCN checks and Flashback procedures ensures you are prepared for the worst-case scenario.
This article focuses on Oracle Database 19c (screens and default values may differ for other versions).
[reference]
Using Oracle Flashback Technology
コメント