How to add partitions to an existing table in Oracle database

When the data in the table becomes bigger, we’ll need partitioning in order to make our SQL command faster. But the thing is we already have data in that table. These queries will help you do that with just one execution step.

--create new partitioned table from old table
CREATE 
Read the rest “How to add partitions to an existing table in Oracle database”

Oracle: “CREATE TABLE AS SELECT” (CTAS) with PARTITIONING and Column Default Value

This article guides you to write Oracle PL/SQL command for creating new table as select from other tables/views with partitioning and column default value.

First of all, this is the syntax of a simple CTAS:

CREATE TABLE TBLA AS SELECT * FROM TBLB;

Please note that in the example above, … Read the rest “Oracle: “CREATE TABLE AS SELECT” (CTAS) with PARTITIONING and Column Default Value”

ORA-65535: Oracle Database client cannot handle error code exceeding 65535. Actual error code and message follow:\n

Error code

ORA-65535: Oracle Database client cannot handle error code exceeding 65535. Actual error code and message follow:\n

Causes

Oracle Database clients earlier than release 12.1 could not handle error codes larger than 65535.

Solutions

Upgrade Oracle Database client library to release 12.1 or later.

References

Read the rest “ORA-65535: Oracle Database client cannot handle error code exceeding 65535. Actual error code and message follow:\n”

ORA-65532: cannot alter or drop automatically created indexes

Error code

ORA-65532: cannot alter or drop automatically created indexes

Causes

An attempt was made to alter or drop an automatically created index.

Solutions

Do not perform the action on an automatically created index.

References

Read the rest “ORA-65532: cannot alter or drop automatically created indexes”

ORA-65531: maximum level reached in index segment with object id string

Error code

ORA-65531: maximum level reached in index segment with object id string

Causes

The attempted operation resulted in the number of index levels exceeding the maximum allowed.

Solutions

Reduce the size of the relevant index, for example, by using compression or partitioning.

References

Read the rest “ORA-65531: maximum level reached in index segment with object id string”

ORA-65513: value-based LOB locator operand mismatch for SQL operator

Error code

ORA-65513: value-based LOB locator operand mismatch for SQL operator

Causes

The LOB locator operands that were passed to the SQL operator did not have the value-based property set uniformly.

Solutions

All of the LOB locator operands should either be value-based or reference-based. There cannot be a mix of … Read the rest “ORA-65513: value-based LOB locator operand mismatch for SQL operator”

ORA-65512: cannot access temporary LOB from old incarnation of pluggable database string

Error code

ORA-65512: cannot access temporary LOB from old incarnation of pluggable database string

Causes

When the pluggable database was closed, any temporary LOBs in it were freed. An attempt was made to access a freed LOB.

Solutions

Reinitialize the temporary LOB in the current incarnation of the pluggable database.… Read the rest “ORA-65512: cannot access temporary LOB from old incarnation of pluggable database string”

ORA-65511: SQL statement executed on a remote database over a database link cannot access a LOB on the local database with parallel execution enabled on the remote side

Error code

ORA-65511: SQL statement executed on a remote database over a database link cannot access a LOB on the local database with parallel execution enabled on the remote side

Causes

This is likely the result of passing a local LOB locator as a bind value to a SQL statement … Read the rest “ORA-65511: SQL statement executed on a remote database over a database link cannot access a LOB on the local database with parallel execution enabled on the remote side”

ORA-65510: Distributed LOB operations are not supported on pre-12.2 databases.

Error code

ORA-65510: Distributed LOB operations are not supported on pre-12.2 databases.

Causes

An attempt was made to send or receive a LOB locator over a database link to a pre-12.2 database.

Solutions

Ensure that the remote database for a distributed LOB operation is Oracle Database version 12.2 or higher.… Read the rest “ORA-65510: Distributed LOB operations are not supported on pre-12.2 databases.”