ORA-02231
ORA-02231 means that a keyword is missing from the position where it should be in ALTER DATABASE statement. Most likely, it's a syntax error.
Since the error is very broadly used by ALTER DATABASE statement, you could never know what mistake you made. In this post, we'll show you some error patterns of ORA-02231.
- Rename Database File
- Rename Temporary File
- Force Log Switch
- Enable Archivelog Mode
- Default Permanent Tablespace
- Default Temporary Tablespace
Rename Database File
Tried to rename a datafile, but it failed with ORA-02231.
SQL> alter database rename datafile '/oradata/ORCLCDB/ORCLPDB/CRM_TBS_01.dbf' to '/oradata/ORCLCDB/ORCLPDB/ERP_TBS_01.dbf';
alter database rename datafile '/oradata/ORCLCDB/ORCLPDB/CRM_TBS_01.dbf' to '/oradata/ORCLCDB/ORCLPDB/ERP_TBS_01.dbf'
*
ERROR at line 1:
ORA-02231: missing or invalid option to ALTER DATABASE
In fact, there's not RENAME DATAFILE clause in ALTER DATABASE statement, the only valid clause is RENAME FILE.
SQL> alter database rename file '/oradata/ORCLCDB/ORCLPDB/CRM_TBS_01.dbf' to '/oradata/ORCLCDB/ORCLPDB/ERP_TBS_01.dbf';
Database altered.
By using RENAME FILE clause, we can rename not only datafiles, but also tempfiles and logfiles.
Rename Temporary File
Tried to rename a tempfile, but it failed with ORA-02231.
SQL> alter database rename tempfile '/oradata/ORCLCDB/ORCLPDB/TEMP02.dbf' to '/oradata/ORCLCDB/ORCLPDB/SYSTMP02.dbf';
alter database rename tempfile '/oradata/ORCLCDB/ORCLPDB/TEMP02.dbf' to '/oradata/ORCLCDB/ORCLPDB/SYSTMP02.dbf'
*
ERROR at line 1:
ORA-02231: missing or invalid option to ALTER DATABASE
In fact, there's not RENAME TEMPFILE clause in ALTER DATABASE statement, the only valid clause is RENAME FILE.
SQL> alter database rename file '/oradata/ORCLCDB/ORCLPDB/TEMP02.dbf' to '/oradata/ORCLCDB/ORCLPDB/SYSTMP02.dbf';
Database altered.
By using RENAME FILE clause, we can rename database files, no matter what the files are datafiles, tempfiles or logfiles.
Force Log Switch
Tried to manually force a log switch so as to archive the current redo log file, but it failed with ORA-02231.
SQL> alter database archive log current;
alter database archive log current
*
ERROR at line 1:
ORA-02231: missing or invalid option to ALTER DATABASE
In fact, archiving a redo log manually, you should do it by ALTER SYSTEM statement, not ALTER DATABASE statement.
SQL> alter system archive log current;
System altered.
Further reading: ALTER SYSTEM SWITCH LOGFILE vs ARCHIVE LOG CURRENT.
Enable Archivelog Mode
Tried to enable archive log mode, but it failed with this error.
SQL> alter database archive log;
alter database archive log
*
ERROR at line 1:
ORA-02231: missing or invalid option to ALTER DATABASE
In fact, keywords ARCHIVE and LOG should be combined as one keyword without spacing.
SQL> alter database archivelog;
Database altered.
The correct keyword is ARCHIVELOG.
Default Permanent Tablespace
Tried to switch the default permanent tablespace to another one, but it failed with ORA-02231.
SQL> alter database tablespace example;
alter database temporary tablespace example
*
ERROR at line 1:
ORA-02231: missing or invalid option to ALTER DATABASE
In fact, there's missing a keyword DEFAULT before TABLESPACE.
SQL> alter database default tablespace example;
Database altered.
Default Temporary Tablespace
Tried to switch the default temporary tablespace to another one, but it failed with ORA-02231.
SQL> alter database temporary tablespace temp1;
alter database temporary tablespace temp1
*
ERROR at line 1:
ORA-02231: missing or invalid option to ALTER DATABASE
In fact, there's missing a keyword DEFAULT before TEMPORARY.
SQL> alter database default temporary tablespace temp1;
Database altered.
As you can see, there're too many possibilities that throw ORA-02231.