search

darold / ora2pg

Ora2Pg is a free tool used to migrate an Oracle database to a PostgreSQL compatible schema. It connects your Oracle database, scan it automatically and extracts its structure or data, it then generates SQL scripts that you can load into PostgreSQL.
http://www.ora2pg.com/
GNU General Public License v3.0
991 stars 342 forks source link

Question: How to fulfill the transfer of current values of Sequence as ordinary data. Only Sequence #1600

Closed sgrinko closed 1 year ago

sgrinko commented 1 year ago

Hello,

I use the Ora2pg version from Head

I need to carry out the transfer of Sequence values with a separate operation from Oracle to PostgreSQL

I am writing about such a team as:

2023-03-16 16:49:35.104 ... app=ora2pg ,client=..., vxid:6/2195 txid:16834104 idle in transaction LOG:  statement: ALTER SEQUENCE IF EXISTS seq_person_address_l_id RESTART WITH 11101506;

I see that copying of these tables is accompanied at the end of the work by transferring Sequence. I have no Sequence on the side of PostgreSQL (it should be, I do not need them in PostgreSQL and I deleted them myself) and I see such messages:

NOTICE:  relation "ul_t_contract_h_seq" does not exist, skipping
NOTICE:  relation "ul_t_contract_l_seq" does not exist, skipping

Although I have a filter installed:

EXCLUDE_TABLES="EXCLUDE UL_.*"
EXCLUDE_TABLES_DATA="EXCLUDE UL_.*"

These Sequence are still used. This is not a problem, since it is Notice and does not interrupt the process. Although it is not clear to me why the filter does not work.

Now I ask you to tell me how I can download the values of Sequence in my postgresql database with a separate operation?

Thanks for your work!

darold commented 1 year ago

Commit b711cc6 adds a new export type: SEQUENCE_VALUES to generate DDL to set sequences start values. Example of use:

ora2pg -c config/ora2pg.conf -t SEQUENCE_VALUES -b tmp -e "departments_.*"
sgrinko commented 1 year ago

Thanks a lot! I'll try, I will write about the results ...

sgrinko commented 1 year ago

Everything worked perfectly, as I need!

Thanks a lot!

SET client_encoding TO 'UTF8';

SET search_path = bbb,oracle,public;
\set ON_ERROR_STOP ON

SET check_function_bodies = false;

SET search_path = bbb,oracle,public;

ALTER SEQUENCE ident_standard_frgn_seq START WITH 6;
ALTER SEQUENCE seq_company_search_frgn_id START WITH 28761;
ALTER SEQUENCE seq_company_search_id START WITH 94840515;