>>-MOVE TABLE--+--------+--source_table--TO DATABASE--database-->
               '-owner.-'
 
>--+-----------------------------------+------------------------>
   '-RENAME--+------------+--new_table-'
             '-new_owner.-'
 
                                            .-RESTRICT-.
>--+-------------------------------------+--+----------+-------><
   '-WITH--(--PRIVILEGES--+---------+--)-'  '-CASCADE--'
                          '-, ROLES-'
 

The MOVE TABLE statement provides a way to move a permanent table from one database to another on the same Extended Parallel Server instance. When the table is moved, many of its objects, including any indexes and fragmentation strategy, are preserved, but the data values in the table are not physically moved to the destination database. The database server transfers only the system catalog information describing source_table to the destination database. For this reason, the performance of MOVE TABLE depends on the time that is required to transfer the system catalog data, but not on the size of source table.

If indexes exist on source table, the data values of the index are not moved. Only the system catalog information is moved to the destination database. By default, all roles assigned to source table are dropped from the source database.

The destination table has, by default, restricted privileges. Only the owner of source table has privileges on new table. Also by default, all roles assigned to source table are dropped from new table. In the following basic example, the privileges of source table are not preserved in the destination database:

MOVE TABLE employee TO DATABASE payroll;

You can preserve the privileges and roles of the original table using the WITH clause. For more information, see Preserving Table Privileges.

Only users with DBA privileges on both the source and destination databases can use the MOVE TABLE statement.

The following types of tables cannot be moved using the MOVE TABLE statement:

• System catalog tables
• Temporary tables (TEMP or SCRATCH)
• Tables not currently in the database
• Active violations tables
• Tables appearing in the FROM clause of a GK index
• System database tables (tables in sysmaster and sysutils)

If the destination database already contains a table with the same name as source table, the MOVE TABLE statement fails with an error. Similarly, if any dependent objects of source table have names that already exist in the destination database, the MOVE TABLE statement fails and returns an error.

The source table can be either fragmented or non-fragmented. You must keep the dbspace and fragmentation schema in mind, however, because the data values of the destination table still reside in the same dbspace. MOVE TABLE does not provide a mechanism to move data from one dbspace to another.

The MOVE TABLE statement can only move tables between databases. Use the RENAME TABLE statement to move a table within the same database.