Конвертация Oracle в PostgreSQL

Перетащите файл .sql, который записывает Database Export в SQL Developer или SQLcl, с таблицами и командами Insert, и скачайте скрипт PostgreSQL с теми же таблицами, ключами, индексами и данными. Каждое значение, которое PostgreSQL не может хранить как есть, и каждый объект, который он не переносит, указаны в отчете.

Руководство по миграции Oracle в PostgreSQL Типы данных из Oracle в PostgreSQL

Ваш файл

sqlshop_2024_oracle.sqlПример файла, 9,3 кБ

18 строк конвертировано, без исправлений, ни одна не отклонена

ТаблицыSQLОтчет

Таблицы в shop_2024_oracle.sqlДамп Oracle · создан программой SQL Developer · utf-8

customers8 строк
7 столбцов
id bigint not null
name varchar(100) not null
email varchar(150) not null
country varchar(2) not null
is_active smallint not null
signed_up timestamp(0)
created_at timestamp(0) not null

orders10 строк

9 столбцов
id	bigint not null
customer_id	bigint not null
invoice	bigint
status	varchar(10) not null
total	numeric(10,2) not null
items	integer not null
ordered_at	timestamp(0) not null
shipped_at	timestamp(0)
note	varchar(255)

-- Written by IntoSQL from a Oracle dump. Load it with: psql -v ON_ERROR_STOP=1 -f <file>
SET client_encoding = 'UTF8';
SET standard_conforming_strings = on;
SET check_function_bodies = false;
SET client_min_messages = warning;
SET synchronous_commit = off;
SET maintenance_work_mem = '512MB';

CREATE SEQUENCE invoice_numbers INCREMENT BY 1 MINVALUE 1 START WITH 1020;
CREATE TABLE customers (
    id bigint GENERATED BY DEFAULT AS IDENTITY NOT NULL,
    name varchar(100) NOT NULL,
    email varchar(150) NOT NULL,
    country varchar(2) DEFAULT 'US' NOT NULL,
    is_active smallint DEFAULT 1 NOT NULL,
    signed_up timestamp(0),
    created_at timestamp(0) DEFAULT LOCALTIMESTAMP NOT NULL,
    CONSTRAINT customers_check1 CHECK (is_active IN (0, 1))
);

CREATE TABLE orders (
    id bigint GENERATED BY DEFAULT AS IDENTITY NOT NULL,
    customer_id bigint NOT NULL,
    invoice bigint DEFAULT (nextval('invoice_numbers')),
    status varchar(10) DEFAULT 'pending' NOT NULL,
    total numeric(10,2) NOT NULL,
    items integer DEFAULT 1 NOT NULL,
    ordered_at timestamp(0) NOT NULL,
    shipped_at timestamp(0),
    note varchar(255),
    CONSTRAINT orders_check1 CHECK (status IN ('pending', 'paid', 'shipped', 'cancelled'))
);

COPY customers (id, name, email, country, is_active, signed_up, created_at) FROM stdin;
1	Ana Martínez	ana@example.com	MX	1	2023-01-15 00:00:00	2023-01-15 10:24:00.000000
2	Zoë Müller	zoe@example.de	DE	1	2023-02-03 00:00:00	2023-02-03 08:11:42.000000
3	José Peña	jose@example.es	ES	0	2023-03-21 00:00:00	2023-03-21 19:05:17.000000
4	李雷	lilei@example.cn	CN	1	2023-05-09 00:00:00	2023-05-09 02:40:00.000000
5	Noah O'Brien	noah@example.ie	IE	1	\N	2023-06-30 14:00:00.000000
6	Amélie Laurent	amelie@example.fr	FR	1	2024-01-02 00:00:00	2024-01-02 09:30:00.000000
7	Ravi Patel	ravi@example.in	IN	0	2024-02-29 00:00:00	2024-02-29 23:59:59.000000
8	Sofia Rossi 🌻	sofia@example.it	IT	1	2024-04-12 00:00:00	2024-04-12 12:12:12.000000
\.

COPY orders (id, customer_id, invoice, status, total, items, ordered_at, shipped_at, note) FROM stdin;
1	1	1000	shipped	129.9	3	2024-03-01 10:00:00	2024-03-02 16:30:00	\N
2	2	1001	paid	54	1	2024-03-02 11:15:00	\N	Gift wrap 🎁
3	1	1002	cancelled	12.5	1	2024-03-03 09:45:00	\N	Customer changed their mind
4	4	1003	shipped	980	12	2024-03-05 02:10:00	2024-03-07 08:00:00	请小心轻放
5	5	1004	pending	45.75	2	2024-03-08 17:20:00	\N	Leave at the door; ring twice
6	6	1005	shipped	310.4	5	2024-03-10 13:00:00	2024-03-11 09:00:00	Fragile: "glass"
7	8	1006	paid	76.2	2	2024-03-11 18:45:00	\N	\N
8	2	1007	paid	19.99	1	2024-03-12 08:00:00	\N	Paid at the counter
9	3	1008	shipped	64	2	2024-03-14 15:30:00	\N	Shipping date never recorded
10	7	1009	paid	1499	1	2024-03-15 21:05:00	\N	C:\\Users\\ravi\\invoice.pdf
\.


-- Keys, indexes and constraints, after the rows so each builds once.
CREATE UNIQUE INDEX customers_customers_pk ON customers (id);
CREATE UNIQUE INDEX customers_customers_email_uk ON customers (email);
CREATE UNIQUE INDEX orders_orders_pk ON orders (id);
CREATE INDEX orders_orders_status_date_ix ON orders (status, ordered_at);
ALTER TABLE customers ADD CONSTRAINT customers_customers_pk PRIMARY KEY USING INDEX customers_customers_pk;
ALTER TABLE customers ADD CONSTRAINT customers_customers_email_uk UNIQUE USING INDEX customers_customers_email_uk;
ALTER TABLE orders ADD CONSTRAINT orders_orders_pk PRIMARY KEY USING INDEX orders_orders_pk;
ALTER TABLE orders ADD CONSTRAINT orders_customer_fk FOREIGN KEY (customer_id) REFERENCES customers (id) NOT VALID;
DO $f$ BEGIN
    ALTER TABLE orders VALIDATE CONSTRAINT orders_customer_fk;
EXCEPTION WHEN foreign_key_violation THEN
    RAISE WARNING 'foreign key % has rows without a parent: it stays NOT VALID, and new rows are checked', 'orders_customer_fk';
END $f$;
SELECT setval(pg_get_serial_sequence('customers', 'id'), LEAST(GREATEST(COALESCE((SELECT max(id) FROM customers)::numeric, 0) + 1, 1), 9223372036854775807)::bigint, false);
COMMENT ON TABLE customers IS 'People who bought at least once';
SELECT setval(pg_get_serial_sequence('orders', 'id'), LEAST(GREATEST(COALESCE((SELECT max(id) FROM orders)::numeric, 0) + 1, 1), 9223372036854775807)::bigint, false);

Исправления

Исправлять ничего не пришлось.

Файл конвертируется на этом компьютере и никуда не загружается.

Другие примеры:

Что меняется при переходе с Oracle на PostgreSQL

Каждая строка - это правило, которое применяет конвертер, и строка отчета, которую он пишет, когда правило что-то меняет в ваших данных.

BOOLEAN (23ai)
boolean
TRUE и FALSE остаются true и false.
VARCHAR2(n BYTE|CHAR), NVARCHAR2(n), CHAR(n), NCHAR(n), CLOB, NCLOB, LONG
varchar(n) и text
Длины считаются в символах, считал ли Oracle байты или символы, поэтому каждое значение помещается.
NUMBER, NUMBER(p,s), FLOAT(b)
numeric(p,s) или bigint
NUMBER сохраняет каждую цифру; NUMBER без точности, все значения которого - целые числа до 18 цифр, становится 64-битным целым, а отрицательный масштаб хранит столько же цифр до запятой.
DATE, TIMESTAMP(p)
timestamp(p)
DATE в Oracle хранит время с точностью до секунды, поэтому это дата и время. Экспорт в американском или немецком сеансе записывает даты без времени и века (DD-MON-RR); экспортируйте в сеансе ISO (SYYYY-MM-DD HH24:MI:SS), чтобы их сохранить.
TIMESTAMP(7) to TIMESTAMP(9)
шесть цифр долей секунды, седьмая отбрасывается
PostgreSQL хранит микросекунды; седьмая цифра (100 наносекунд) отбрасывается, а не округляется, поэтому ни одно значение не переходит на следующую секунду или день.
Строка отчета: fraction_cut
TIMESTAMP WITH TIME ZONE, TIMESTAMP WITH LOCAL TIME ZONE
timestamptz, те же моменты
timestamptz сохраняет момент; PostgreSQL показывает его в часовом поясе сеанса, а не со смещением, которое было у значения.
JSON (21c), and CLOB holding JSON
jsonb
Те же значения JSON; SQL NULL, который экспорт записал как 'null', остается NULL.
BLOB, RAW(n), LONG RAW
bytea
Байты остаются байтами.
ROWID, UROWID, XMLTYPE, SDO_GEOMETRY
text, а для XMLTYPE - xml
У собственных типов Oracle нет аналогов; их значения сохраняют свой текст, включая значения ROWID, хотя вне Oracle они ничего не значат.
Строка отчета: type_replaced
collations that ignore case (BINARY_CI)
точное сравнение
PostgreSQL сравнивает текст точно, поэтому столбцы, которые в Oracle не учитывали регистр, теперь различают Ana и ana.
Строка отчета: case_insensitive_collation
identity columns, and a sequence a BEFORE INSERT trigger assigns
GENERATED BY DEFAULT AS IDENTITY, продолжается после наибольшего id
Скопированные id загружаются как есть, а счетчик продолжается после наибольшего. Столбец, который триггер заполняет из последовательности (способ до 12c), тоже становится столбцом identity.
CREATE SEQUENCE
CREATE SEQUENCE с теми же параметрами
Экспорт записывает, до какого значения дошла последовательность, как ее START WITH, поэтому она продолжается оттуда.
virtual columns (GENERATED ALWAYS AS ... VIRTUAL)
генерируемые столбцы, STORED
PostgreSQL с 13 по 17 только хранит генерируемые столбцы; формула та же, а || записан так, чтобы NULL считался пустой строкой, как в Oracle.
Строка отчета: virtual_as_stored
virtual columns with functions IntoSQL doesn't write, and the || operator
обычный столбец
Формула, которую нельзя записать для PostgreSQL, оставляет обычный столбец и строку в отчете; значения, которые экспорт записал для виртуальных столбцов, опускаются, и PostgreSQL не может их вычислить.
Строка отчета: generated_not_translated
defaults with functions IntoSQL doesn't write (SYS_GUID(), ||)
без значения по умолчанию
Значение по умолчанию, которое нельзя записать для PostgreSQL, опускается и попадает в отчет.
Строка отчета: default_not_translated
CHECK constraints
переводится
Те же проверки, применяемые при загрузке каждой строки.
CHECK constraints with functions IntoSQL doesn't write
не создается
Проверка, которую нельзя записать для PostgreSQL, опускается и попадает в отчет.
Строка отчета: check_skipped
CHECK constraints that are DISABLE or ENABLE NOVALIDATE
не создается
Oracle не применяет эти проверки к уже существующим строкам, поэтому строки могут их нарушать.
Строка отчета: check_not_enforced
function-based, bitmap and domain indexes
не создается
IntoSQL пишет индексы b-tree по столбцам; остальные создайте заново после загрузки.
Строка отчета: index_skipped
foreign keys
внешние ключи, после строк
Строки загружаются в любом порядке, а ключи проверяют их после этого.
foreign keys to tables the export doesn't create, and DISABLE or NOVALIDATE ones
не создается
Ключ не может ссылаться на таблицу, которой нет, а у ключа, который Oracle не проверяет, могут быть строки без родителя.
Строка отчета: foreign_key_skipped
partitioned tables
одна таблица со строками всех секций
Строки те же; при необходимости секционируйте таблицу заново в PostgreSQL.
Строка отчета: partitions_dropped
NOT NULL columns with values that become NULL
NOT NULL снимается
Столбец, значения которого пришлось заменить на NULL, не может сохранить NOT NULL, иначе загрузка остановится.
Строка отчета: not_null_relaxed
views, synonyms, types, packages, procedures, functions and other triggers
перечисляются в отчете
Их код написан на PL/SQL, поэтому они перечислены, чтобы вы создали их заново.
Строка отчета: not_converted_objects
exports without CREATE TABLE (data only)
типы определяются по значениям
Экспорт только с данными содержит лишь строки, поэтому типы берутся из значений.
Строка отчета: no_schema
expressions in INSERT values other than the ones exports write
NULL
Конвертер, который не запускает базу данных, не может их вычислить; to_date, to_timestamp, TO_CLOB, HEXTORAW и другие функции, которые пишут экспорты, читаются.
Строка отчета: expression_value
types IntoSQL doesn't know, and object types
text
Тип text вмещает любое значение; измените тип после загрузки.
Строка отчета: unknown_type
dates before year 1 and time zones given as region names
NULL
Значение, которое не является датой, становится NULL, и отчет его подсчитывает.
Строка отчета: date_out_of_range
NaN and Infinity in BINARY_FLOAT and BINARY_DOUBLE
NaN и Infinity сохраняются
Типы с плавающей точкой в PostgreSQL хранят NaN и бесконечность.
NUL characters in text
удаляется
text в PostgreSQL не может содержать символ NUL. Он удаляется, и отчет подсчитывает такие значения.
Строка отчета: nul_removed
text that isn't valid in its encoding
неверные байты заменяются символом замены
Текст в PostgreSQL должен быть корректным UTF-8.
Строка отчета: invalid_utf8_replaced
text decoded twice (Ã±)
копируется как есть
Скрипт содержит именно эти символы, поэтому они копируются; отчет на них указывает.
Строка отчета: mojibake_suspected

В Oracle	В PostgreSQL	Строка отчета
BOOLEAN (23ai)	booleanTRUE и FALSE остаются true и false.
VARCHAR2(n BYTE\|CHAR), NVARCHAR2(n), CHAR(n), NCHAR(n), CLOB, NCLOB, LONG	varchar(n) и textДлины считаются в символах, считал ли Oracle байты или символы, поэтому каждое значение помещается.
NUMBER, NUMBER(p,s), FLOAT(b)	numeric(p,s) или bigintNUMBER сохраняет каждую цифру; NUMBER без точности, все значения которого - целые числа до 18 цифр, становится 64-битным целым, а отрицательный масштаб хранит столько же цифр до запятой.
DATE, TIMESTAMP(p)	timestamp(p)DATE в Oracle хранит время с точностью до секунды, поэтому это дата и время. Экспорт в американском или немецком сеансе записывает даты без времени и века (DD-MON-RR); экспортируйте в сеансе ISO (SYYYY-MM-DD HH24:MI:SS), чтобы их сохранить.
TIMESTAMP(7) to TIMESTAMP(9)	шесть цифр долей секунды, седьмая отбрасываетсяPostgreSQL хранит микросекунды; седьмая цифра (100 наносекунд) отбрасывается, а не округляется, поэтому ни одно значение не переходит на следующую секунду или день.	fraction_cut
TIMESTAMP WITH TIME ZONE, TIMESTAMP WITH LOCAL TIME ZONE	timestamptz, те же моментыtimestamptz сохраняет момент; PostgreSQL показывает его в часовом поясе сеанса, а не со смещением, которое было у значения.
JSON (21c), and CLOB holding JSON	jsonbТе же значения JSON; SQL NULL, который экспорт записал как 'null', остается NULL.
BLOB, RAW(n), LONG RAW	byteaБайты остаются байтами.
ROWID, UROWID, XMLTYPE, SDO_GEOMETRY	text, а для XMLTYPE - xmlУ собственных типов Oracle нет аналогов; их значения сохраняют свой текст, включая значения ROWID, хотя вне Oracle они ничего не значат.	type_replaced
collations that ignore case (BINARY_CI)	точное сравнениеPostgreSQL сравнивает текст точно, поэтому столбцы, которые в Oracle не учитывали регистр, теперь различают Ana и ana.	case_insensitive_collation
identity columns, and a sequence a BEFORE INSERT trigger assigns	GENERATED BY DEFAULT AS IDENTITY, продолжается после наибольшего idСкопированные id загружаются как есть, а счетчик продолжается после наибольшего. Столбец, который триггер заполняет из последовательности (способ до 12c), тоже становится столбцом identity.
CREATE SEQUENCE	CREATE SEQUENCE с теми же параметрамиЭкспорт записывает, до какого значения дошла последовательность, как ее START WITH, поэтому она продолжается оттуда.
virtual columns (GENERATED ALWAYS AS ... VIRTUAL)	генерируемые столбцы, STOREDPostgreSQL с 13 по 17 только хранит генерируемые столбцы; формула та же, а \|\| записан так, чтобы NULL считался пустой строкой, как в Oracle.	virtual_as_stored
virtual columns with functions IntoSQL doesn't write, and the \|\| operator	обычный столбецФормула, которую нельзя записать для PostgreSQL, оставляет обычный столбец и строку в отчете; значения, которые экспорт записал для виртуальных столбцов, опускаются, и PostgreSQL не может их вычислить.	generated_not_translated
defaults with functions IntoSQL doesn't write (SYS_GUID(), \|\|)	без значения по умолчаниюЗначение по умолчанию, которое нельзя записать для PostgreSQL, опускается и попадает в отчет.	default_not_translated
CHECK constraints	переводитсяТе же проверки, применяемые при загрузке каждой строки.
CHECK constraints with functions IntoSQL doesn't write	не создаетсяПроверка, которую нельзя записать для PostgreSQL, опускается и попадает в отчет.	check_skipped
CHECK constraints that are DISABLE or ENABLE NOVALIDATE	не создаетсяOracle не применяет эти проверки к уже существующим строкам, поэтому строки могут их нарушать.	check_not_enforced
function-based, bitmap and domain indexes	не создаетсяIntoSQL пишет индексы b-tree по столбцам; остальные создайте заново после загрузки.	index_skipped
foreign keys	внешние ключи, после строкСтроки загружаются в любом порядке, а ключи проверяют их после этого.
foreign keys to tables the export doesn't create, and DISABLE or NOVALIDATE ones	не создаетсяКлюч не может ссылаться на таблицу, которой нет, а у ключа, который Oracle не проверяет, могут быть строки без родителя.	foreign_key_skipped
partitioned tables	одна таблица со строками всех секцийСтроки те же; при необходимости секционируйте таблицу заново в PostgreSQL.	partitions_dropped
NOT NULL columns with values that become NULL	NOT NULL снимаетсяСтолбец, значения которого пришлось заменить на NULL, не может сохранить NOT NULL, иначе загрузка остановится.	not_null_relaxed
views, synonyms, types, packages, procedures, functions and other triggers	перечисляются в отчетеИх код написан на PL/SQL, поэтому они перечислены, чтобы вы создали их заново.	not_converted_objects
exports without CREATE TABLE (data only)	типы определяются по значениямЭкспорт только с данными содержит лишь строки, поэтому типы берутся из значений.	no_schema
expressions in INSERT values other than the ones exports write	NULLКонвертер, который не запускает базу данных, не может их вычислить; to_date, to_timestamp, TO_CLOB, HEXTORAW и другие функции, которые пишут экспорты, читаются.	expression_value
types IntoSQL doesn't know, and object types	textТип text вмещает любое значение; измените тип после загрузки.	unknown_type
dates before year 1 and time zones given as region names	NULLЗначение, которое не является датой, становится NULL, и отчет его подсчитывает.	date_out_of_range
NaN and Infinity in BINARY_FLOAT and BINARY_DOUBLE	NaN и Infinity сохраняютсяТипы с плавающей точкой в PostgreSQL хранят NaN и бесконечность.
NUL characters in text	удаляетсяtext в PostgreSQL не может содержать символ NUL. Он удаляется, и отчет подсчитывает такие значения.	nul_removed
text that isn't valid in its encoding	неверные байты заменяются символом заменыТекст в PostgreSQL должен быть корректным UTF-8.	invalid_utf8_replaced
text decoded twice (Ã±)	копируется как естьСкрипт содержит именно эти символы, поэтому они копируются; отчет на них указывает.	mojibake_suspected

Загрузите скрипт в PostgreSQL

Скрипт создает таблицы, копирует строки, затем добавляет ключи, индексы и внешние ключи, поэтому загрузка идет быстро и останавливается на первой ошибке.

$psql -v ON_ERROR_STOP=1 -d mydb -f oracle.postgresql.sql

Внешние ключи добавляются как NOT VALID, а затем проверяются. Если строка ссылается на несуществующую строку, скрипт сообщает, какое ограничение нарушено, а не прерывается на полпути.

Для большого дампа сначала создайте базу данных и запустите скрипт через psql на той же машине, где работает сервер.

IntoSQL Pro загружает файл прямо в PostgreSQL из приложения для компьютера или командной строки, без написания скрипта.

Как создать дамп Oracle для лучшего результата

Конвертер читает то, что есть в дампе. Эти параметры сохраняют в нем больше ваших данных:

В SQL Developer выберите Tools, Database Export, с DDL и форматом данных Insert. SQLcl записывает то же самое с SET SQLFORMAT INSERT.
Выполните ALTER SESSION SET NLS_DATE_FORMAT = 'SYYYY-MM-DD HH24:MI:SS' перед экспортом: формат по умолчанию записывает даты без времени и века.
Файл .dmp из exp или expdp двоичный: импортируйте его в базу Oracle, например в Oracle Database Free в Docker, и экспортируйте через SQL Developer.

Что не конвертируется

Представления, пакеты, процедуры, функции, триггеры, типы и синонимы написаны на PL/SQL. Они перечислены в отчете по имени и никогда не удаляются молча, чтобы вы могли переписать их для PostgreSQL.

Вопросы о Oracle в PostgreSQL

Нет ответа на ваш вопрос? Напишите нам через страницу контактов, и вам ответит человек. Страница контактов

Мой дамп загружается на сервер?

Нет. Конвертер работает в вашем браузере и читает дамп с диска по частям, поэтому он никогда не покидает ваш компьютер. Приложение для компьютера и командная строка тоже конвертируют на вашем компьютере.

Насколько большим может быть дамп?

Конвертер никогда не держит весь дамп в памяти: он читает файл дважды, по диапазонам, и пишет скрипт по ходу чтения. Дампы в несколько гигабайт конвертируются в браузере, в приложении для компьютера и в командной строке.

Можно ли конвертировать файл .dmp из exp или expdp?

Не напрямую: Data Pump и исходный Export пишут двоичный формат, который читает только Oracle. Импортируйте его в любую базу Oracle (Oracle Database Free работает в Docker) и экспортируйте через Database Export в SQL Developer или SQLcl. Конвертер сообщает об этом, когда получает файл .dmp.

Из каких версий Oracle может быть экспорт?

Экспорты из SQL Developer и SQLcl с определениями таблиц из DBMS_METADATA, из Oracle 11g и новее. Каждый тестовый экспорт создан в Oracle 21c и 23ai, в американском, немецком и ISO-сеансах, и сверен с источником ячейка за ячейкой.

Что происходит с NUMBER, DATE и другими типами Oracle?

NUMBER становится numeric или bigint, если каждое значение целое; DATE становится timestamp и сохраняет время; CLOB - text, BLOB и RAW - bytea, BINARY_DOUBLE - double precision с NaN и Infinity, а интервалы - interval.

Сохраняются ли столбцы identity и последовательности?

Да. Столбцы identity остаются столбцами identity и продолжаются после наибольшего id, как и столбец, который триггер заполняет из последовательности. Последовательности продолжаются с того значения, до которого, по данным экспорта, они дошли.

А пакеты, процедуры и триггеры PL/SQL?

Они перечислены в отчете по имени и не конвертируются, потому что каждая база данных пишет их на своем языке.

На каких версиях PostgreSQL загружается скрипт?

PostgreSQL с 13 по 18. Один и тот же скрипт загружается на каждой из них и останавливается на первой ошибке, если что-то пойдет не так.

id	bigint not null
name	varchar(100) not null
email	varchar(150) not null
country	varchar(2) not null
is_active	smallint not null
signed_up	timestamp(0)
created_at	timestamp(0) not null