Skip to content

How to Resolve ORA-02000: missing keyword

  • by

ORA-02000

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

  1. Missing DATAFILES
  2. 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!

Leave a Reply

Your email address will not be published. Required fields are marked *