Oracle 12c R2 Error Codes and Solution Suggestions from ORA-02140 to ORA-02300
- ORA-02140: invalid tablespace name
Cause: An identifier does not follow ALTER TABLESPACE.
Action: Specify a tablespace name following ALTER TABLESPACE.
Cause: An option other than NORMAL or IMMEDIATE follows OFFLINE.
Action: The user must either specify no option following OFFLINE or one of the options NORMAL or IMMEDIATE.
Cause: A valid option was not present.
Action: Use one of the valid options: add, rename, default, online, offline, read only, read write, begin, end, no, force, retention guarantee and retention noguarantee.
Cause: An option other than INITIAL, NEXT, MINEXTENTS, MAXEXTENTS, or PCTINCREASE was specified in the STORAGE clause.
Action: Specify only valid options.
Cause: No ALTER CLUSTER options are specified.
Action: Specify one or more of the following options: pctfree, pctused, size, storage.
Cause: No STORAGE options were specified following STORAGE (
Action: Specify one or more STORAGE option between the parentheses.
Cause: The SHARED option was specified in a CREATE DATABASE statement multiple times.
Action: Only specify the SHARED option once.
Cause: Both the SHARED and EXCLUSIVE options were specified in a CREATE DATABASE statement.
Action: Specify SHARED or EXCLUSIVE, but not both.
Cause: The EXCLUSIVE option was specified in a CREATE DATABASE statement multiple times.
Action: Only specify the EXCLUSIVE option once.
Cause: Partition not found for the object.
Action: Retry with correct partition name.
Cause: The new tablespace name specified in ALTER TABLESPACE RENAME TO statement was invalid.
Action: Retry with a valid new tablespace name.
Cause: Oracle cannot create a tablespace whose name starts with ‘_$deleted$’.
Action: Try a different tablespace name.
Cause: An option other than DATAFILE or TO follows by ALTER TABLESPACE … RENAME.
Action: The user must specify either DATAFILE or TO following ALTER TABLESPACE … RENAME.
Cause: An encoded password string does not follow the VALUES clause.
Action: Place a proper encoded password string after the VALUES clause.
Cause: An attempt to rename a tablespace to a new name failed because the new name is already used by some other tablespace.
Action: Retry with a different new name.
Cause: An identifier does not follow DEFAULT TABLESPACE.
Action: Place a tablespace name after DEFAULT TABLESPACE.
Cause: An identifier does not follow TEMPORARY TABLESPACE.
Action: Place a tablespace name after TEMPORARY TABLESPACE.
Cause: No options were specified.
Action: Specify at least one ALTER USER option.
Cause: An option other than COMPRESS, NOCOMPRESS, PCTFREE, INITRANS, MAXTRANS, STORAGE, TABLESPACE, PARALLEL, NOPARALLEL, RECOVERABLE, UNRECOVERABLE, LOGGING, NOLOGGING, LOCAL, or GLOBAL was specified.
Action: Choose one of the valid CREATE INDEX options.
Cause: The parameter file specified gc_* parameters that allow locks to be release by the LCK process when not in use. This mode requires additional support from the DLM that is not available.
Action: Specify configuration parameters that do not require the additional function.
Cause: A column of type LONG defined for index-organized table.
Action: Do not use columns of type LONG in index-organized tables.
Cause: A number does not follow MAXLOGFILES.
Action: Specify a number after MAXLOGFILES.
Cause: A number does not follow MAXDATAFILES.
Action: Specify a number after MAXDATAFILES.
Cause: A number does not follow FREELIST GROUPS.
Action: Specify a number after FREELIST GROUPS.
Cause: The CREATE DATABASE command contains more than one DATAFILE clause.
Action: Specify at most one DATAFILE clause.
Cause: An invalid CREATE DATABASE option is present.
Action: Specify only valid CREATE DATABASE options.
Cause: Both ARCHIVELOG and NOARCHIVELOG are specified in a CREATE DATABASE statement.
Action: Specify at most one of these two options.
Cause: The CREATE DATABASE command contains more than one LOGFILE clause.
Action: Specify at most one LOGFILE clause.
Cause: A number does not follow FREELISTS
Action: Specify a number after FREELISTS
Cause: The user attempted to specify the FREELISTS storage option. This option may only be specified during create table or create index.
Action: Remove these options and retry the statement.
Cause: The user attempted to specify the FREELIST GROUPS storage option. This option may only be specified during create table and when allow_freelist_groups INIT.ORA is specified.
Action: Remove this option and retry the statement or set the allow_freelist_groups INIT.ORA parameter.
Cause: A number does not follow MAXLOGHISTORY
Action: Specify a number after MAXLOGHISTORY
Cause: The PUBLIC keyword was specified for a disable.
Action: Remove the keyword and resubmit.
Cause: Either a token other than INCLUDING was found following the tablespace name or some text was found following INCLUDING CONTENTS.
Action: Place nothing or only INCLUDING CONTENTS after the tablespace name.
Cause: Must specify thread number after THREAD keyword
Action: n/a
Cause: In the CREATE or DROP ROLLBACK SEGMENT statements, an identifier was not found following ROLLBACK SEGMENT.
Action: Place the segment name following ROLLBACK SEGMENT.
Cause: An invalid option was specified in a CREATE ROLLBACK SEGMENT statement.
Action: Specify one of the valid options: TABLESPACE and STORAGE.
Cause: Must specify group number after GROUP keyword
Action: n/a
Cause: There is a syntax error in the user’s statement.
Action: Correct the syntax as indicated.
Cause: There is a syntax error in the user’s statement.
Action: Correct the syntax as indicated.
Cause: An invalid option appeared.
Action: Specify one of the valid options: DATAFILE, DEFAULT STORAGE, ONLINE, OFFLINE, FORCE, RETENTION, IN SHARD.
Cause: A token other than TO follows ROLLBACK [WORK].
Action: Place nothing or TO SAVEPOINT name after ROLLBACK [WORK].
Cause: An identifier does not follow ROLLBACK [WORK] TO [SAVEPOINT].
Action: Place a savepoint name following TO [SAVEPOINT].
Cause: There is a syntax error in the user’s statement.
Action: Correct the syntax as indicated.
Cause: In a revoke statement, a resource quota was specified.
Action: Specify resource privilege without the quota.
Cause: A token other than WORK follows COMMIT.
Action: Place either nothing or WORK after COMMIT.
Cause: An attempt was made to grant or revoke a resource quota in the same statement in which other privileges are granted or revoked.
Action: Use a separate grant or revoke statement for the resource quota privilege.
Cause: The user attempted to grant a tablespace quota above the upper limit.
Action: Grant a smaller tablespace quota.
Cause: An attempt was made to publicly enable a thread associated with an instance.
Action: Leave out the PUBLIC keyword.
Cause: In a grant or revoke statement, the user specified a tablespace quota but did not follow it with the ON tablespace clause.
Action: Specify the tablespace on which the quota is to be applied with the ON tablespace clause.
Cause: The keyword TABLES is expected following DROP CLUSTER name INCLUDING.
Action: Place TABLES after INCLUDING.
Cause: There is a syntax error in the user’s statement.
Action: Correct the syntax as indicated.
Cause: pctincrease was specified in a create or alter rollback segment
Action: reissue statement without the pctincrease clause
Cause: There is a syntax error in an event specification.
Action: Fix the error.
Cause: The object type is inconsistent with a tablespace contents.
Action: Create an object in a different tablespace, or change the user defaults.
Cause: In CREATE TABLESPACE, the PERMANENT and/or TEMPORARY options were specified more than once.
Action: n/a
Cause: In CREATE TABLESPACE, more than one DATAFILE/TEMPFILE clause was specified.
Action: Merge the DATAFILE/TEMPFILE clauses into a single clause.
Cause: In CREATE TABLESPACE, the ONLINE and/or OFFLINE options were specified more than once.
Action: Specify at most one of ONLINE or OFFLINE.
Cause: A CREATE TABLESPACE statement has no DATAFILE/TEMPFILE clause.
Action: Specify a DATAFILE/TEMPFILE clause.
Cause: An attempt was made to GRANT to PUBLIC WITH GRANT OPTION.
Action: Remove the WITH GRANT OPTION clause.
Cause: An attempt was made to reference a sequence in a from-list.
Action: A sequence can only be referenced in a select-list.
Cause: An attempt was made to create a table in a cluster which already contains 32 tables.
Action: Up to 32 tables may be stored per cluster.
Cause: The user attempted to alter the INITIAL storage option of a table, cluster, index, or rollback segment. These options may only be specified when the object is created.
Action: Remove these options and retry the statement.
Cause: An attempt was made to grant or revoke an invalid privilege on a view.
Action: Do not attempt to grant or revoke any of ALTER, INDEX, or EXECUTE privileges on views.
Cause: An attempt was made to grant or revoke an invalid privilege on a sequence.
Action: Do not attempt to grant or revoke DELETE, INDEX, INSERT, UPDATE, REFERENCES or EXECUTE privilege on sequences.
Cause: INITRANS is specified more than once.
Action: Specify INITRANS at most once.
Cause: The INITRANS value is not an integer between 1 and 255 and less than or equal to the MAXTRANS value.
Action: Choose a valid INITRANS value.
Cause: MAXTRANS is specified more than once.
Action: Specify MAXTRANS at most once.
Cause: The MAXTRANS value is not an integer between 1 and 255 and greater than or equal to the INITRANS value.
Action: Choose a valid MAXTRANS value.
Cause: No ALTER TABLE option was specified.
Action: Specify at least one alter table option.
Cause: The specified value for PCTFREE or PCTUSED is not an integer between 0 and 100.
Action: Choose an appropriate value for the option.
Cause: PCTFREE option specified more than once.
Action: Specify PCTFREE at most once.
Cause: PCTUSED option specified more than once.
Action: Specify PCTUSED at most once.
Cause: The BACKUP option to ALTER TABLE is specified more than once.
Action: Specify the option at most once.
Cause: There is more than one TABLESPACE clause in the CREATE TABLE, CREATE INDEX, or CREATE ROLLBACK SEGMENT statement.
Action: Specify at most one TABLESPACE clause.
Cause: A tablespace name was not present where required by the syntax for the failing statement.
Action: Specify a tablespace name where required by the syntax.
Cause: A storage option (INIITAL, NEXT, MINEXTENTS, MAXEXTENTS, PCTINCREASE) is specified more than once.
Action: Specify all storage options at most once.
Cause: The specified value must be an integer.
Action: Choose an appropriate integer value.
Cause: The specified value must be an integer.
Action: Choose an appropriate integer value.
Cause: The specified value must be a positive integer less than or equal to MAXEXTENTS.
Action: Specify an appropriate value.
Cause: The specified value must be a positive integer greater than or equal to MINEXTENTS.
Action: Specify an appropriate value.
Cause: The specified value must be a positive integer.
Action: Specify an appropriate value.
Cause: The specified value must be an integer.
Action: Choose an appropriate integer value.
Cause: An attempt was made to grant or revoke an invalid privilege on a table.
Action: Do not attempt to grant or revoke EXECUTE privilege on tables.
Cause: An attempt was made to grant or revoke an invalid privilege on a procedure, function or package.
Action: Do not attempt to grant or revoke any privilege besides EXECUTE or DEBUG on procedures, functions or packages.
Cause: The MAXEXTENTS specified is too large for the database block size. This applies only to SYSTEM rollback segment.
Action: Specify a smaller value.
Cause: A cluster name of the form [ identifier . ] identifier is expected but not present.
Action: Enter an appropriate cluster name.
Cause: The SIZE option is specified more than once.
Action: Specify the SIZE option at most once.
Cause: The specified value must be an integer number of bytes.
Action: Specify an appropriate value.
Cause: An option other than PCTFREE, PCTUSED, INITRANS, MAXTRANS, STORAGE, or SIZE is specified in an ALTER CLUSTER statement.
Action: Specify only legal options.
Cause: An option other than ADD, DROP, RENAME, ARCHIVELOG, NOARCHIVELOG, MOUNT, DISMOUNT, OPEN, or CLOSE is specified in the statement.
Action: Specify only legal options.
Cause: A mode other than SHARED or EXCLUSIVE follows the MOUNT keyword in an ALTER DATABASE statement.
Action: Specify either SHARED, EXCLUSIVE, or nothing following MOUNT.
Cause: A mode other than NORMAL or IMMEDIATE follows the CLOSE keyword in an ALTER DATABASE statement.
Action: Specify either NORMAL, IMMEDIATE, or nothing following CLOSE.
Cause: the log table to be added is a duplicate of another
Action: Don’t add this change log to the system; check that the replication product’s system tables are consistent
Cause: the table to be altered is already a change log for another table
Action: Don’t log changes to the specified base table to this table; check that the replication product’s system tables are consistent
Cause: A character string literal was not used in the filename list of a LOGFILE, DATAFILE, or RENAME clause.
Action: Use correct syntax.
Cause: A non-integer value was specified in the SIZE or RESIZE clause.
Action: Use correct syntax.
Cause: In a RENAME clause in ALTER DATABASE or TABLESPACE, the the number of existing filenames does not equal the number of new filenames.
Action: Make sure there is a new filename to correspond to each existing filename.
Cause: the sequence to be dropped is still referenced
Action: Make sure the sequence name is correct, or drop the referencing constraint/object
Cause: A number does not follow either OBJNO or TABNO.
Action: Specify a number after OBJNO or TABNO.
Cause: bad extent storage clause
Action: respecify
Cause: No options specified.
Action: Specify at least one of REBUILD, INITRANS, MAXTRANS, or STORAGE.
Cause: An option other than INITRANS, MAXTRANS,or STORAGE is specified in an ALTER INDEX statement or in the USING INDEX clause of an ALTER MATERIALIZED VIEW statement.
Action: Specify only legal options.
Cause: The STORAGE option is expected but not found.
Action: Specify the STORAGE option.
Cause: An identifier was expected, but not found, following ALTER [PUBLIC] ROLLBACK SEGMENT.
Action: Place a rollback segment name following SEGMENT.
Cause: A character string literal was expected, but not found, following ALTER SESSION SET EVENTS.
Action: Place the string literal containing the events text after EVENTS.
Cause: The option SET EVENTS was expected, but not found, following ALTER SESSION.
Action: Place the SET EVENTS option after ALTER SESSION.
Cause: Obvious.
Action: see SQL Language Manual for legal options.
Cause: A valid number does not follow MAXLOGMEMBERS. The value specified must be between 1 and the port-specific maximum number of log file members.
Action: Specify a valid number after MAXLOGMEMBERS.
Cause: The constraint name is missing or invalid.
Action: Specify a valid identifier name for the constraint name.
Cause: Subquery is not allowed here in the statement.
Action: Remove the subquery from the statement.
Cause: The specified search condition for the check constraint is not properly ended.
Action: End the condition properly.
Cause: Constraint specification is not allowed here in the statement.
Action: Remove the constraint specification from the statement.
Cause: Default value expression is not allowed for the column here in the statement.
Action: Remove the default value expression from the statement.
Cause: Was that defaults must not conflict with not null constraints
Action: n/a
Cause: The number of columns in the foreign-key referencing list is not equal to the number of columns in the referenced list.
Action: Make sure that the referencing columns match the referenced columns.
Cause: The number of columns in the key list exceeds the maximum number.
Action: Reduce the number columns in the list.
Cause: Self-evident.
Action: Remove the duplicate or conflicting specification.
Cause: Self-evident.
Action: Remove the duplicate specification.
Cause: Self-evident.
Action: Remove the extra primary key.
Cause: Self-evident.
Action: Remove the extra key.
Cause: New column datatype causes type-checking error for existing column default value expression.
Action: Remove the default value expression or don’t alter the column datatype.
Cause: The required datatype for the column is missing.
Action: Specify the required datatype.
Cause: The specified constraint name has to be unique.
Action: Specify a unique constraint name for the constraint.
Cause: The datatype of the referenced column is not defined as yet.
Action: Make sure that the datatype of the referenced column is defined before referencing it.
Cause: An attempt was made to truncate a table with unique or primary keys referenced by foreign keys enabled in another table. Other operations not allowed are dropping/truncating a partition of a partitioned table or an ALTER TABLE EXCHANGE PARTITION.
Action: Before performing the above operations the table, disable the foreign key constraints in other tables. You can see what constraints are referencing a table by issuing the following command: SELECT * FROM USER_CONSTRAINTS WHERE TABLE_NAME = “tabnam”;
Cause: The data type or collation of the referencing column was incompatible with the data type or collation of the referenced column.
Action: Select a compatible data type for the referencing column. Also, the collation of a character column in a foreign key must match the collation of the corresponding column in the primary key.
Cause: The referenced table does not have a primary key.
Action: Specify explicitly the referenced table unique key.
Cause: Self-evident.
Action: Change the datatype of the column, or remove the column from the key.
Cause: A REFERENCES clause in a CREATE/ALTER TABLE statement gives a column-list for which there is no matching unique or primary key constraint in the referenced table.
Action: Find the correct column names using the ALL_CONS_COLUMNS catalog view
Cause: Self-evident.
Action: Make sure the specified constraint name is correct.
Cause: Self-evident.
Action: Change the datatype of the column, or remove the constraint on the column.
Cause: Self-evident.
Action: Remove all references to the key before the key is to be dropped.
Cause: Self-evident.
Action: Remove the duplicate specification.
Cause: Self-evident.
Action: Remove the extra constraint.
Cause: The type of the evaluated default expression is incompatible with the datatype of the column.
Action: Change the type of the column, or modify the default expression.
Cause: The specified sequence name is not a valid identifier name.
Action: Specify a valid identifier name for the sequence name.
Cause: Self-evident.
Action: Remove the duplicate or conflicting specification.
Cause: Self-evident.
Action: Remove the duplicate or conflicting specification.
Cause: Self-evident.
Action: Remove the duplicate or conflicting specification.
Cause: Self-evident.
Action: Remove the duplicate or conflicting specification.
Cause: Self-evident.
Action: Remove the duplicate or conflicting specification.
Cause: Self-evident.
Action: Don’t alter it.
Cause: Self-evident.
Action: Remove the duplicate specification.
Cause: Self-evident.
Action: Remove the duplicate specification.
Cause: Self-evident.
Action: The statement is meaningless without any options.
Cause: The specified sequence number (CURRVAL or NEXTVAL) is inappropriate here in the statement.
Action: Remove the sequence number.
Cause: A token other than RESETLOGS appears following ALTER DATABASE name OPEN.
Action: Either nothing or RESETLOGS should be placed following OPEN.
Cause: The specified sequence does not exist, or the user does not have the required privilege to perform this operation.
Action: Make sure the sequence name is correct, and that you have the right to perform the desired operation on this sequence.
Cause: The values being inserted do not satisfy the named check constraint.
Action: do not insert values that violate the constraint.
Cause: A foreign key value has no matching primary key value.
Action: Delete the foreign key or add a matching primary key.
Cause: attempted to delete a parent key value that had a foreign key dependency.
Action: delete dependencies first then parent or disable constraint.
Cause: an alter table operation tried to validate a check constraint to a populated table that had nocomplying values.
Action: Obvious
Cause: While one DDL statement was attempting to enable this constraint, another DDL changed this same constraint.
Action: Try again, with only one DDL changing the constraint this time.
Cause: a create or alter table specified more than one enable and/or disable clause for a given constraint.
Action: only one enable or disable may be specified for a given constraint.
Cause: an alter table enable constraint failed because the table contains values that do not satisfy the constraint.
Action: Obvious
Cause: an alter table disable constraint failed becuase the table has foriegn keys that are dpendent on this constraint.
Action: Either disable the foreign key constraints or use disable cascade
Cause: an alter table validating constraint failed because the table has orphaned child records.
Action: Obvious
Cause: an alter table validating constraint failed because the table has duplicate key values.
Action: Obvious
Cause: A number was not specified for the value of OIDGENERATORS.
Action: Specify a number for OIDGENERATORS.