Oracle 12c删除表后无法创建同名表问题求助

Let's break down why you're hitting this error even though ALL_OBJECTS doesn't show WIP_DISCRETE_JOBS, and walk through actionable fixes:

1. The original table is stuck in the Recycle Bin #

Oracle 10g+ automatically moves dropped tables to the Recycle Bin (unless you use DROP TABLE ... PURGE). Even though the table isn't visible under its original name in ALL_OBJECTS, Oracle reserves the name to prevent conflicts with restored objects.

Check for Recycle Bin entries: #

SELECT original_name, object_name, type 
FROM user_recyclebin 
WHERE UPPER(original_name) = 'WIP_DISCRETE_JOBS';

Fix options: #

• Restore the original table directly (this preserves constraints, indexes, and triggers that your CREATE TABLE AS SELECT would lose):

  FLASHBACK TABLE WIP_DISCRETE_JOBS TO BEFORE DROP;
  

• Permanently delete the recycled table to free up the name:

  PURGE TABLE WIP_DISCRETE_JOBS;
  -- Or clear your entire recycle bin: PURGE RECYCLEBIN;
  

  Then run your CREATE TABLE WIP_DISCRETE_JOBS AS SELECT * FROM WDJ_BKP; again.

2. A non-table object with the same name exists in your schema #

ORA-00955 applies to any database object, not just tables. You might have a synonym, view, sequence, or stored procedure named WIP_DISCRETE_JOBS that you missed if you only filtered for tables in ALL_OBJECTS.

Check all object types: #

SELECT owner, object_name, object_type 
FROM all_objects 
WHERE UPPER(object_name) = 'WIP_DISCRETE_JOBS';

Fix: #

If you find a non-table object (e.g., a synonym), drop it first:

DROP SYNONYM WIP_DISCRETE_JOBS; -- Adjust the command based on the object type you find

Then proceed with restoring your table.

3. The object exists in another schema (or is a public synonym) #

If you're not querying with a user that has DBA privileges, ALL_OBJECTS might not show objects in other schemas. A table in another user's schema or a public synonym could be conflicting with your table creation.

Check across all schemas (requires DBA access): #

SELECT owner, object_name, object_type 
FROM dba_objects 
WHERE UPPER(object_name) = 'WIP_DISCRETE_JOBS';

Fix: #

• If it's a public synonym: DROP PUBLIC SYNONYM WIP_DISCRETE_JOBS;
• If it's another user's table: Either drop that table (if you have permissions) or explicitly specify your schema when creating the table (e.g., CREATE TABLE YOUR_SCHEMA.WIP_DISCRETE_JOBS AS SELECT * FROM WDJ_BKP;).

4. Case-sensitivity issues (unlikely but possible) #

Oracle stores object names in uppercase by default unless you created them with quoted identifiers. If you originally created WIP_DISCRETE_JOBS with quotes (e.g., "WIP_DISCRETE_JOBS"), you need to match the case exactly in your queries and create statement.

Verify case: #

SELECT object_name 
FROM all_objects 
WHERE object_name = '"WIP_DISCRETE_JOBS"';

Fix: #

If this returns a result, create the table with quoted identifiers:

CREATE TABLE "WIP_DISCRETE_JOBS" AS SELECT * FROM WDJ_BKP;

内容的提问来源于stack exchange，提问作者manu muraleedharan