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 Valid ALTER
Here I list all valid 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
Anything else falls into ORA-00940.
In addition to ALTER syntax, I think you might be interested in reading more about following ALTER TABLE and ALTER INDEX syntaxes: