How to Resolve ORA-02000: missing keyword

ORA-02000

There could be many types of error patterns to throw ORA-02000.

Missing DATAFILES
Missing LIMIT

ORA-02000 means that there's a keyword missing from your statement, even though it looks like no problem.

Missing DATAFILES

Tried to drop a pluggable database PDB completely from the container, but it failed with ORA-02000.


SQL> drop pluggable database ERPAPP2 including datafile;

drop pluggable database ERPAPP2 including datafile

                                           *

ERROR at line 1:

ORA-02000: missing DATAFILES keyword

We forgot to pluralize the last keyword, it should be DATAFILES.

Similarly, we may accidentally split the keyword.


SQL> drop pluggable database ERPAPP2 including data files;

drop pluggable database ERPAPP2 including data files

                                           *

ERROR at line 1:

ORA-02000: missing DATAFILES keyword

DATAFILES is a one-word keyword, not a noun phrase.


SQL> drop pluggable database ERPAPP2 including datafiles;



Pluggable database dropped.

Missing LIMIT

Tried to create a new profile for application users to use, but it failed with ORA-02000.

Default Values


SQL> create profile app_user;

create profile app_user

                      *

ERROR at line 1:

ORA-02000: missing LIMIT keyword

Customized Values


SQL> create profile app_user password_grace_time 3;

create profile app_user password_grace_time 3

                        *

ERROR at line 1:

ORA-02000: missing LIMIT keyword

To solve ORA-02000 in CREATE PROFILE statement, we specify the keyword LIMIT even though we accept all default values when creating a new profile.


SQL> create profile app_user limit;



Profile created.


SQL> create profile app_user limit password_grace_time 3;



Profile created.

Done!