OCI: Datapump between 23ai ADB and 19c ADB using database link

Now that we know how to manually create a 23ai ADB in OCI, that we also know how to create a database link between a 23ai ADB and a 19C ADB, let's now try a datapump between a 19c ADB and a 23ai ADB (oh, this is also a migration method, right?) using a database link.

First, let's remind our environment:

[fred@myvm ~]$ cd wallet_23ai
[fred@myvm wallet_23ai]$ export TNS_ADMIN=$(pwd)
[fred@myvm wallet_23ai]$ sqlplus admin/"Iw0ntt3lly0u"@sandbox01_high
SQL> select banner_full from v$version;
BANNER_FULL
--------------------------------------------------------------------------------
Oracle Database 23ai Enterprise Edition Release 23.0.0.0.0 - Production
Version 23.6.0.24.10
SQL> select banner_full from v$version@TO_19C;
BANNER_FULL
--------------------------------------------------------------------------------
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.25.0.1.0
SQL>

Let's datapump that ~ 50 million rows table from our 19C ADB:

SQL> select count(*) from USER_19C.TABLE_19C@TO_19C;
COUNT(*)
----------
 48930604
SQL>

Let's create a simple datapump parfile (nothing specific compared to a "classic" Oracle database):

[fred@myvm ~]$ cat imp.par
TABLES=USER_19C.TABLE_19C
TABLE_EXISTS_ACTION=append
REMAP_SCHEMA=USER_19C:USER_23C
network_link=TO_19C
encryption_pwd_prompt=no
directory=DATA_PUMP_DIR
logfile=imp.log
[fred@myvm ~]$

And run it:

[fred@myvm ~]$ impdp admin/"Iw0ntt3lly0u"@sandbox01_high parfile=imp.par
Import: Release 21.0.0.0.0 - Production on Thu Nov 21 15:47:06 2024
Version 21.14.0.0.0
Copyright (c) 1982, 2024, Oracle and/or its affiliates.  All rights reserved.
Connected to: Oracle Database 23ai Enterprise Edition Release 23.0.0.0.0 - Production
Starting "ADMIN"."SYS_IMPORT_TABLE_01":  admin/********@sandbox01_high parfile=imp.par
Processing object type TABLE_EXPORT/TABLE/TABLE
. . imported "USER_23C"."TABLE_19C"               48930604 rows
Processing object type TABLE_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Job "ADMIN"."SYS_IMPORT_TABLE_01" completed successfully at Thu Nov 21 16:09:50 2024 elapsed 0 00:22:41
[fred@myvm ~]$

That was very easy and pretty fast: 22 minutes for 48 million lines (with no parallel, this example table was not partitioned).