使用Oracle SQL闪回恢复已删除表fixer时遇ORA-00905错误求助

Hey there, let's work through that ORA-00905 "missing keyword" error you're hitting when trying to recover your dropped fixer table. This error almost always boils down to a syntax mistake in your flashback command—let's break down the right way to do this, plus a few edge cases to check.

First: Use the Correct Flashback Drop Syntax #

If you dropped the fixer table and want to restore it from the recycle bin, the proper command requires the full set of keywords. A common mistake is omitting part of the clause, which triggers the missing keyword error.

Here's the correct basic command:

FLASHBACK TABLE fixer TO BEFORE DROP;

Notes on Table Names: #

• Oracle defaults to uppercase table names, so if you created the table with lowercase letters (using double quotes), you'll need to match that in your command:

  FLASHBACK TABLE "fixer" TO BEFORE DROP;
  

If the Table Was Renamed in the Recycle Bin #

Oracle renames dropped tables in the recycle bin to a system-generated name like BIN$xxxxxx$0. If you get an error saying the table doesn't exist, first check what's in your recycle bin:

SELECT original_name, object_name FROM user_recyclebin WHERE original_name = 'FIXER';

Then use the recycle bin object name to flashback:

FLASHBACK TABLE "BIN$xxxxxx$0" TO BEFORE DROP;

If You're Trying to Flashback to a Specific Time (Not Undo a Drop) #

If your goal isn't to recover a dropped table but to revert the table to a previous state, the syntax is different—make sure you don't miss the required keywords here either. For example, using a timestamp:

FLASHBACK TABLE fixer TO TIMESTAMP TO_TIMESTAMP('2024-05-20 14:30:00', 'YYYY-MM-DD HH24:MI:SS');

Or using an SCN (System Change Number):

FLASHBACK TABLE fixer TO SCN 1234567;

Quick Checks to Rule Out Other Issues #

• Permissions: Ensure you have the FLASHBACK ANY TABLE system privilege, or the FLASHBACK object privilege on the fixer table (though this usually throws a different error than ORA-00905).
• Recycle Bin Status: The recycle bin must be enabled (it's on by default, but if it was disabled, you can't use flashback drop). Check with:

  SHOW PARAMETER recyclebin;
  

内容的提问来源于stack exchange，提问作者Masum Billah