Locking While Copying a Database
When you create the copy database scripts or execute the copy.out script, the locking system takes shared locks on the tables being copied.
When you execute the copy.in script, the locking system takes exclusive locks on the tables being copied in.
Inconsistent Database During Copy Operation
There are two major ways that the database can become inconsistent during the creation of copy database scripts or the execution of the scripts:
• Because shared locks are taken on the tables being copied while the copy scripts are being created or copy.out is being executed, a user can alter the tables that are not locked during this time.
• A user can alter the tables being copied after you run the copy.out script, but before you have run the copy.in script.
If a user drops a table in this interval, it generates an error message. However, if a user makes either of the following changes during this time, no error message is generated, and you do not know about the change:
– Adds or deletes rows from a table
– Adds a table
To ensure the consistency of the tables being copied, lock them exclusively while they are being copied.
Lock Database Exclusively When Copying
Locking ensures the consistency of the tables being copied.
To lock tables exclusively when copying them, use the sql command with -l flag when you run the copy.out script, as follows:
sql -l dbname <copy.out
Last modified date: 08/29/2024