This functionality may come very useful when migrating data that cannot be easily exported into any of Data Pump supported flat files due to either column name or field content length limitations. In order to utilize this function you will need to create a script where both .source and .destination files will contain connection strings to the databases and the .script file will provide names for the tables. In its simplest form this script would look similar to the one shown below:

dbMigrate.script

1
2
3
4
5
6
7
8
9

<?xml version="1.0"?>
<Script xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
  <Actions>
  <Action xsi:type="Load">
  <Source>OS_CodePoint</Source>
  <Destination>Code_Point</Destination>
  </Action>
  </Actions>
</Script>

dbMigrate.source

1	database=statmap;user id=USER;password=PASSWORD;timeout=15;pooling=True;enlist=False;integrated security=False;initial catalog=EarthlightDB;data source=dbserver\sqlexpress;cartridge=SqlServer;schema=dbo

dbMigrate.destination

user id=USER;password=PASSWORD;pooling=True;enlist=False;port=5432;protocol=3;ssl=False;sslmode=Disable;timeout=15;connectionlifetime=15;minpoolsize=1;maxpoolsize=20;syncnotification=False;commandtimeout=20;preloadreader=False;useextendedtypes=False;integrated security=False;compatible=2.0.11.0;host=dbserver;database=EarthlightDB;cartridge=PostgreSql;schema=public

As you can see in the example above, this script will migrate OS_CodePoint table from SQLServer to PostgreSQL. The name of the table created in PostgreSQL will be Code_Point.

Of course there is nothing preventing you from migrating more than one table at once by adding additional <Action> entries to your script.

When you would like to tell DataPump which column from the source should be treated as a primary key, you can use the <PrimaryKey> entry. Please note that this is optional.

Browser not supported