The DROP SUBSCRIPTION statement is used to delete data that is no longer needed in a replica database by dropping the subscription that was used to retrieve the data from the master database. To issue the DROP SUBSCRIPTION statement, autocommit must be set off.
When issued on a master, the DROP SUBSCRIPTION statement removes a subscription for the specified replica.
When issued on a replica, the DROP SUBSCRIPTION statement removes a subscription from that replica.
By default, the data of a subscription is deleted in one transaction. If the amount of data is large, for example, tens of thousands of rows, it is recommended that the COMMITBLOCK be defined. When using the COMMITBLOCK option the data is deleted in more than one transaction. This ensures good performance for the operation.
In a replica, you can define that the DROP SUBSCRIPTION statement uses table-level pessimistic locking when it is initially executed. If the PESSIMISTIC mode is specified, all other concurrent access to the table affected is blocked until the drop has completed. If the optimistic mode is used, the DROP SUBSCRIPTION can fail due to a concurrency conflict.
A subscription can be dropped also from the master database. In this case, the replica name is included in the command. Names of all replicates that have been registered in the master database can be found in the SYS_SYNC_REPLICAS table. This operation deletes only the internal information about the subscription for this replica. The actual data in the replica is kept.
Dropping a subscription from the master is useful when a replica is no longer using the subscription and the replica has not dropped the subscription itself. Dropping old subscriptions releases old history data from the database. This history data is automatically deleted from the master databases after dropping the subscription.
If a replica’s subscription has been dropped in the master database, the replica will receive the full data in the next refresh.
If a subscription is dropped in this case, DROP SUBSCRIPTION also drops the publication registration if the last subscription to the publication was dropped. Otherwise, registration must be dropped explicitly using the DROP PUBLICATION REGISTRATION statement or MESSAGE APPEND UNREGISTER PUBLICATION.
You can define the DROP SUBSCRIPTION statement to use table-level pessimistic locking when it is initially executed. If the PESSIMISTIC mode is specified, all other concurrent access to the tables affected is blocked until the import has completed. If the optimistic mode is used, the DROP SUBSCRIPTION can fail due to a concurrency conflict.
When a transaction acquires an exclusive lock to a table, the General.TableLockWaitTimeout parameter setting determines the transaction’s wait period until the exclusive or shared lock is released.
Return values
Error code
Description
13047
No privileges for operation
25004
Dynamic parameters are not supported
25009
Replica replica_name not found
25010
Publication publication_name not found
25019
Database is not a replica database
25020
Database is not a master database
25041
Subscription to publication publication_name not found
25056
Autocommit not allowed
Example
Drop subscription from a master database
DROP SUBSCRIPTION customers_by_area('south') FROM REPLICA salesman_joe