ORA-00933
ORA-00933 means that you use an unexpected word or phrase at the position where SQL parser knows what qualified keyword should be. Use cases that throw ORA-00933 may broadly vary. Here're some cases of the error.
ALTER TABLESPACE
Tried to add a tempfile to a tablespace, but it failed with ORA-00933.
SQL> alter tablespace temp add tempfile '+DATA/ORCLCDB/ERPAPP2A/TEMP32.dbf' size 10m auto extend on next 10m maxsize unlimited;
alter tablespace temp add tempfile '+DATA/ORCLCDB/ERPAPP2A/TEMP32.dbf' size 10m auto extend on next 10m maxsize unlimited
*
ERROR at line 1:
ORA-00933: SQL command not properly ended
Solution
Actually, the keyword is AUTOEXTEND, not AUTO EXTEND. We should correct the statement like this:
SQL> alter tablespace temp add tempfile '+DATA/ORCLCDB/ERPAPP2A/TEMP32.dbf' size 10m autoextend on next 10m maxsize unlimited;
Tablespace altered.
We solve it.
To correctly operate temporary tablespace, you may refer to the post: How to Alter Temporary Tablespace.
Create Table as Select (CTAS)
Tried to backup a table to a another tablespace by CTAS, but it failed with ORA-00933.
SQL> create table hr.employees_bak as select * from hr.employees tablespace example;
create table hr.employees_bak as select * from hr.employees tablespace example
*
ERROR at line 1:
ORA-00933: SQL command not properly ended
Solution
This is because the TABLESPACE clause cannot be at the last, it should stick with CREATE TABLE clause
SQL> create table hr.employees_bak tablespace example as select * from hr.employees;
Table created.
Substitution Variable
There're hundreds of tables like pro001env, pro002env, ... and so on.
Let's see one of these tables.
SQL> desc pro220env
Name Null? Type
----------------------------------------- -------- ----------------------------
C1 NUMBER
When we want to use a substitution variable to execute the statement, it failed with ORA-00933.
SQL> select * from pro'&num'env;
Enter value for num: 220
old 1: select * from pro'&num'env
new 1: select * from pro'220'env
select * from pro'220'env
*
ERROR at line 1:
ORA-00933: SQL command not properly ended
As you can see, we use single quotes to isolate the variable, but it's not working.
Solution
To use substitution variable to concatenate a string, you need a period (.) to separate the substitution variable from rest of characters.
SQL> select * from pro&num.env;
Enter value for num: 220
old 1: select * from pro&num.env
new 1: select * from pro220env
C1
----------
1
2
3
It works!
By the way, the use case of substitution variables is different from the scenarios of using bind variables.