How to Resolve ORA-00940: invalid ALTER command

  • by

ORA-00940

ORA-00940 means that you use an invalid object type for ALTER operation, usually it's because you misspelled the object type name.

First of all, we logged into the database as a normal user.

SQL> conn hr/hr@orclpdb
Connected.

Let's see some common error patterns.

1. Missing Keyword

ALTER TABLE

SQL> alter employees modify (employee_id number(8));
alter employees modify (employee_id number(8))
      *
ERROR at line 1:
ORA-00940: invalid ALTER command

ALTER INDEX

SQL> alter emp_emp_id_pk rebuild online;
alter emp_emp_id_pk rebuild online
      *
ERROR at line 1:
ORA-00940: invalid ALTER command

We forgot to use the keyword TABLE or INDEX after ALTER.

2. Misspelling Keyword

ALTER TABLE

SQL> alter tabel employees modify (employee_id number(8));
alter tabel employees modify (employee_id number(8))
      *
ERROR at line 1:
ORA-00940: invalid ALTER command

ALTER INDEX

SQL> alter indxe emp_emp_id_pk rebuild online;
alter indxe emp_emp_id_pk rebuild online
      *
ERROR at line 1:
ORA-00940: invalid ALTER command

We misspelled the keyword, it's pretty normal for developers.

3. Pluralizing Keyword

ALTER TABLE

SQL> alter tables employees modify (employee_id number(8));
alter tables employees modify (employee_id number(8))
      *
ERROR at line 1:
ORA-00940: invalid ALTER command

ALTER INDEX

SQL> alter indexes emp_emp_id_pk rebuild online;
alter indexes emp_emp_id_pk rebuild online
      *
ERROR at line 1:
ORA-00940: invalid ALTER command

We pluralized the keyword, which is unnecessary.

4. Correct Keyword to solve ORA-00940

The solution is to correct the keywords into the right ones.

ALTER TABLE

SQL> alter table employees modify (employee_id number(8));

Table altered.

ALTER INDEX

SQL> alter index emp_emp_id_pk rebuild online;

Index altered.

Complete and Correct ALTER

Here I list all ALTER syntax at every level or object. You may refer to the document: Oracle Database 19c SQL Language Reference for more information.

  • ALTER ANALYTIC VIEW
  • ALTER ATTRIBUTE DIMENSION
  • ALTER AUDIT POLICY
  • ALTER CLUSTER
  • ALTER DATABASE
  • ALTER DATABASE DICTIONARY
  • ALTER DATABASE LINK
  • ALTER DIMENSION
  • ALTER DISKGROUP
  • ALTER FLASHBACK ARCHIVE
  • ALTER FUNCTION
  • ALTER HIERARCHY
  • ALTER INDEX
  • ALTER INDEXTYPE
  • ALTER INMEMORY JOIN GROUP
  • ALTER JAVA
  • ALTER LIBRARY
  • ALTER LOCKDOWN PROFILE
  • ALTER MATERIALIZED VIEW
  • ALTER MATERIALIZED VIEW LOG
  • ALTER MATERIALIZED ZONEMAP
  • ALTER OPERATOR
  • ALTER OUTLINE
  • ALTER PACKAGE
  • ALTER PLUGGABLE DATABASE
  • ALTER PROCEDURE
  • ALTER PROFILE
  • ALTER RESOURCE COST
  • ALTER ROLE
  • ALTER ROLLBACK SEGMENT
  • ALTER SEQUENCE
  • ALTER SESSION
  • ALTER SYNONYM
  • ALTER SYSTEM
  • ALTER TABLE
  • ALTER TABLESPACE
  • ALTER TABLESPACE SET
  • ALTER TRIGGER
  • ALTER TYPE
  • ALTER USER
  • ALTER VIEW

In addition to ALTER syntax, I think you might be interested in reading more about following ALTER TABLE and ALTER INDEX syntaxes:

Leave a Reply

Your email address will not be published. Required fields are marked *