ORA-00946
There could be many cases that throw ORA-00946. Let's see some samples.
Rename a Datafile
Tried to rename a file of database but it failed with ORA-00946.
SQL> alter database rename file '/oradata/ORCLCDB/ORCLPDB/example01.dbf' '/oradata/TESTCDB/ORCLPDB/example01.dbf';
alter database rename file '/oradata/ORCLCDB/ORCLPDB/example01.dbf' '/oradata/TESTCDB/ORCLPDB/example01.dbf'
*
ERROR at line 1:
ORA-00946: missing TO keyword
Rename a Table
When we renamed a table by RENAME TO syntax, we got ORA-00946.
SQL> rename employees employees_bak;
rename employees employees_bak
*
ERROR at line 1:
ORA-00946: missing TO keyword
Create Database Link
Also, you might see ORA-00946 when you create a database link.
SQL> create database link erpapp connect erp identified by erp using 'ORCLPDB';
create database link erpapp connect erp identified by erp using 'ORCLPDB'
*
ERROR at line 1:
ORA-00946: missing TO keyword
ORA-00946 means that the keyword TO is missing from the statement you issued, which causes a syntax error.
Solution
To solve ORA-00946, we should follow the error message to fix the problem. Here we take the first case for an example.
SQL> alter database rename file '/oradata/ORCLCDB/ORCLPDB/example01.dbf' to '/oradata/TESTCDB/ORCLPDB/example01.dbf';
Database altered.
Adding TO to the correct position in the statement is the key to solve ORA-00946.