Migrating all tables using the sa_migrate system procedure

You can migrate all tables using the sa_migrate system procedure.

Prérequis

You must have the following system privileges:

CREATE TABLE or CREATE ANY TABLE (if you are not the base table owner)
SELECT ANY TABLE (if you are not the base table owner)
INSERT ANY TABLE (if you are not the base table owner)
ALTER ANY TABLE (if you are not the base table owner)
CREATE ANY INDEX (if you are not the base table owner)
DROP ANY TABLE (if you are not the base table owner)

You must already have a user to own the migrated tables in the target database.

To create an external login, you must have the MANAGE ANY USER system privilege.

Contexte et remarques

Tables that have the same name, but different owners, in the remote database all belong to one owner in the target database. For these reasons, you should migrate tables associated with one owner at a time.

If you do not want all the migrated tables to be owned by the same user on the target database, you must run the sa_migrate procedure for each owner on the target database, specifying the local-table-owner and owner-name arguments.

Task

From Interactive SQL, connect to the target database.
Create a remote server to connect to the remote database.
(Optional) Create an external login to connect to the remote database. This is only required when the user has different passwords on the target and remote databases, or when you want to log in using a different user ID on the remote database than the one you are using on the target database.

In the SQL Statements pane, run the sa_migrate system procedure. Supplying NULL for both the table-name and owner-name parameters migrates all the tables in the database, including system tables.

For example:

CALL sa_migrate( 'local_user1', 'rmt_server1', NULL, 'remote_user1', NULL, 1, 1, 1 );

Résultat

This procedure calls several procedures in turn and migrates all the remote tables belonging to the user remote_user1 using the specified criteria.

Migrating all tables using the sa_migrate system procedure

Task

See also