ORA-01795: the maximum number of expressions in the list is 1000

A developer encountered an error: ORA-01795: the maximum number of expressions in the list is 1000

Statement is a select xxx from xxx where xxx in (xx, xx ,.....);

Let's take a look at the error:

01795,000 00, "maximum number of expressions in a list is 1000"
// * Cause: Number of expressions in the query exceeded than 1000.
// Note that unused column/expressions are also counted
// Maximum number of expressions that are allowed are 1000.
// * Action: Reduce the number of expressions in the list and resubmit.

 

It can be seen from this that a list contains a maximum of 1000 values, that is to say, a maximum of 1000 values in. This error is reported if there are more than 1000 values in brackets.

The solution is simple, that is, to split an in statement into multiple in statements. If it exceeds 1000, it is split into two in statements. If it exceeds 4000, it is split into five in statements.

Change to SQL as follows: select xxx from xxx where xxx in (xx, xxx ...) or xxx in (xx, xx ,...) or xxx in (xx, xx ,...);

GoldenGate performs two-way Oracle-Oracle replication without using a Data Pump

One-way Oracle-Oracle Replication Using the GoldenGate Data Pump

How to debug Oracle Data Pump (expdp/impdp)

Oracle Database Export data pump (EXPDP) file storage location

Export of Oracle 10g Data Pump Partition Table