For reporting and other uni-directional replication, if the users are allowed to make changes to the replicating tables on target, it may interfere with replication and the integrity of the target data may be lost. This requirement is listed out in the Shareplex Admin Guide Chapter 3 Planning Shareplex Replication section Requirements for target table access. The question arises as to whether the target tables can be set to read only mode to prevent the direct user access.
There is no such mechanism within Shareplex to render the target tables in a read only state. Nor can one make a target table as read only using the SQL command ALTER TABLESPACE <tablespace_name> READ ONLY since it will interfere with the Shareplex Post process. Some of the viable solutions include running the database in restricted mode (ALTER SYSTEM ENABLE RESTRICTED SESSION) or turning off the listener. One may want to go through the Oracle manual SQL*Plus Users Guide and Reference Release 9.2 Chapter 10 SQL*Plus Security that delves on some interesting features. These features include:
PRODUCT_USER_PROFILE Table
Disabling SQL*Plus, SQL, and PL/SQL Commands
Creating and Controlling Roles
Disabling Commands with SQLPLUS -RESTRICT
iSQL*Plus Security
A thorough exposition of the above features is beyond the scope of this solution but one may find them very useful as each one has its own characteristic that may make it more suitable than others in certain circumstances.
