COPY of a foreign table throws error

[Aliya55]

I did import schema for one table and wanted records from a specific column(account-id)
The below query was run.
COPY(select * from where account_id = '23') To '23.csv' With CSV DELIMITER ',' HEADER;

After running for a while I observed the following errors for two different account-ids

ERROR: error fetching result: OCIStmtFetch2 failed to fetch next result row DETAIL: ORA-01555: snapshot too old: rollback segment number 1 with name "_SYSSMU1_2176836045$" too small

The csv file generated after the above error has similar number of counts when compared with oracle.

"error- ERROR: invalid byte sequence for encoding ""UTF8"": 0x00
CONTEXT: converting column ""user_id"" for foreign table scan of """", row 141007627"

The csv file generated after the above error has a huge difference in counts when compared to oracle.

ERROR: error executing query: OCIStmtExecute failed to execute remote query DETAIL: ORA-01652: unable to extend temp segment by 128 in tablespace TEMP

Though the copy for other account-ids is a success in postgres, some are failing with the errors mentioned.

Could it be because of any issue in postgres which is not letting the oracle data to copy or issue from oracle side itself?

Hope this explanation suffices to answer my question.
Thanks in advance.

[laurenz]

These are three different, unrelated problems.

The ORA-01555 and the ORA-01652 are caused by Oracle because the UNDO and the TEMP space are too small.
You will have to consult with your Oracle DBA what to do about those.

The invalid byte 0x00 is a zero byte, which is allowed in strings in Oracle, but not in PostgreSQL. If you cannot fix those on the Oracle side, you can use the strip_zeros column option, as detailed in the documentation.

[Aliya55]

Thanks for this useful info.
I want to include the strip_zeros option in the import schema query.

IMPORT FOREIGN SCHEMA "tpn" LIMIT TO (tpn.table) FROM SERVER pst INTO tpn OPTIONS(strip_zeros 'true');

Since the byte sequence error occurs only in one column. Will this work? or I need to run create foreign table query.

[laurenz]

No, you cannot use this option with IMPORT FOREIGN SCHEMA.

Once you know which column of which table has the problem, you can run the following after IMPORT FOREIGN SCHEMA:

ALTER FOREIGN TABLE ftab ALTER col OPTIONS (ADD strip_zeros 'true');

[laurenz]

Can I close this issue?

[Aliya55]

Yeah sure. And thanks for the help.

[laurenz]

Just for the record, I'll mark this as a duplicate of #114.