Skip to content
≡

Administrator

Developer

Tools

External Tools

UTL COPYSWAP

UTL_COPYSWAP#

The UTL_COPYSWAP package provides table schema copy, data replication, and table exchange interfaces.

The procedures and functions that make up the UTL_COPYSWAP package are shown in the table below.

Refer to the the description of CHECK_PRECONDITION for the prerequisites for using UTL_COPYSWAP.

Procedures/Functions Description
CHECK_PRECONDITION Checks privileges, session properties, system properties, and replication constraints
COPY_TABLE_SCHEMA Copies the table schema. Afterwards, execute the DDL the user wants on the copied table.
REPLICATE_TABLE Replicates the data.
SWAP_TABLE Swaps the table.
SWAP_TABLE_PARTITION Swaps the table partition.
FINISH Cleans up what was generated by COPY_TABLE_SCHEMA_REPLICATE_TABLE.

CHECK_PRECONDITION#

This procedure checks prerequisites such as privileges, session properties, system properties, and replication constraints for using UTL_COPYSWAP.

The prerequisites to be examined are:

  • Privilege
    Must be the SYS user.

  • Session Properties
    The AUTOCOMMIT property must be FALSE.
    The REPLICATION property must be TRUE.

  • System Properties
    The REPLICATION_PORT_NO property must not be zero.
    The REPLICATION_DDL_ENABLE property must be 1.
    The REPLICATION_ALLOW_DUPLICATE_HOSTS property must be 1.

  • Replication Constraints
    Compressed columns are not supported.
    There should be no related Eager Sender/Receiver thread.

Syntax#

UTL_COPYSWAP.CHECK_PRECONDITION(
  source_user_name IN VARCHAR(128),
  source_table_name IN VARCHAR(128) );

Parameters#

Name In/Output Data Type Description
source_user_name IN VARCHAR2(128) Owner name of the source table
source_table_name IN VARCHAR2(128) Name of the source table

Return Value#

Because it is a stored procedure, there is no return value.

Exception#

If a parameter is entered incorrecly, an exception will be occurred.

Example#

iSQL> CREATE TABLE T1 ( I1 INTEGER PRIMARY KEY, V1 VARCHAR(1024) );
Create success.
iSQL> EXEC UTL_COPYSWAP.CHECK_PRECONDITION( 'SYS', 'T1' );
[SESSION PROPERTY] AUTOCOMMIT property value must be FALSE.
[SYSTEM PROPERTY] REPLICATION_PORT_NO property value must be larger than 0.
[SYSTEM PROPERTY] REPLICATION_DDL_ENABLE property value must be 1.
[SYSTEM PROPERTY] REPLICATION_ALLOW_DUPLICATE_HOSTS property value must be 1.
Execute success.

COPY_TABLE_SCHEMA#

The procedure to copy Table Schema. After that, execute the DDL the user want on the copied table. The copy destination is as follows.

  • Table basic infromation

  • Column

  • Index

  • Constraint

  • Trigger

  • Comment

  • Partition

Syntax#

UTL_COPYSWAP.COPY_TABLE_SCHEMA(
  target_user_name IN VARCHAR(128),
  target_table_name IN VARCHAR(128),
  source_user_name IN VARCHAR(128),
  source_table_name IN VARCHAR(128) );

Parameters#

Name In/Output Data Type Description
target_user_name IN VARCHAR2(128) Owner name of the target table
target_table_name IN VARCHAR2(128) Name of the target table
source_user_name IN VARCHAR2(128) Owner name of the source table
source_table_name IN VARCHAR2(128) Name of the source table

Return Value#

Because it is a stored procedure, there is no return value.

Example#

If a parameter is entered incorrecly, an exception will be occurred.

Example#

iSQL> CREATE TABLE T1 ( I1 INTEGER PRIMARY KEY, V1 VARCHAR(1024) );
Create success.
iSQL> INSERT INTO T1 VALUES ( 1, 'ABC' );
1 row inserted.
iSQL> ALTER SESSION SET AUTOCOMMIT = FALSE;
Alter success.
iSQL> ALTER SYSTEM SET REPLICATION_DDL_ENABLE = 1;
Alter success.
iSQL> ALTER SYSTEM SET REPLICATION_ALLOW_DUPLICATE_HOSTS = 1;
Alter success.
iSQL> EXEC UTL_COPYSWAP.COPY_TABLE_SCHEMA( 'SYS', 'T1_COPY', 'SYS', 'T1' );
Execute success.
iSQL> SELECT COUNT(*) FROM T1_COPY;
COUNT
-----------------------
0    
1 row selected.
iSQL> ALTER TABLE T1_COPY ALTER TABLESPACE SYS_TBS_DISK_DATA;
Alter success.

REPLICATE_TABLE#

The procedure to replicate data using replication.

Syntax#

UTL_COPYSWAP.REPLICATE_TABLE(
  replication_name IN VARCHAR(35),
  target_user_name IN VARCHAR(128),
  target_table_name IN VARCHAR(128),
  source_user_name IN VARCHAR(128),
  source_table_name IN VARCHAR(128),
  sync_parallel_factor IN INTEGER DEFAULT 8,
  receiver_applier_count IN INTEGER DEFAULT 8 );

Parameters#

Name In/Output Data Type Description
replication_name IN VARCHAR2(35) Name of the replication
target_user_name IN VARCHAR2(128) Owner name of the target table
target_table_name IN VARCHAR2(128) Name of the target table
source_user_name IN VARCHAR2(128) Owner name of the source table
source_table_name IN VARCHAR2(128) Name of the source table
sync_parallel_factor IN INTEGER Parallel factor to apply to initial synchronization
receiver_applier_count IN INTEGER Parallel factor to apply to incremental synchronization

Return Value#

Because it is a stored procedure, there is no return value.

Exception#

If a parameter is entered incorrecly, an exception will be occurred.

Example#

iSQL> CREATE TABLE T1 ( I1 INTEGER PRIMARY KEY, V1 VARCHAR(1024) );
Create success.
iSQL> INSERT INTO T1 VALUES ( 1, 'ABC' );
1 row inserted.
iSQL> ALTER SESSION SET AUTOCOMMIT = FALSE;
Alter success.
iSQL> ALTER SYSTEM SET REPLICATION_DDL_ENABLE = 1;
Alter success.
iSQL> ALTER SYSTEM SET REPLICATION_ALLOW_DUPLICATE_HOSTS = 1;
Alter success.
iSQL> EXEC UTL_COPYSWAP.COPY_TABLE_SCHEMA( 'SYS', 'T1_COPY', 'SYS', 'T1' );
Execute success.
iSQL> SELECT COUNT(*) FROM T1_COPY;
COUNT
-----------------------
0    
1 row selected.
iSQL> ALTER TABLE T1_COPY ALTER TABLESPACE SYS_TBS_DISK_DATA;
Alter success.
iSQL> EXEC UTL_COPYSWAP.REPLICATE_TABLE( 'REP_LOCAL', 'SYS', 'T1_COPY', 'SYS', 'T1' );
Execute success.
iSQL> SELECT COUNT(*) FROM T1_COPY;
COUNT
-----------------------
1    
1 row selected.

SWAP_TABLE#

This is a procedure to complete synchronization using replication and exchange tables.

The exchange target is as follows.

  • Table basic information

  • Column

  • Index

  • Constraint

  • Trigger

  • Comment

  • Partition

Syntax#

UTL_COPYSWAP.SWAP_TABLE(  
  replication_name IN VARCHAR(35),  
  target_user_name IN VARCHAR(128),  
  target_table_name IN VARCHAR(128),  
  source_user_name IN VARCHAR(128),  
  source_table_name IN VARCHAR(128),  
  force_to_rename_encrypt_column IN BOOLEAN DEFAULT FALSE,  
  ignore_foreign_key_child IN BOOLEAN DEFAULT FALSE );

Parameters#

Name In/Output Data Type Description
replication_name IN VARCHAR2(35) Name of the replicaiton
target_user_name IN VARCHAR2(128) Owner name of the target table
target_table_name IN VARCHAR2(128) Name of the target table
source_user_name IN VARCHAR2(128) Owner name of the source table
source_table_name IN VARCHAR2(128) Name of the source table
force_to_rename_encrypt_column IN BOOLEAN Set to TRUE if there is an encryption column and the encryption module supports Rename.
ignore_foreign_key_child IN BOOLEAN Set to TRUE if there is a table referencing the source table.

Result Value#

Because it is a stored procedure, there is no result value.

Exception#

If a parameter is entered incorrecly, an exception will be occurred.

Example#

iSQL> CREATE TABLE T1 ( I1 INTEGER PRIMARY KEY, V1 VARCHAR(1024) );
Create success.
iSQL> INSERT INTO T1 VALUES ( 1, 'ABC' );
1 row inserted.
iSQL> ALTER SESSION SET AUTOCOMMIT = FALSE;
Alter success.
iSQL> ALTER SYSTEM SET REPLICATION_DDL_ENABLE = 1;
Alter success.
iSQL> ALTER SYSTEM SET REPLICATION_ALLOW_DUPLICATE_HOSTS = 1;
Alter success.
iSQL> EXEC UTL_COPYSWAP.COPY_TABLE_SCHEMA( 'SYS', 'T1_COPY', 'SYS', 'T1' );
Execute success.
iSQL> SELECT COUNT(*) FROM T1_COPY;
COUNT
-----------------------
0    
1 row selected.
iSQL> ALTER TABLE T1_COPY ALTER TABLESPACE SYS_TBS_DISK_DATA;
Alter success.
iSQL> EXEC UTL_COPYSWAP.REPLICATE_TABLE( 'REP_LOCAL', 'SYS', 'T1_COPY', 'SYS', 'T1' );
Execute success.
iSQL> SELECT COUNT(*) FROM T1_COPY;
COUNT
-----------------------
1    
1 row selected.
iSQL> INSERT INTO T1 VALUES ( 2, 'XYZ' );
1 row inserted.
iSQL> COMMIT;
Commit success.
iSQL> EXEC UTL_COPYSWAP.SWAP_TABLE( 'REP_LOCAL', 'SYS', 'T1_COPY', 'SYS', 'T1' );
Execute success.
iSQL> SELECT COUNT(*) FROM T1_COPY;
COUNT
-----------------------
2    
1 row selected.

SWAP_TABLE_PARTITION#

The procedure to complete synchronization using replication and exchange table partitions. The exchange target is as follows.

  • Partition

Syntax#

PROCEDURE swap_table_partition(  
  replication_name IN VARCHAR(35),  
  target_user_name IN VARCHAR(128),  
  target_table_name IN VARCHAR(128),  
  source_user_name IN VARCHAR(128),  
  source_table_name IN VARCHAR(128),  
  table_partition_name IN VARCHAR(128) );

Parameters#

Name In/Output Data Type Description
replication_name IN VARCHAR2(35) Name of the replication
target_user_name IN VARCHAR2(128) Owner name of the target table
target_table_name IN VARCHAR2(128) Name of the target table
source_user_name IN VARCHAR2(128) Owner name of the source table
source_table_name IN VARCHAR2(128) Name of the source table
table_partition_name IN VARCHAR2(128) Table partition to be exchanged

Return Value#

Because it is a stored procedure, there is no return value.

Exception#

If a parameter is entered incorrecly, an exception will be occurred.

Example#

iSQL> create table t1 (i1 int, i2 int)
partition by range (i1)
(
    partition p1 values less than (10),
    partition p2 values less than (20),
    partition p3 values default
)tablespace sys_tbs_disk_data;
Create success.

iSQL> alter table t1 add constraint pk_t1 primary key(i1) using index local
(
    partition  pk_p1 on p1 tablespace SYS_TBS_DISK_DATA,
    partition  pk_p2 on p2 tablespace SYS_TBS_DISK_DATA,
    partition  pk_p3 on p3 tablespace SYS_TBS_DISK_DATA
);
Alter success.
iSQL> INSERT INTO T1 VALUES ( 15, 15 );
1 row inserted.
iSQL> ALTER SESSION SET AUTOCOMMIT = FALSE;
Alter success.
iSQL> ALTER SYSTEM SET REPLICATION_DDL_ENABLE = 1;
Alter success.
iSQL> ALTER SYSTEM SET REPLICATION_ALLOW_DUPLICATE_HOSTS = 1;
Alter success.
iSQL> EXEC UTL_COPYSWAP.COPY_TABLE_SCHEMA( 'SYS', 'T1_COPY', 'SYS', 'T1' );
Execute success.
iSQL> SELECT COUNT(*) FROM T1_COPY;
COUNT
-----------------------
0    
1 row selected.
iSQL> ALTER TABLE T1_COPY ALTER TABLESPACE SYS_TBS_MEM_DATA;
Alter success.
iSQL> EXEC UTL_COPYSWAP.REPLICATE_TABLE( 'REP_LOCAL', 'SYS', 'T1_COPY', 'SYS', 'T1' );
Execute success.
iSQL> SELECT COUNT(*) FROM T1_COPY;
COUNT
-----------------------
1    
1 row selected.
iSQL> INSERT INTO T1 VALUES ( 16, 16 );
1 row inserted.

iSQL> commit ;
iSQL> EXEC UTL_COPYSWAP.SWAP_TABLE_PARTITION( 'REP_LOCAL', 'SYS', 'T1_COPY', 'SYS', 'T1','P2' );
Execute success.
iSQL> SELECT COUNT(*) FROM T1_COPY;
COUNT
-----------------------
2    
1 row selected.

FINISH#

Cleans up what was generated by COPY_TABLE_SCHEMA_REPLICATE_TABLE.

Syntax#

UTL_COPYSWAP.FINISH(  
  replication_name IN VARCHAR(35),  
  target_user_name IN VARCHAR(128),  
  target_table_name IN VARCHAR(128),  
  print_all_errors IN BOOLEAN DEFAULT FALSE );

Parameters#

Name In/Output Data Type Description
replication_name IN VARCHAR2(35) Name of the replication
target_user_name IN VARCHAR2(128) Owner name of the target table
target_table_name IN VARCHAR2(128) Name of the target table
print_all_errors IN BOOLEAN Set to TRUE to display replication-related errors.

Result Value#

Because it is a stored procedure, there is no return value.

Exception#

If a parameter is entered incorrecly, an exception will be occurred.

Example#

iSQL> CREATE TABLE T1 ( I1 INTEGER PRIMARY KEY, V1 VARCHAR(1024) );
Create success.
iSQL> INSERT INTO T1 VALUES ( 1, 'ABC' );
1 row inserted.
iSQL> ALTER SESSION SET AUTOCOMMIT = FALSE;
Alter success.
iSQL> ALTER SYSTEM SET REPLICATION_DDL_ENABLE = 1;
Alter success.
iSQL> ALTER SYSTEM SET REPLICATION_ALLOW_DUPLICATE_HOSTS = 1;
Alter success.
iSQL> EXEC UTL_COPYSWAP.COPY_TABLE_SCHEMA( 'SYS', 'T1_COPY', 'SYS', 'T1' );
Execute success.
iSQL> SELECT COUNT(*) FROM T1_COPY;
COUNT
-----------------------
0    
1 row selected.
iSQL> ALTER TABLE T1_COPY ALTER TABLESPACE SYS_TBS_DISK_DATA;
Alter success.
iSQL> EXEC UTL_COPYSWAP.REPLICATE_TABLE( 'REP_LOCAL', 'SYS', 'T1_COPY', 'SYS', 'T1' );
Execute success.
iSQL> SELECT COUNT(*) FROM T1_COPY;
COUNT
-----------------------
1    
1 row selected.
iSQL> INSERT INTO T1 VALUES ( 2, 'XYZ' );
1 row inserted.
iSQL> COMMIT;
Commit success.
iSQL> EXEC UTL_COPYSWAP.SWAP_TABLE( 'REP_LOCAL', 'SYS', 'T1_COPY', 'SYS', 'T1' );
Execute success.
iSQL> SELECT COUNT(*) FROM T1_COPY;
COUNT
-----------------------
2    
1 row selected.
iSQL> EXEC UTL_COPYSWAP.FINISH( 'REP_LOCAL', 'SYS', 'T1_COPY' );
Execute success.
iSQL> SELECT COUNT(*) FROM T1_COPY;
[ERR-31031 : Table or view was not found :
0001 : SELECT COUNT(*) FROM T1_COPY
^      ^

Notes#

  • To replicate data using the REPLICATE_TABLE procedure, free space is required in the tablespace in proportion to the size of the source table. Log files created by the REPLICATE_TABLE procedure are not removed by Checkpoint until the REPLICATE_TABLE procedure is terminated.

  • While using the UTL_COPYSWAP package, replication must be able to resolve the DML that applies to the source table. DML that cannot be analyzed in replication may be lost.

    • When executing DML on the source table, the REPLICATION session property must be TRUE.
    • If the source table is replication target table, replication must be stopped at the remote server that corresponds to the source table so that replication does not reflect the data in the source table.

  • When dropping a target table using the FINISH procedure, if the RECYCLEBIN_ENABLE property value is 1, then it is moved to the recycle bin.