▪ In replica database (for DROP SUBSCRIPTION statement): Valid master user
▪ In master database (for DROP SUBSCRIPTION ... REPLICA statement): DBA or SYS_SYNC_ADMIN_ROLE
Usage
Use the DROP SUBSCRIPTION statement in a master or replica database to drop a subscription that the replica database uses to retrieve the data from the master database.
▪ When you run the DROP SUBSCRIPTION ... REPLICA statement on a master database, you remove the subscription for the specified replica database. This operation deletes (from the master database) the internal information about the subscription for the specified replica, including history data; the actual data in the replica database is not deleted.
If a replica subscription has been dropped in the master database but not in the replica database, the replica database receives 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.
Note Names of all replica databases that have been registered in the master database can be found in the SYS_SYNC_REPLICAS table, see SYS_SYNC_REPLICAS.
▪ When you run the DROP SUBSCRIPTION statement on a replica database, you remove the subscription from that replica database including all the data that is associated with the subscription.
To execute the DROP SUBSCRIPTION statement, autocommit mode must be switched off.
Parameters, clauses, keywords, and variables
▪ COMMITBLOCK: Indicates the number of rows deleted before the data is committed. 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, include the COMMITBLOCK keyword in order to delete the data in more than one transaction. This ensures good performance for the operation.
▪ OPTIMISTIC | PESSIMISTIC: Include the PESSIMISTIC keyword so that the DROP SUBSCRIPTION statement uses table-level pessimistic locking when it is initially executed (see Pessimistic and optimistic concurrency controls). If the PESSIMISTIC mode is specified, all other concurrent access to the table is blocked until the drop subscription operation has completed. If you use OPTIMISTIC mode, the DROP SUBSCRIPTION statement might fail due to a concurrency conflict.
When a transaction acquires an exclusive lock to a table, the General.TableLockWaitTimeout parameter determines the wait period of the transaction after which the exclusive or shared lock is released, see General section.
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