Oracle 12c R2 Error Codes and Solution Suggestions from ORA-14070 to ORA-14300
- ORA-14170: cannot specify <(sub)partition-description> clause in CREATE TABLE or CREATE INDEX
Cause: A request was made to generate default partition description(s) (possibly via PARTITIONS number-of-partitions) while at the same time specified <(sub)partition-description> clause which is illegal.
Action: Remove one of offending clauses.
Cause: User requested to generate default subpartition description(s) (possibly via SUBPARTITIONS) while at the same time specified subpartition-description clause which is illegal
Action: Remove one of offending clauses.
Cause: Name of the table to be EXCHANGED has to be followed by [{INCLUDING|EXCLUDING} INDEX][{WITH|WITHOUT} VALIDATION]
Action: Ensure that no options besides INCLDING INDEX or EXCLUDING INDEX are specified with ALTER TABLE EXCHANGE SUBPARTITION
Cause: Subpartition to be accessed may only be specified using its name. User attempted to use a subpartition number or a bind variable.
Action: Modify statement to refer to a subpartition using its name
Cause: ALTER TABLE COALESCE PARTITION|SUBPARTITION may be followed by an optional parallel clause. No partition/subpartition attributes may be specified
Action: Ensure that no partition/subpartition attribute was specified.
Cause: ALTER TABLE or ALTER INDEX statement attempted to combine a subpartition maintenance operation (e.g. MOVE SUBPARTITION) with some other operation (e.g. MODIFY PARTITION ADD SUBPARTITION or PCTFREE) which is illegal
Action: Ensure that a subpartition maintenance operation is the sole operation specified in ALTER TABLE or ALTER INDEX statement; operations other than those dealing with subpartitions, default attributes of partitioned tables/indices or specifying that a table be renamed (ALTER TABLE RENAME) may be combined at will
Cause: An invalid option was encountered while parsing physical attributes of a partition of an object partitioned using the HASH method. The TABLESPACE option is the only valid option for such partitions.
Action: Remove the invalid option(s).
- ORA-14177: STORE-IN (Tablespace list) can only be specified for a LOCAL index on a Hash or Composite Range Hash table
Cause: STORE-IN (Tablespace list) clause was used while creating a local index on a range/list/composite range list partitioned table
Action: Do not use the STORE_IN (Tablespace list) clause while creating a local index on range/list/composite range list partitioned table
Cause: STORE IN (DEFAULT) is valid only for local indexes.
Action: a) Specify a tablespace list in place of DEFAULT, or b) Remove STORE IN clause and specify tablespaces individually for each index partition.
Cause: An attempt was made to specify an unsupported partitioning method in this context.
Action: Remove the invalid option.
Cause: User attempted to specify TABLESPACE DEFAULT for a partition of a Range/System/Hash partitioned LOCAL index object, which is illegal.
Action: Replace TABLESPACE DEFAULT with TABLESPACE <ts_name> or remove it.
Cause: An unexpected option was encountered while parsing physical attributes of a local index partition; valid options for Range or Composite Range partitions are INITRANS, MAXTRANS, TABLESPACE, STORAGE, PCTFREE, PCTUSED, LOGGING and TABLESPACE; but only TABLESPACE may be specified for Hash partitions STORE IN () is also disallowed for all but Hash, Interval, Autolist, Composite Range Hash, Composite Interval, Composite Autolist partition.
Action: Remove the invalid options from the list of physical attributes of an index partition.
Cause: User attempted to create a LOCAL partitioned index with a number of sub-partitions which is different from that of the underlying table.
Action: Correct the CREATE INDEX statement to specify a correct number of sub-partitions
Cause: User attempted to create a LOCAL partitioned index that is not equi-partitioned with the underlying table. The partitioning types are mismatched.
Action: Correct the CREATE INDEX statement to ensure that the index partitionining method is consistent with that of the base table
Cause: User attempted to create a UNIQUE partitioned index whose sub-partitioning columns do not form a subset of its key columns which is illegal
Action: If the user, indeed, desired to create an index whose subpartitioning columns do not form a subset of its key columns, it must be created as non-UNIQUE; otherwise, correct the list of key and/or subpartitioning columns to ensure that the index’ subpartitioning columns form a subset of its key columns
Cause: unexpected option was encountered while parsing physical attributes of an index subpartition; TABLESPACE is the only valid option
Action: remove invalid option(s)
Cause: One of three possible actions caused the error: 1) ENABLE ROW MOVEMENT was specified more than once. 2) DISABLE ROW MOVEMENT was specified more than once. 3) Both ENABLE ROW MOVEMENT and DISABLE ROW MOVEMENT were specified.
Action: Remove all but one of the ENABLE ROW MOVEMENT or DISABLE ROW MOVEMENT clauses; then, reissue the command.
Cause: User specified ALLOCATE STORAGE clause in ALTER TABLE/ALTER INDEX statement issued against a composite partitioned index which is illegal.
Action: Remove the illegal option. If you want to add storage to individual [sub]partitions,the ALLOCATE EXTENT clause may be specified with ALTER TABLE OR INDEX MODIFY [SUB]PARTITION statement.
Cause: User attempted to modify one of INITRANS/MAXTRANS/LOGGING/STORAGE clause for an index partition of a Hash partitioned index
Action: Remove the physical attributes one is trying to modify
Cause: An option other than UNUSABLE, ALLOCATE EXTENT, DEALLOCATE UNUSED was specified in an ALTER INDEX MODIFY SUBPARTITION statement.
Action: Specify only legal options.
Cause: ALTER INDEX REBUILD SUBPARTITION contained descriptions of more than one subpartition to be rebuilt
Action: Ensure that the statement describes exactly one subpartition to be rebuilt and that it does not contain any commas
Cause: User specified ALLOCATE STORAGE clause in ALTER TABLE/ALTER INDEX statement issued against a range-partitioned index which is illegal.
Action: Remove the illegal option. If it is desired to add storage to individual partitions, ALLOCATE STORAGE clause may be specified with ALTER TABLE/INDEX MODIFY PARTITION statement.
Cause: The index specified to enforce the constraint is unsuitable for the purpose.
Action: Specify a suitable index or allow one to be built automatically.
Cause: Length of a linear key representation of a high bound of a table subpartition being added or along which an existing table or index subpartition is being split exceeded the legal limit (4K).
Action: Change the representation of a subpartition high bound to bring its length within legal limit.
Cause: High bound of the specified subpartition displayed in this message did not collate lower than that of the subsequent subpartition.
Action: Ensure that high bound of every subpartition (except for the last one) collates lower than that of a subsequent subpartition.
Cause: Length of linear key representation of a high bound of the specified subpartition displayed in this message exceeded the legal limit (4K).
Action: Change the representation of a subpartition high bound to bring its length within legal limit.
Cause: High bound of the specified subpartition displayed in this message did not collate lower than that of the subsequent subpartition.
Action: Ensure that high bound of every subpartition (except for the last one) collates lower than that of a subsequent subpartition.
Cause: Length of linear key representation of a high bound of the specified subpartition displayed in this message exceeded the legal limit (4K).
Action: Change the representation of a subpartition high bound to bring its length within legal limit.
Cause: A subpartition maintenance operation such as ALTER TABLE DROP|SPLIT|MERGE SUBPARTITION can only be performed on List or Range subpartitioned objects
Action: Issue the command only against a List or Range subpartitioned object.
Cause: number of columns in a subpartitioning column list exceeded the legal limit of 16
Action: modify subpartitioning column list so that it consists of at most 16 columns
Cause: A higher-bound subpartition was specified before the lower-bound subpartition in ALTER TABLE MERGE SUBPARTITIONS statement.
Action: Specify lower-bound subpartition first and then higher-bound subpartition
Cause: Attempted to merge two subpartitions that are not adjacent to each other.
Action: Specify two subpartitions that are adjacent
Cause: Attempted to reuse the lower-bound subpartition of the subpartitions being merged.
Action: Specify new resulting subpartition name or reuse the higher-bound subpartition only
Cause: Subpartition bound specified in ALTER TABLE MODIFY PARTITION ADD SUBPARTITION statement did not collate higher than that of the partition’s last subpartition.
Action: Ensure that the subpartition bound of the subpartition to be added collates higher than that of the table partition’s last subpartition.
Cause: Attempted to split a subpartition along a bound which either collates higher than that of the subpartition to be split or lower than that of a subpartition immediately preceding the one to be split
Action: Ensure that the bound along which a subpartition is to be split collates lower than that of the subpartition to be split and higher than that of a subpartition immediately preceding the one to be split
Cause: VALUES LESS THAN or AT clause was used for List subpartitioned tables
Action: Use VALUES (value list) clause for List subpartitioned tables
Cause: VALUES (value list) clause was used for Range subpartitioned tables
Action: Use VALUES LESS THAN or AT clause with Range subpartitioned tables
Cause: Subpartition bound list contained more elements than there are subpartitioning columns
Action: Ensure that the number of elements in subpartition bound list is equal to the number of subpartitioning columns of the table or index
Cause: Subpartition bound list contained fewer elements than there are subpartitioning columns
Action: Ensure that the number of elements in partition bound list is equal to the number of partitioning columns of the table or index
Cause: VALUES (value list) clause was not specified for the list subpartition being added
Action: Specify a VALUES (value list) clause
Cause: The object (table or index or lob) did not have a segment.
Action: Create segments for the object with the ALTER TABLE ALLOCATE EXTENT command.
Cause: Attempted to modify storage or allocate an extent for an object (table or LOB or index) without a segment.
Action: Create segments for the object with the ALTER TABLE ALLOCATE EXTENT command.
Cause: Attempted to create an index or add a LOB column in a dictionary managed tablespace to a table without a segment.
Action: Specify a locally managed tablespace OR create segments for the table with the ALTER TABLE ALLOCATE EXTENT command.
Cause: The SEGMENT CREATION DEFERRED clause was specified in the CREATE TABLE statement.
Action: Remove the SEGMENT CREATION DEFERRED clause.
Cause: The SEGMENT CREATION DEFERRED clause was specified in the CREATE TABLE statement.
Action: Remove the SEGMENT CREATION DEFERRED clause.
Cause: The INDEXING clause was specified for an unsupported table type.
Action: Remove the INDEXING clause from the statement.
Cause: INDEXING PARTIAL was specified during creation or modification of a unique index.
Action: Remove the INDEXING PARTIAL clause from the statement.
Cause: INDEXING FULL or INDEXING PARTIAL was specified during CREATE INDEX or ALTER INDEX when the base table was not partitioned.
Action: Remove the INDEXING clause from the statement.
Cause: INDEXING FULL or INDEXING PARTIAL was specified during CREATE INDEX or ALTER INDEX for an unsupported index type.
Action: Remove the INDEXING clause from the statement.
Cause: An attempt was made to perform ALTER TABLE MODIFY PARTITION INDEXING [ON|OFF] on a composite table partition.
Action: Run ALTER TABLE MODIFY SUBPARTITION INDEXING [ON|OFF] on each subpartition individually.
Cause: Subpartition not found for the object.
Action: Retry with correct subpartition name.
Cause: Only ALLOCATE EXTENT and DEALLOCATE UNUSED may be specified in ALTER TABLE MODIFY PARTITION for a Hash partition.
Action: Specify only legal options.
Cause: One of the following occurred: – The table in a subpartition maintenance operation (ALTER TABLE [EXCHANGE | MODIFY | MOVE | TRUNCATE] SUBPARTITION or ALTER TABLE MODIFY PARTITION [ADD | COALESCE] SUBPARTITION) was not partitioned by the composite partition method. – The table in the MODIFY DEFAULT ATTRIBUTES FOR PARTITION operation was partitioned by a method other than the composite method. – The table in the ALTER TABLE MODIFY SUBPARTITION [UNUSABLE LOCAL INDEXES | REBUILD UNUSABLE LOCAL INDEXES] statement was not partitioned by the composite partition method which is illegal.
Action: Ensure that the table is partitioned by composite partition method.
Cause: User specified ALLOCATE STORAGE clause in ALTER TABLE statement issued against a Range or Composite Range partitioned table which is illegal.
Action: Remove the illegal option. If it is desired to add storage to individual partitions/subpartitions, ALLOCATE STORAGE clause may be specified with ALTER TABLE MODIFY PARTITION/SUBPARTITION statement. If it is desired to add storage to all subpartitions of a Composite partition, ALLOCATE STORAGE clause may be specified with ALTER TABLE MODIFY PARTITION.
Cause: The table was not partitioned by the range, list, composite range, or composite list method.
Action: The ALTER TABLE [SPLIT | DROP] PARTITION or ALTER TABLE MERGE PARTITIONS command is only valid for a table partitioned by range, list, composite range, or composite list methods. Ensure that the table is partitioned appropriately.
Cause: User specified STORE-IN clause, SUBPARTITIONS clause, and/or subpartition-description clause in partition description(s) in ALTER TABLE SPLIT PARTITION or ALTER TABLE MERGE PARTITIONS statement but the table in the maintenance operation is not a Composite Range partitioned table which is illegal
Action: Remove invalid clause(s), or ensure that the table is partitioned by Composite Range method
Cause: An attempt was made to move a partition that was a composite partition.
Action: Move the subpartitions one-by-one to move the composite partition.
Cause: User specified STORE-IN clause, SUBPARTITIONS clause, and/or subpartition-description clause in ALTER TABLE ADD PARTITION statement but the table in the maintenance operation is not a Composite Range/Hash partitioned table which is illegal
Action: Remove invalid clause(s), or ensure that the table is partitioned by Composite Range/Hash method
Cause: ALTER TABLE COALESCE PARTITION is only valid for table partitioned by Hash method
Action: Specify valid ALTER TABLE option for the table, or ensure that the table is partitioned by Hash method
Cause: User specified INITRANS, MAXTRANS, STORAGE, PCTFREE, PCTUSED, and/or [NO]LOGGING option to a Hash partition via ALTER TABLE ADD/MOVE PARTITION command which is illegal. Only TABLESPACE may be specified.
Action: Remove invalid option(s)
Cause: User specified VALUES LESS THAN clause when adding a partition (via ALTER TABLE ADD PARTITION) to a Hash partitioned table which is illegal
Action: Remove VALUES LESS THAN clause from the description of partition being added
Cause: User entered ALTER TABLE/INDEX RENAME SUBPARTITION specifying new-subpartition-name which is identical to the name of the subpartition being renamed
Action: Ensure that the new subpartition name is different from the name of any (including the one being renamed) existing subpartition of a given table or index
- ORA-14263: New subpartition name must differ from that of any other partition or subpartition of the object.
Cause: ALTER TABLE RENAME SUBPARTITION or ALTER INDEX RENAME SUBPARTITION specifying new-subpartition-name which is identical to the name of an existing partition or subpartition of the object was specified.
Action: Ensure that the new subpartition name is different from the name of any (including the one being renamed) existing partition or subpartition of a given table or index.
Cause: User issued ALTER TABLE statement attempting to modify data type and/or length of a column used to subpartition the table named in ALTER TABLE statement, which is illegal
Action: Avoid modifying data type and/or length of table subpartitioning column(s)
Cause: User issued ALTER TABLE statement attempting to modify data type and/or length of a column used to subpartition some index defined on the table named in ALTER TABLE statement, which is illegal
Action: Avoid modifying data type and/or length of index subpartitioning column(s)
Cause: The ALTER TABLE ADD PARTITION statement with PARALLEL clause for a partition or composite partition method other than HASH was issued.
Action: Remove the PARALLEL clause.
Cause: User attempted an operation requiring that we access data in a subpartition which resides in a tablespace which was taken offline. Such operations include trying to drop a tablespace of a table which has indices defined on it or is referenced by a constraint.
Action: Bring tablespace online before attempting the operation.
Cause: User attempt to exchange a partition with a non-partitioned table but the specified partition is not a Range,List,System, or Hash partition which is illegal
Action: Specify EXCHANGE PARTITION for a Range,List,System, or Hash partition only
Cause: The table in ALTER TABLE MODIFY PARTITION { UNUSABLE LOCAL INDEXES | REBUILD UNUSABLE LOCAL INDEXES } statement is not partitioned by Range,List,System, or Hash method which is illegal.
Action: Ensure that the table is partitioned by Range,List,System, or Hash method 14271, 00000, “free error number”
Cause: User attempt to reuse a lower-bound partition in ALTER TABLE MERGE PARTITIONS statement as the resulting partition which is illegal.
Action: Use the higher-bound partition to be the resulting partition or specify a new partition name
Cause: User specified higher-bound partition before lower-bound partition in ALTER TABLE MERGE PARTITIONS statement which is illegal
Action: Specify lower-bound partition then higher-bound partition
Cause: User attempt to merge two partitions that are not adjacent to each other which is illegal
Action: Specify two partitions that are adjacent
Cause: User attempt to reuse lower-bound partition of the partitions being merged which is illegal
Action: Specify new resulting partition name or reuse the higher-bound partition only
Cause: The table in the ALTER TABLE EXCHANGE SUBPARTITION operation is either clustered or partitioned
Action: Ensure that the table with which the subpartition is being exchanged for is not partitioned or clustered.
Cause: The two tables specified in the ALTER TABLE EXCHANGE SUBPARTITION have different number of columns
Action: Ensure that the two tables have the same number of columns with the same type and size.
Cause: The corresponding columns in the tables specified in the ALTER TABLE EXCHANGE SUBPARTITION are of different type or size
Action: Ensure that the two tables have the same number of columns with the same type and size.
Cause: The two tables specified in the ALTER TABLE EXCHANGE SUBPARTITION have indexes which are not equivalent
Action: Ensure that the indexes for the two tables have indexes which follow this rule For every non partitioned index for the non partitioned table, there has to be an identical LOCAL index on the partitioned table and vice versa. By identical, the column position, type and size have to be the same.
Cause: There is at least one row in the non partitioned table which does not qualify for the subpartition specified in the ALTER TABLE EXCHANGE SUBPARTITION
Action: Ensure that all the rows in the segment qualify for the subpartition. Perform the alter table operation with the NO CHECKING option. Run ANALYZE table VALIDATE on that subpartition to find out the invalid rows and delete them.
Cause: The corresponding columns in the tables specified in the ALTER TABLE EXCHANGE SUBPARTITION statement have CHECK constraint defined on them.
Action: Ensure that the two tables do not have CHECK constraint defined on any column
Cause: The corresponding columns in the tables specified in the ALTER TABLE EXCHANGE SUBPARTITION statement have different FOREIGN KEY constraints.
Action: Ensure that the two tables do not have FOREIGN KEY constraints defined on any column or disable all FOREIGN KEY constraints on both tables. Then retry the operation.
Cause: One of the tables named in the ALTER TABLE EXCHANGE SUBPARTITION command has a UNIQUE constraint for which no matching (vis-a-vis key columns) constraint is defined on the other table or a matching constraint is defined on the other table, but it differs from that defined on the first table vis-a-vis being enabled and/or validated.
Action: Ensure that for every UNIQUE constraint defined on one of the tables named in the ALTER TABLE EXCHANGE SUBPARTITION statement there is a matching (vis-a-vis key columns and being enabled and/or validated) UNIQUE constraint defined on the other table. If UNIQUE constrains are enabled, UNIQUE constraints on the partitioned table should be enforced using local indexes.
Cause: User issued ALTER TABLE statement attempting to modify an existing VARCHAR2 (or VARCHAR) column to be of type CHAR (or CHARACTER), increase length of an existing CHAR (or CHARACTER) column, or add a column with user-specified default for a table one or more subpartitions of which reside in read-only tablespaces, which is illegal
Action: Avoid performing aformentioned operations on a partitioned table one or more subpartitions of which reside in read-only tablespaces
Cause: A COALESCE PARTITION command was issued when there is only one partition in the table or index, which is illegal
Action: Ensure that there is at least one partition. Drop the table or index to remove all partitions.
Cause: A COALESCE SUBPARTITION command was issued when there is only one subpartition in the partition which is illegal
Action: Ensure that there is at least one subpartition. Drop partition to remove all subpartitions.
Cause: An attempt was made to rebuild a partition of a composite partitioned index which is illegal.
Action: REBUILD the index partition one subpartition at a time.
Cause: The index in a partition or subpartition maintenance operation (ALTER INDEX MODIFY [SUBPARTITION|DEFAULT ATTRIBUTES FOR PARTITION] or ALTER INDEX REBUILD SUBPARTITION command was not partitioned by the composite partition method.
Action: REBUILD the index partition one at a time.
Cause: An attempt was made to rebuild a partition of a composite partitioned index which is illegal.
Action: REBUILD the index partition one subpartition at a time.
Cause: The corresponding columns in the tables specified in the ALTER TABLE EXCHANGE [SUB]PARTITION statement have different PRIMARY KEY constraints.
Action: Ensure that the two tables do not have PRIMARY KEY constraints defined on any column or disable all PRIMARY KEY constraints on both tables. Then retry the operation.
Cause: A composite partition can only be exchanged with a partitioned table.
Action: Ensure that the table being exchanged is partitioned or that that the partition being exchanged is non-composite.
Cause: When exchanging a partitioned table with a composite partition the partitioning type of the table must match the subpartitioning type of the composite partition.
Action: Ensure that the partitioning type of partitioned table is the same as the subpartitioning type of the composite partition.
Cause: When exchanging a partitioned table with a composite partition the number of partitioning columns of the table must match the number of subpartitioning columns of the composite partition.
Action: Ensure that the number of partitioning columns in the partitioned table is the same as the number of subpartitioning columns in the the composite partition.
Cause: When exchanging a partitioned table with a composite partition the number of partitions of the table must match the number of subpartitions of the composite partition.
Action: Ensure that the number of partitions in the partitioned table is the same as the number of subpartitions in the the composite partition.
Cause: When exchanging a partitioned table with a composite partition the type and size of the partitioning columns of the table must match the type and size of the subpartitioning columns of the composite partition.
Action: Ensure that the type and size of the partitioning columns of the partitioned is the same as the type and size of the subpartitioning columns of the composite partition.
Cause: The block sizes of the two tables specified in the ALTER TABLE EXCHANGE [SUB]PARTITION statement are different. For index organized tables, either the block sizes of the index or the overflow (or both) do not match.
Action: Ensure that the block sizes of the tables involved in the ALTER TABLE EXCHANGE [SUB]PARTITION statement are the same. For index organized tables, ensure that the block sizes of both the index and the overflow of the two tables match.
Cause: The block sizes of a pair of indexes being exchanged in the ALTER TABLE EXCHANGE [SUB]PARTITION statement are different.
Action: Ensure that the block sizes of the corresponding pairs of indexes that need to be exchanged in the ALTER TABLE EXCHANGE [SUB]PARTITION statement are the same.
Cause: The block sizes of a pair of corresponding LOB columns of the two tables specified in the ALTER TABLE EXCHANGE [SUB]PARTITION statement are different.
Action: Ensure that the block sizes of corresponding pairs of LOB columns of the tables involved in the ALTER TABLE EXCHANGE [SUB]PARTITION statement are the same.
Cause: The total number of combined fragments specified in partitions /subpartitions exceeds 1048575.
Action: Reissue the statement with fewer number of fragments
Cause: The row inserted had a partitioning key that maps to a partition number greater than 1048575
Action: Ensure that the partitioning key falls within 1048575 partitions or subpartitions.