ORA-00955 Name Is Already Used by an Existing Object

ORA-00955: Name Is Already Used by an Existing Object

Error Overview

Error Text: ORA-00955: name is already used by an existing object

The ORA-00955 error occurs when attempting to create a database object (table, view, index, sequence, synonym, etc.) using a name that already exists in the same schema or namespace. Oracle requires unique names for objects within the same namespace.

Common Causes

1. Duplicate Object Creation

• Running CREATE statements multiple times
• Re-executing deployment scripts without DROP statements
• Conflicting object names from different developers

2. Schema Migration Issues

• Importing data without proper cleanup
• Partial rollback of schema changes
• Testing scripts in production schemas

3. Namespace Confusion

• Not understanding Oracle’s namespace rules
• Forgetting about existing synonyms or views
• Case sensitivity misunderstandings

4. Application Deployment Errors

• Missing IF NOT EXISTS logic (not natively supported in Oracle)
• Incorrect deployment order
• Failed previous deployments leaving orphaned objects

Diagnostic Queries

Find Existing Object with Same Name

-- Search for object by name
SELECT owner, object_name, object_type, status, created
FROM dba_objects
WHERE object_name = UPPER('&object_name')
ORDER BY owner, object_type;

-- Search in current schema
SELECT object_name, object_type, status, created, last_ddl_time
FROM user_objects
WHERE object_name = UPPER('&object_name');

Check All Namespaces

-- Objects share namespaces: tables, views, sequences,
-- private synonyms, stand-alone procedures, functions, packages
SELECT
  object_type,
  object_name,
  status,
  created
FROM user_objects
WHERE object_name = UPPER('&object_name')
UNION ALL
SELECT
  'PUBLIC SYNONYM' as object_type,
  synonym_name,
  'VALID' as status,
  NULL as created
FROM all_synonyms
WHERE synonym_name = UPPER('&object_name')
  AND owner = 'PUBLIC';

Find Object Dependencies

-- Check if object has dependencies
SELECT
  name, type, referenced_owner, referenced_name, referenced_type
FROM dba_dependencies
WHERE referenced_name = UPPER('&object_name')
ORDER BY name;

-- Check for synonyms pointing to the object
SELECT owner, synonym_name, table_owner, table_name
FROM dba_synonyms
WHERE table_name = UPPER('&object_name');

Resolution Steps

1. Identify and Review Existing Object

-- Get full object information
SELECT
  owner,
  object_name,
  object_type,
  status,
  created,
  last_ddl_time
FROM dba_objects
WHERE object_name = UPPER('EMPLOYEES')
ORDER BY owner, object_type;

-- For tables, check structure
DESC schema_name.table_name;

-- For views, check definition
SELECT text FROM dba_views
WHERE view_name = UPPER('VIEW_NAME');

2. Drop Existing Object (If Safe)

-- Drop table (with cascade for dependent objects)
DROP TABLE schema_name.table_name CASCADE CONSTRAINTS PURGE;

-- Drop view
DROP VIEW schema_name.view_name;

-- Drop sequence
DROP SEQUENCE schema_name.sequence_name;

-- Drop index
DROP INDEX schema_name.index_name;

-- Drop synonym
DROP SYNONYM schema_name.synonym_name;
DROP PUBLIC SYNONYM synonym_name;  -- Requires privileges

3. Rename Existing Object

-- Rename table
ALTER TABLE old_table_name RENAME TO new_table_name;

-- Rename index
ALTER INDEX old_index_name RENAME TO new_index_name;

-- Rename sequence
RENAME old_sequence_name TO new_sequence_name;

-- Cannot rename views - must drop and recreate

4. Use CREATE OR REPLACE (Where Supported)

-- Works for views, procedures, functions, packages, triggers
CREATE OR REPLACE VIEW employee_summary AS
SELECT department_id, COUNT(*) as emp_count
FROM employees
GROUP BY department_id;

CREATE OR REPLACE PROCEDURE process_data AS
BEGIN
  -- procedure code
  NULL;
END;
/

CREATE OR REPLACE TRIGGER audit_trigger
AFTER INSERT ON employees
FOR EACH ROW
BEGIN
  -- trigger code
  NULL;
END;
/

5. Implement Safe DDL Scripts

-- Check before creating table
DECLARE
  v_count NUMBER;
BEGIN
  SELECT COUNT(*) INTO v_count
  FROM user_tables
  WHERE table_name = 'NEW_TABLE';

  IF v_count = 0 THEN
    EXECUTE IMMEDIATE 'CREATE TABLE new_table (
      id NUMBER PRIMARY KEY,
      name VARCHAR2(100)
    )';
    DBMS_OUTPUT.PUT_LINE('Table created successfully');
  ELSE
    DBMS_OUTPUT.PUT_LINE('Table already exists - skipping');
  END IF;
END;
/

-- Check before creating sequence
DECLARE
  v_count NUMBER;
BEGIN
  SELECT COUNT(*) INTO v_count
  FROM user_sequences
  WHERE sequence_name = 'NEW_SEQ';

  IF v_count = 0 THEN
    EXECUTE IMMEDIATE 'CREATE SEQUENCE new_seq START WITH 1';
  END IF;
END;
/

Prevention Strategies

1. Use Naming Conventions

-- Prefix objects by type and application
-- Tables: TBL_APP_ENTITY or APP_ENTITY
-- Views: VW_APP_ENTITY
-- Indexes: IDX_TABLE_COLUMNS
-- Sequences: SEQ_TABLE_ID
-- Triggers: TRG_TABLE_ACTION
-- Packages: PKG_APP_MODULE

2. Create Deployment Framework

-- Example deployment wrapper
CREATE OR REPLACE PROCEDURE deploy_object(
  p_object_type VARCHAR2,
  p_object_name VARCHAR2,
  p_ddl_statement VARCHAR2,
  p_drop_first BOOLEAN DEFAULT FALSE
) AS
  v_count NUMBER;
  v_exists BOOLEAN;
BEGIN
  -- Check if object exists
  SELECT COUNT(*) INTO v_count
  FROM user_objects
  WHERE object_name = UPPER(p_object_name)
    AND object_type = UPPER(p_object_type);

  v_exists := (v_count > 0);

  IF v_exists AND p_drop_first THEN
    EXECUTE IMMEDIATE 'DROP ' || p_object_type || ' ' || p_object_name;
    v_exists := FALSE;
  END IF;

  IF NOT v_exists THEN
    EXECUTE IMMEDIATE p_ddl_statement;
    DBMS_OUTPUT.PUT_LINE('Created: ' || p_object_type || ' ' || p_object_name);
  ELSE
    DBMS_OUTPUT.PUT_LINE('Skipped (exists): ' || p_object_type || ' ' || p_object_name);
  END IF;
END;
/

3. Document Object Inventory

-- Generate object inventory report
SELECT
  object_type,
  COUNT(*) as object_count
FROM user_objects
GROUP BY object_type
ORDER BY object_count DESC;

-- Export object list for documentation
SELECT object_name || ',' || object_type || ',' || status
FROM user_objects
ORDER BY object_type, object_name;

Oracle Namespace Rules

Understanding Oracle’s namespace helps prevent conflicts:

Namespace	Objects
Schema Object	Tables, Views, Sequences, Private Synonyms, Standalone Procedures/Functions, Packages, Materialized Views, User-defined Types
Constraint	Constraints (must be unique within schema)
Index	Indexes (must be unique within schema)
Trigger	Triggers (must be unique within schema)
Cluster	Clusters
Database Link	Database Links
Dimension	Dimensions

Related Errors

• ORA-00942 - Table or view does not exist
• ORA-00972 - Identifier is too long
• ORA-01031 - Insufficient privileges

Emergency Response

Quick Object Check

-- Fast check for object existence
SELECT 1 FROM dual
WHERE EXISTS (
  SELECT 1 FROM user_objects
  WHERE object_name = UPPER('&object_name')
);

-- Find owner of conflicting object
SELECT owner, object_type
FROM dba_objects
WHERE object_name = UPPER('&object_name');

Safe Script Pattern

-- Use exception handling for deployment
BEGIN
  EXECUTE IMMEDIATE 'CREATE TABLE test_table (id NUMBER)';
EXCEPTION
  WHEN OTHERS THEN
    IF SQLCODE = -955 THEN
      DBMS_OUTPUT.PUT_LINE('Object already exists - continuing');
    ELSE
      RAISE;
    END IF;
END;
/