ALTER SESSION SET CURRENT_SCHEMA = [ USER NAME]

ALTER SESSION SET CURRENT_SCHEMA = [ USER NAME]


The ALTER SESSION SET CURRENT_SCHEMA = [USER NAME] allows you to switch your current session to that schema user.
Usually you will log in as sys or system to execute the above. In short this gives you the option to act as a schema user
without knowing the schema user password. The settings persists for the duration of the
session or until you issue another
ALTER SESSION SET CURRENT_SCHEMA statement.

e.g.

ALTER SESSION SET CURRENT_SCHEMA = OE

SQL> EXECUTE DBMS_LOGSTDBY.INSTANTIAT...

ORA-06512: at “SYS.DBMS_LOGSTBY”, LINE 577

Example


SQL> EXECUTE DBMS_LOGSTDBY.INSTANTIATE_TABLE( SCHEMA_NAME => 'OE', TABLE_NAME => 'MF_RESOURCES_SN', dblink => '[DBLINK]');
BEGIN DBMS_LOGSTDBY.INSTANTIATE_TABLE( SCHEMA_NAME => 'OE', TABLE_NAME => 'ORDERS', dblink => 'OE_LINK'); END;

*

ERROR at line 1:

ORA-31631: privileges are required
ORA-06512: at "SYS.DBMS_LOGSTDBY", line 577
ORA-06512: at line 1

Detail

The above error can arise when you are attempting to refresh an object in your logical standby database using
EXECUTE
DBMS_LOGSTDBY.INSTANTIATE_TABLE

In the event that you receive the above error please grant the appropriate permissions affected schema
before re-syncing the object
 
SQL> GRANT LOGSTDBY_ADMINISTRATOR TO [USER];

Once complete revoke LOGSTDBY_ADMINISTRATOR for that schema.

 
Solution

In the event an object within the logical standby database server gets out of sync with the primary database server
you can re-sync the object within the schema as follows. This can be the case with Materialized Views which are not supported
as part of the logical apply for logical standby database servers for Oracle 10g
 
   1. Stop Logical Apply

SQL > ALTER DATABASE STOP LOGICAL STANDBY APPLY;

   2. Create a DB Link from Reporting DB server to Primary DB server with respect to the affected schema.

CREATE [PUBLIC] DATABASE LINK <link_name>
CONNECT TO <user_name>
IDENTIFIED BY <password>
USING '<service_name>';

e.g.

CREATE DATABASE LINK OE_LINK
CONNECT TO OE
IDENTIFIED BY XXXX
USING 'ORDERDB';

   3. Grant “DBA” and “LOGSTDBY_ADMINISTRATOR” role to the affected schema

e.g.

SQL> GRANT DBA TO OE;
SQL> GRANT LOGSTDBY_ADMINISTRATOR TO OE;

   4. Re-sync the object using DBMS_LOGSTDBY.INSTANTIATE_TABLE as follows

SQL> EXECUTE DBMS_LOGSTDBY.INSTANTIATE_TABLE( SCHEMA_NAME => '<schema>', TABLE_NAME => '<table>', dblink => '<dblink>');


e.g.
 

SQL> EXECUTE DBMS_LOGSTDBY.INSTANTIATE_TABLE( SCHEMA_NAME => 'OE', TABLE_NAME => 'ORDERS', dblink => 'OE_LINK');

 
PL/SQL procedure successfully completed.

 
Once complete revoke LOGSTDBY_ADMINISTRATOR for that schema.