Audit Table changes with Trigger by example

==================================
General
==================================
This is an example of auditing DML operations on a table using a trigger, and logging these changes into an audit table.
Audit table resides on a dedicated Tablespace.
Optionally this table can be partitioned by change date.

==================================
Trigger code
==================================
The trigger is inserting records into Audit table whenever there is an update on the base table.

CREATE OR REPLACE TRIGGER AUD_CUSTOMERS_TRG
  AFTER UPDATE OR INSERT OR DELETE ON CUSTOMERS
  FOR EACH ROW
DECLARE
  v_oper_user VARCHAR2(30);
BEGIN

  SELECT  UPPER(SUBSTR(SYS_CONTEXT('USERENV','OS_USER'),1,30)) INTO v_oper_user 
    FROM DUAL;

  IF INSERTING THEN
    INSERT INTO AUD_CUSTOMERS
      (change_date, change_action, old_new, customer_id, name, oper_user)
    VALUES
      (SYSDATE, 'INSERT', 'NEW', :NEW.customer_id, :NEW.name, v_oper_user);
  ELSIF UPDATING THEN
    INSERT INTO AUD_CUSTOMERS
      (change_date, change_action, old_new, customer_id, name, oper_user)
    VALUES
      (SYSDATE, 'UPDATE', 'OLD', :OLD.customer_id, :OLD.name,  v_oper_user);
    INSERT INTO AUD_CUSTOMERS
      (change_date, change_action, old_new, customer_id, name, oper_user)
    VALUES
      (SYSDATE, 'UPDATE', 'NEW', :NEW.customer_id, :NEW.name, v_oper_user);
  ELSE
    INSERT INTO AUD_CUSTOMERS
      (change_date, change_action, old_new, customer_id, name, oper_user)
    VALUES
      (SYSDATE, 'DELETE', 'OLD', :OLD.customer_id, :OLD.name,  v_oper_user);
  END IF;
END;

==================================

The DDL of the Audit Table

==================================

The structure of the Audit table is same as the base table, with addition of four fields:

- change_date

- change_action: INSERT/UPDATE/DELETE

- old_new: NEW/OLD

- oper_user: The OS login of the user who made this change.

CREATE TABLE AUD_CUSTOMERS(

change_date                DATE NOT NULL, 

change_action              VARCHAR2(10 BYTE) NOT NULL,

old_new                    VARCHAR2(3 BYTE) NOT NULL,

customer_id                VARCHAR2(5 BYTE)  ,                          

name                       VARCHAR2(30 BYTE) ,                          

oper_user                  VARCHAR2(30 BYTE)  

)

tablespace AUDIT_TBS NOLOGGING;

alter table AUD_CUSTOMERS

  add constraint AUD_CUSTOMERS_PK primary key (change_date, change_action, old_new)

  using index 

  tablespace AUDIT_TBS;

grant select, insert, update, delete, references, alter, index on AUD_CUSTOMERS to MANAGER;

grant select on AUD_CUSTOMERS to SELECTOR;

grant select on AUD_CUSTOMERS to USERS_ROLE;

Remote Desktop, mstsc options.

Remote Desktop, mstsc, options

useful options when using mstsc

mstsc /v:222:333:444:555
  Connect to server 222:333:444:555

mstsc /v:222:333:444:555:44
  Connect to server 222:333:444:555 on port 44

mstsc /v:222:333:444:555 /f 
  Starts Remote Desktop Connection in full-screen mode.

mstsc /v:222:333:444:555 /span 
  Matches the remote desktop width and height with the local virtual desktop

mstsc /v:222:333:444:555 /w:[width] /h:[height] 
  Starts Remote Desktop Connection in width and height.

mstsc /v:222:333:444:555 /edit  "connection file"
  Opens  "connection file".rdp for editing

mstsc /v:222:333:444:555 /console
  An older (before Windows 7 and Windows Server 2008) syntax.
  By using it you're connecting to the Console Session on the server. 

mstsc /v:222:333:444:555 /admin
  This mode differed from normal connection in few ways: 
  Disable Remote Desktop Services client access licensing, and other minor changes.

mstsc /v:222:333:444:555 /public
  Connect to server 222:333:444:555 in public mode.


console and admin connection modes
In Windows Server 2003, you can start the RDC client (mstsc.exe) by using the /console switch to remotely connect to the physical console session on the server (also known as session 0). 
In Windows Server 2008 or Windows Server 2008 R2, the /console switch has been deprecated. 

In Windows Server 2008 and Windows Server 2008 R2, session 0 is a noninteractive session that is reserved for services. 


You can use the new /admin switch to remotely connect to a Windows Server 2008-based server for administrative purposes.

If you you /console option with RDC 6.0 client or higher, the /console switch is silently ignored.

tsadminStart -> Run -> tsadmin -> Would open a window of Terminal Services Manager.
This is a way to connect to other Terminal Server sessions on a server.

Check on Oracle version, Oracle Edition

=========================
General
=========================
1. How to tell if Oracle is Enterprise Edition (EE) or Standard Edition (SE)
2. How to view oracle Component versions.


=========================
Oracle SE or EE?
=========================

Option A. - check in V$VERSION

SQL> SELECT * FROM V$VERSION;

For Standard Edition the output is:

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Release 11.1.0.7.0 - 64bit Production
PL/SQL Release 11.1.0.7.0 - Production
CORE    11.1.0.7.0      Production
TNS for Linux: Version 11.1.0.7.0 - Production
NLSRTL Version 11.1.0.7.0 - Production


For Enterprise Edition the output is:

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.1.0.5.0 - Prod
PL/SQL Release 10.1.0.5.0 - Production
CORE 10.1.0.5.0 Production
TNS for Linux: Version 10.1.0.5.0 - Production
NLSRTL Version 10.1.0.5.0 - Production

Option B. - check on the server, in file context.xml

/software/oracle/111/inventory/Components21/oracle.server/11.1.0.6.0>% 
less context.xml | grep  s_serverInstallType

For Standard Edition the output is:
<VAR NAME="s_serverInstallType" TYPE="String" DESC_RES_ID="s_serverInstallType_DESC" SECURE="F" VAL="SE" ADV="F" CLONABLE="F" USER_INPUT="CALC"/>

For Standard Edition the output is:
<VAR NAME="s_serverInstallType" TYPE="String" DESC_RES_ID="s_serverInstallType_DESC" SECURE="F" VAL="EE" ADV="F" CLONABLE="F" USER_INPUT="CALC"/>

=========================
Oracle components versions

=========================

SELECT * FROM SYS.PRODUCT_COMPONENT_VERSION;

PRODUCT                        VERSION                        STATUS

------------------------------ ------------------------------ ------------------------------

NLSRTL                         11.2.0.4.0                     Production

Oracle Database 11g            11.2.0.4.0                     64bit Production

PL/SQL                         11.2.0.4.0                     Production

TNS for Linux:                 11.2.0.4.0                     Production

Partition an existing Table with DBMS_REDEFINITION, by Example.

=============================
General
=============================
Example of partitioning existing table using DBMS_REDEFINITION package.

Please see this post for general description of DBMS_REDEFINITION

=============================
General steps to partition existing table with DBMS_REDEFINITION
=============================

A. Check is the redefinition is possible 
DBMS_REDEFINITION.can_redef_table

B. Manual steps.
B-1. Check the space needed. 
You may need to create the partitioned table on a new Tablespace/Add datafile.

B-2. Manually create a new partitioned table. 
         This table would be manually dropped at the end of the process.

C. Start the redefinition 
DBMS_REDEFINITION.start_redef_table

D. Copy dependent objects
DBMS_REDEFINITION.copy_table_dependents
An alternative to this step is to manually create Constraints and Indexes on the new table.

E. Check for errors.
Query DBA_REDEFINITION_ERRORS

F. Synchronize data between old and new tables
DBMS_REDEFINITION.sync_interim_table

G. Complete the Redefinition Process
DBMS_REDEFINITION.finish_redef_table

At this point the partitioned table has become the "real" table and their names have been switched in the data dictionary.

H. Drop the interim table.

DROP TABLE ... PURGE;

J. Gather statistics on the new table.
EXEC DBMS_STATS.gather_table_stats

=============================
Example of using  DBMS_REDEFINITION to partition existing table step by step.
=============================

In this example, the table is quite large.
Approx 35,000,000 rows, 10Gb storage space.
It is holding audit data from DBA_FGA_AUDIT_TRAIL.
The idea is to partition the table by timestamp field.

Before starting the Partitioning of a table, need to make few checks
1. Check if the original table could be partitioned.
2. Check the space needed. During partitioning, a new temporary table would be created.
3. Manually create a new partitioned table.


The table before partitioning:

CREATE TABLE STATEMENTS_AUDIT
(
  session_id         NUMBER not null,
  timestamp          DATE,
  db_user            VARCHAR2(120),
  os_user            VARCHAR2(1020),
  userhost           VARCHAR2(512),
  client_id          VARCHAR2(256),
  ext_name           VARCHAR2(4000),
  object_schema      VARCHAR2(120),
  object_name        VARCHAR2(512),
  policy_name        VARCHAR2(120),
  scn                NUMBER,
  sql_text           VARCHAR2(4000),
  sql_bind           VARCHAR2(4000),
  comment$text       VARCHAR2(4000),
  statement_type     VARCHAR2(112),
  extended_timestamp TIMESTAMP(6) WITH TIME ZONE,
  proxy_sessionid    NUMBER,
  global_uid         VARCHAR2(128),
  instance_number    NUMBER,
  os_process         VARCHAR2(64),
  transactionid      RAW(8),
  statementid        NUMBER,
  entryid            NUMBER
)
tablespace COLLECT_TABLE;

CREATE INDEX STATEMENTS_AUDIT_IND1 on STATEMENTS_AUDIT (OBJECT_SCHEMA, OBJECT_NAME) tablespace COLLECT_INDEX;

There is no PK on this table.

A. Check if the Redefinition is possible.

Since there is no PK on the table, Oracle would need to work with ROWIDs.

BEGIN

  DBMS_REDEFINITION.can_redef_table ('MANAGER', 'STATEMENTS_AUDIT', DBMS_REDEFINITION.CONS_USE_ROWID);

END;

/

PL/SQL procedure successfully completed

B-1. Check the space needed. 

SELECT tablespace_name, segment_name, bytes/1024/1024 AS MB 

FROM DBA_SEGMENTS 

WHERE  segment_name='STATEMENTS_AUDIT';

TABLESPACE_NAME                SEGMENT_NAME                           MB
------------------------------ ------------------------------ ----------
COLLECT_TABLE                  STATEMENTS_AUDIT                    10128

SELECT tablespace_name, file_name, (maxbytes-user_bytes)/1024/1024 AS free_Mb  

FROM DBA_DATA_FILES 

WHERE  tablespace_name = 'COLLECT_TABLE';

TABLESPACE_NAME  FILE_NAME                                     FREE_MB

---------------- ------------------------------------------ ----------

COLLECT_TABLE    /oracle_db/db1/oraint/COLLECT_TABLE_1.dbf  3003.04687

COLLECT_TABLE    /oracle_db/db1/oraint/COLLECT_TABLE_2.dbf    580.0625

There is no additional 10Gb on the COLLECT_TABLE tablespace

The new partitioned table would be created in a new tablespace, AUDIT_TBS.

Lets create new Tablespace.

CREATE TABLESPACE AUDIT_TBS DATAFILE '/oracle_db/db1/orainst/AUDIT_TBS_1.dbf' SIZE 200M AUTOEXTEND ON NEXT 200M MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO ;

Tablespace created

B-2. Create new partitioned table on the new tablespace.

The table is partitioned by year range, on field timestamp.

The index is created with LOCAL clause.

CREATE TABLE STATEMENTS_AUDIT_PRT
(
  session_id         NUMBER not null,
  timestamp        DATE,
  db_user            VARCHAR2(120),
  os_user            VARCHAR2(1020),
  userhost           VARCHAR2(512),
  client_id          VARCHAR2(256),
  ext_name           VARCHAR2(4000),
  object_schema      VARCHAR2(120),
  object_name        VARCHAR2(512),
  policy_name        VARCHAR2(120),
  scn                NUMBER,
  sql_text           VARCHAR2(4000),
  sql_bind           VARCHAR2(4000),
  comment$text       VARCHAR2(4000),
  statement_type     VARCHAR2(112),
  extended_timestamp TIMESTAMP(6) WITH TIME ZONE,
  proxy_sessionid    NUMBER,
  global_uid         VARCHAR2(128),
  instance_number    NUMBER,
  os_process         VARCHAR2(64),
  transactionid      RAW(8),
  statementid        NUMBER,
  entryid            NUMBER
)
partition by range (timestamp)
(
  partition P_2010 values less than (TO_DATE('20110101','YYYYMMDD')) tablespace AUDIT_TBS
   storage (initial 64K minextents 1 maxextents unlimited  ),  
  partition P_2011 values less than (TO_DATE('20120101','YYYYMMDD')) tablespace AUDIT_TBS
   storage (initial 64K minextents 1 maxextents unlimited  ),     
  partition P_2012 values less than (TO_DATE('20130101','YYYYMMDD')) tablespace AUDIT_TBS
   storage (initial 64K minextents 1 maxextents unlimited  ),     
  partition P_2013 values less than (TO_DATE('20140101','YYYYMMDD')) tablespace AUDIT_TBS
   storage (initial 64K minextents 1 maxextents unlimited  ),     
  partition P_2014 values less than (TO_DATE('20150101','YYYYMMDD')) tablespace AUDIT_TBS
   storage (initial 64K minextents 1 maxextents unlimited  ),     
  partition P_2015 values less than (TO_DATE('20160101','YYYYMMDD')) tablespace AUDIT_TBS
   storage (initial 64K minextents 1 maxextents unlimited  ),     
  partition P_2016 values less than (TO_DATE('20170101','YYYYMMDD')) tablespace AUDIT_TBS
   storage (initial 64K minextents 1 maxextents unlimited  ),     
  partition P_2017 values less than (TO_DATE('20180101','YYYYMMDD')) tablespace AUDIT_TBS
   storage (initial 64K minextents 1 maxextents unlimited  ),     
  partition P_2018 values less than (TO_DATE('20190101','YYYYMMDD')) tablespace AUDIT_TBS
   storage (initial 64K minextents 1 maxextents unlimited  ),     
  partition P_2019 values less than (TO_DATE('20200101','YYYYMMDD')) tablespace AUDIT_TBS
   storage (initial 64K minextents 1 maxextents unlimited  ),     
  partition P_2020 values less than (TO_DATE('20210101','YYYYMMDD')) tablespace AUDIT_TBS
   storage (initial 64K minextents 1 maxextents unlimited  )
)

NOLOGGING;

CREATE INDEX STATEMENTS_AUDIT_IX01 ON STATEMENTS_AUDIT_PRT (OBJECT_SCHEMA, OBJECT_NAME) LOCAL;

Index created


C. Start the redefinition 
BEGIN
  DBMS_REDEFINITION.start_redef_table('MANAGER', 'STATEMENTS_AUDIT', 'STATEMENTS_AUDIT_PRT',OPTIONS_FLAG => DBMS_REDEFINITION.CONS_USE_ROWID);
END;
/
PL/SQL procedure successfully completed

This is the most heavy step.

D. Copy dependent objects
DECLARE
  num_errors PLS_INTEGER;
BEGIN
  DBMS_REDEFINITION.copy_table_dependents ('MANAGER', 'STATEMENTS_AUDIT', 'STATEMENTS_AUDIT_PRT', DBMS_REDEFINITION.CONS_ORIG_PARAMS, TRUE, TRUE, TRUE, TRUE, num_errors);
END;
/
PL/SQL procedure successfully completed

E. Check for errors.
SELECT object_name, base_table_name, ddl_txt 
FROM DBA_REDEFINITION_ERRORS;

F. Synchronize data between old and new tables
BEGIN
  DBMS_REDEFINITION.sync_interim_table ('MANAGER', 'STATEMENTS_AUDIT', 'STATEMENTS_AUDIT_PRT');
END;
/
PL/SQL procedure successfully completed

G. Complete the Redefintion Process
BEGIN
  DBMS_REDEFINITION.finish_redef_table ('MANAGER', 'STATEMENTS_AUDIT', 'STATEMENTS_AUDIT_PRT');
END;
/
PL/SQL procedure successfully completed

H. Drop the interim table.

Before dropping the inetrim table, check USER_TABLES, USER_SEGMENTS, USER_OBJECTS.

SELECT table_name, tablespace_name, logging, partitioned 
FROM USER_TABLES 
WHERE table_name LIKE 'STATEMENTS_AUDIT%';

TABLE_NAME                     TABLESPACE_NAME                LOGGING PARTITIONED
------------------------------ ------------------------------ ------- -----------
STATEMENTS_AUDIT                                                      YES
STATEMENTS_AUDIT_PRT           COLLECT_TABLE                  NO      NO

SELECT segment_name, partition_name, segment_type, tablespace_name 
FROM USER_SEGMENTS 
WHERE segment_name like 'STATEMENTS_AUDIT%'

SEGMENT_NAME                   PARTITION_NAME       SEGMENT_TYPE         TABLESPACE_NAME
------------------------------ -------------------- -------------------- ----------------
STATEMENTS_AUDIT_PRT                                TABLE                COLLECT_TABLE
STATEMENTS_AUDIT               P_2010               TABLE PARTITION      AUDIT_TBS
STATEMENTS_AUDIT               P_2011               TABLE PARTITION      AUDIT_TBS
STATEMENTS_AUDIT               P_2012               TABLE PARTITION      AUDIT_TBS
STATEMENTS_AUDIT               P_2013               TABLE PARTITION      AUDIT_TBS
STATEMENTS_AUDIT               P_2014               TABLE PARTITION      AUDIT_TBS
STATEMENTS_AUDIT               P_2015               TABLE PARTITION      AUDIT_TBS
STATEMENTS_AUDIT               P_2016               TABLE PARTITION      AUDIT_TBS
STATEMENTS_AUDIT               P_2017               TABLE PARTITION      AUDIT_TBS
STATEMENTS_AUDIT               P_2018               TABLE PARTITION      AUDIT_TBS
STATEMENTS_AUDIT               P_2019               TABLE PARTITION      AUDIT_TBS
STATEMENTS_AUDIT               P_2020               TABLE PARTITION      AUDIT_TBS
STATEMENTS_AUDIT_IND1                               INDEX                COLLECT_INDEX
STATEMENTS_AUDIT_IX01          P_2010               INDEX PARTITION      AUDIT_TBS
STATEMENTS_AUDIT_IX01          P_2011               INDEX PARTITION      AUDIT_TBS
STATEMENTS_AUDIT_IX01          P_2012               INDEX PARTITION      AUDIT_TBS
STATEMENTS_AUDIT_IX01          P_2013               INDEX PARTITION      AUDIT_TBS
STATEMENTS_AUDIT_IX01          P_2014               INDEX PARTITION      AUDIT_TBS
STATEMENTS_AUDIT_IX01          P_2015               INDEX PARTITION      AUDIT_TBS
STATEMENTS_AUDIT_IX01          P_2016               INDEX PARTITION      AUDIT_TBS
STATEMENTS_AUDIT_IX01          P_2017               INDEX PARTITION      AUDIT_TBS
STATEMENTS_AUDIT_IX01          P_2018               INDEX PARTITION      AUDIT_TBS
STATEMENTS_AUDIT_IX01          P_2019               INDEX PARTITION      AUDIT_TBS
STATEMENTS_AUDIT_IX01          P_2020               INDEX PARTITION      AUDIT_TBS

SELECT object_name, subobject_name, object_id, created, status 
FROM USER_OBJECTS 
WHERE object_name like 'STATEMENTS_AUDIT%';

OBJECT_NAME                    SUBOBJECT_NAME        OBJECT_ID CREATED     STATUS
------------------------------ -------------------- ---------- ----------- --------
STATEMENTS_AUDIT               P_2010                 32047566 01/06/2015  VALID
STATEMENTS_AUDIT               P_2011                 32047567 01/06/2015  VALID
STATEMENTS_AUDIT               P_2012                 32047568 01/06/2015  VALID
STATEMENTS_AUDIT               P_2013                 32047569 01/06/2015  VALID
STATEMENTS_AUDIT               P_2014                 32047570 01/06/2015  VALID
STATEMENTS_AUDIT               P_2015                 32047571 01/06/2015  VALID
STATEMENTS_AUDIT               P_2016                 32047572 01/06/2015  VALID
STATEMENTS_AUDIT               P_2017                 32047573 01/06/2015  VALID
STATEMENTS_AUDIT               P_2018                 32047574 01/06/2015  VALID
STATEMENTS_AUDIT               P_2019                 32047575 01/06/2015  VALID
STATEMENTS_AUDIT               P_2020                 32047576 01/06/2015  VALID
STATEMENTS_AUDIT                                      32047565 01/06/2015  VALID
STATEMENTS_AUDIT_IND1                                  7728253 24/03/2010  VALID
STATEMENTS_AUDIT_IX01          P_2010                 32062110 02/06/2015  VALID
STATEMENTS_AUDIT_IX01          P_2011                 32062111 02/06/2015  VALID
STATEMENTS_AUDIT_IX01          P_2012                 32062112 02/06/2015  VALID
STATEMENTS_AUDIT_IX01          P_2013                 32062113 02/06/2015  VALID
STATEMENTS_AUDIT_IX01          P_2014                 32062114 02/06/2015  VALID
STATEMENTS_AUDIT_IX01          P_2015                 32062115 02/06/2015  VALID
STATEMENTS_AUDIT_IX01          P_2016                 32062116 02/06/2015  VALID
STATEMENTS_AUDIT_IX01          P_2017                 32062117 02/06/2015  VALID
STATEMENTS_AUDIT_IX01          P_2018                 32062118 02/06/2015  VALID
STATEMENTS_AUDIT_IX01          P_2019                 32062119 02/06/2015  VALID
STATEMENTS_AUDIT_IX01          P_2020                 32062120 02/06/2015  VALID
STATEMENTS_AUDIT_IX01                                 32062109 02/06/2015  VALID

STATEMENTS_AUDIT_PRT                                   1825644 26/12/2007  VALID

DROP TABLE MANAGER.STATEMENTS_AUDIT_PRT CASCADE CONSTRAINTS PURGE;

Table dropped

J. Gather statistics on the new table.
BEGIN
  DBMS_STATS.GATHER_TABLE_STATS (ownname =>  'MANAGER', tabname => 'STATEMENTS_AUDIT');
END;
/

PL/SQL procedure successfully completed

SELECT table_name, partition_name, num_rows, sample_size, last_analyzed   
FROM USER_TAB_PARTITIONS
WHERE table_name = 'STATEMENTS_AUDIT'

TABLE_NAME                     PARTITION_NAME         NUM_ROWS SAMPLE_SIZE LAST_ANALYZED
------------------------------ -------------------- ---------- ----------- -------------
STATEMENTS_AUDIT               P_2020                        0             02/06/2015 08
STATEMENTS_AUDIT               P_2019                        0             02/06/2015 08
STATEMENTS_AUDIT               P_2018                        0             02/06/2015 08
STATEMENTS_AUDIT               P_2017                        0             02/06/2015 08
STATEMENTS_AUDIT               P_2016                        0             02/06/2015 08
STATEMENTS_AUDIT               P_2015                 12236078      573669 02/06/2015 08
STATEMENTS_AUDIT               P_2014                 22430950      529563 02/06/2015 08
STATEMENTS_AUDIT               P_2013                    11103       11103 02/06/2015 08
STATEMENTS_AUDIT               P_2012                    41490       41490 02/06/2015 08
STATEMENTS_AUDIT               P_2011                    56042       56042 02/06/2015 08STATEMENTS_AUDIT               P_2010                   949638      949638 02/06/2015 08

=============================
Optionally - drop old partitions
=============================
ALTER TABLE STATEMENTS_AUDIT DROP PARTITION P_2010

Batch and sqlplus by example. Call bat file, that would execute sqlplus

The Flow
Need to run a SQL on remote database(s), and report the results.
In this case, the SQL logic is split into several steps.
As system, connect to remote DB and kill any stuck session.
As schema owner, connect to remote DB, and refresh snaspshot.

The files:
bat files
main_main.bat                   - Main bat file, that is calling all other bat files.
main_delete.bat                 - Call delete_refresh_job.sql
main_kill_sessions.bat     - Call gen_kill_sessions.sql
main_refresh_1by1.bat     - Call refresh_All_1by1.sql
main_create.bat                 - Call create_refresh_job.sql
main_run_refresh.bat      - Call run_refresh_job.sql

sql files
delete_refresh_job.sql     - Delete "stuck" job. (The "stuck" job is refresh of a group)  
gen_kill_sessions.sql       - Kill "stuck" job session
refresh_All_1by1.sql        - Refresh Group snapshots one by one
create_refresh_job.sql    - Create refresh job
run_refresh_job.sql         - Run refresh job 

parameter files
db_list.ini           - The remote database we need to connect to.
db_system.ini   - System account on remote database.

Code on Remote DB
The logic is in package on the Remote DB
REFRESH_PKG

===========================
Code example
===========================

===========================
parameter files
===========================

db_list.ini
===========================
userA/passA@connect_str

db_system.ini
===========================
system/pass@connect_str

===========================
bat files
===========================

===========================
main_main.bat        
===========================

ECHO OFF
setlocal
cls

ECHO.
ECHO =============================================
ECHO.
ECHO calling main_delete.bat
call main_delete.bat
ECHO.
ECHO =============================================
ECHO.

ECHO.
ECHO =============================================
ECHO.
ECHO calling main_kill_sessions.bat
call main_kill_sessions.bat
ECHO.
ECHO =============================================
ECHO.

ECHO.
ECHO =============================================
ECHO.
ECHO calling main_refresh_1by1.bat
call main_refresh_1by1.bat
ECHO.
ECHO =============================================
ECHO.

ECHO.
ECHO =============================================
ECHO.
ECHO calling main_create.bat
call main_create.bat
ECHO.
ECHO =============================================
ECHO.

ECHO.
ECHO =============================================
ECHO.
ECHO calling main_run_refresh.bat
call main_run_refresh.bat
ECHO.
ECHO =============================================
ECHO.

ECHO.
ECHO =============================================
ECHO main has finished
ECHO =============================================


===========================
main_delete.bat      
===========================

ECHO OFF
setlocal
cls

SET inifile=db_list.ini
REM REFRESH_LOG - the name of the generated file
SET REFRESH_LOG=delete_result.txt
SET TEMP_REFRESH_LOG=delete.log

ECHO.
ECHO =============================================
ECHO main_delete.bat is starting
ECHO This Program read entries from file %inifile% and report results to %REFRESH_LOG%
ECHO =============================================
ECHO.

ECHO. 2>%TEMP_REFRESH_LOG%
ECHO. 2>%REFRESH_LOG%

SET SQL_FILE=delete_refresh_job.sql
ECHO Running %SQL_FILE%

FOR /F "tokens=*" %%i IN (%inifile%) DO (
    ECHO Running %SQL_FILE% on %%i
    call run_sql.bat %TEMP_REFRESH_LOG% %%i %SQL_FILE%
    TYPE %TEMP_REFRESH_LOG% >> %REFRESH_LOG%
ECHO Done
ECHO.
)

ECHO.
ECHO =============================================
ECHO main has finished
ECHO =============================================



===========================

main_kill_sessions.bat

===========================
ECHO OFF
setlocal
cls

SET inifile=db_system.ini
REM REFRESH_LOG - the name of the generated file
SET REFRESH_LOG=kill_sessions.log
SET TEMP_REFRESH_LOG=temp_kill_sessions.log

ECHO.
ECHO =============================================
ECHO main_kill_sessions.bat is starting
ECHO This Program read entries from file %inifile% and report results to %REFRESH_LOG%
ECHO =============================================
ECHO.
ECHO. 2>%TEMP_REFRESH_LOG%
ECHO. 2>%REFRESH_LOG%

SET SQL_FILE=gen_kill_sessions.sql
ECHO Running %SQL_FILE%

FOR /F "tokens=*" %%i IN (%inifile%) DO (
    ECHO %SQL_FILE% %%i
    call run_sql.bat %TEMP_REFRESH_LOG% %%i %SQL_FILE%
    TYPE %TEMP_REFRESH_LOG% >> %REFRESH_LOG%
ECHO Done
ECHO.
)

ECHO.
ECHO =============================================
ECHO main has finished
ECHO =============================================

===========================

main_refresh_1by1.bat

===========================
ECHO OFF
setlocal
cls

SET inifile=db_list.ini
REM REFRESH_LOG - the name of the generated file
SET REFRESH_LOG=refresh.log
SET TEMP_REFRESH_LOG=temp_refresh.log


ECHO.
ECHO =============================================
ECHO main_refresh_1by1.bat is starting
ECHO This Program read entries from file %inifile% and report results to %REFRESH_LOG%
ECHO =============================================
ECHO.

ECHO. 2>%TEMP_REFRESH_LOG%
ECHO. 2>%REFRESH_LOG%
SET SQL_FILE=IG1_Refresh_All_1by1.sql
ECHO Running %SQL_FILE%

FOR /F "tokens=*" %%i IN (%inifile%) DO (
    ECHO %SQL_FILE% %%i
    call run_sql.bat %TEMP_REFRESH_LOG% %%i %SQL_FILE%
    TYPE %TEMP_REFRESH_LOG% >> %REFRESH_LOG%
ECHO Done
ECHO.
)

ECHO.
ECHO =============================================
ECHO main has finished
ECHO =============================================


      
===========================

main_create.bat 

===========================
ECHO OFF
setlocal
cls

SET inifile=db_list.ini
REM REFRESH_LOG - the name of the generated file
SET PROCESS_LOG=create.log
SET TEMP_PROCESS_LOG=temp_create.log

ECHO.
ECHO =============================================
ECHO main_create.bat is starting
ECHO This Program read entries from file %inifile% and report results to %PROCESS_LOG%
ECHO =============================================
ECHO.

ECHO. 2>%TEMP_PROCESS_LOG%
ECHO. 2>%PROCESS_LOG%

SET SQL_FILE=create_refresh_job.sql
ECHO Running %SQL_FILE%
FOR /F "tokens=*" %%i IN (%inifile%) DO (
    ECHO %SQL_FILE% %%i
    call run_sql.bat %TEMP_PROCESS_LOG% %%i %SQL_FILE%
    TYPE %TEMP_PROCESS_LOG% >> %PROCESS_LOG%
ECHO Done
ECHO.
)

ECHO.
ECHO =============================================
ECHO main has finished
ECHO =============================================

===========================

main_run_refresh.bat 

===========================

ECHO OFF
setlocal
cls

SET inifile=db_list.ini
REM REFRESH_LOG - the name of the generated file
SET REFRESH_LOG=run_refresh_result.log
SET TEMP_REFRESH_LOG=run_refresh.log

ECHO.
ECHO =============================================
ECHO main_run_refresh.bat is starting
ECHO This Program read entries from file %inifile% and report results to %REFRESH_LOG%
ECHO =============================================
ECHO.

ECHO. 2>%TEMP_REFRESH_LOG%
ECHO. 2>%REFRESH_LOG%

SET SQL_FILE=run_refresh_job.sql
FOR /F "tokens=*" %%i IN (%inifile%) DO (
    ECHO Running %SQL_FILE% on %%i
    call run_sql.bat %TEMP_REFRESH_LOG% %%i %SQL_FILE%
    TYPE %TEMP_REFRESH_LOG% >> %REFRESH_LOG%
ECHO Done
ECHO.
)

ECHO.
ECHO =============================================
ECHO main has finished
ECHO =============================================


===========================
sql files
===========================


===========================
delete_refresh_job.sql
===========================
SET TERMOUT ON
SET SHOW OFF
SET VERIFY OFF 
SET HEAD ON
SET LINE 500
SET FEEDBACK OFF
SET PAGES 500
SET TRIMS ON

SPOOL &&1

begin
  -- Call the procedure
  SH_REFRESH_PKG.delete_refresh_job;
end;
/

SPOOL OFF
EXIT

/

===========================
gen_kill_sessions.sql
===========================
SET TERMOUT ON
SET SHOW OFF
SET VERIFY OFF 
SET HEAD OFF
SET LINE 500
SET FEEDBACK OFF
SET PAGES 500
SET TRIMS ON

SPOOL kill_sessions.sql
SELECT 'ALTER SYSTEM KILL SESSION ' || SUBSTR(''''||v_session.sid||','||v_session.serial#||'''', 1,15)||' IMMEDIATE;'
FROM   v$session v_session,
       v$process v_process,
       V$INSTANCE,
       DBA_JOBS_RUNNING stuck_jobs,
       DBA_JOBS     
WHERE  v_process.addr = v_session.paddr 
  AND  v_session.type != 'BACKGROUND'
  AND  v_session.sid = stuck_jobs.sid
  AND  DBA_JOBS.job(+) = stuck_jobs.job;
SPOOL OFF  

EXEC DBMS_LOCK.sleep(20);
  
@kill_sessions.sql   

SPOOL kill_sessions.sql
SELECT 'ALTER SYSTEM KILL SESSION ' || SUBSTR(''''||v_session.sid||','||v_session.serial#||'''', 1,15)||' IMMEDIATE;'
FROM   v$session v_session,
       v$process v_process,
       V$INSTANCE,
       DBA_JOBS_RUNNING stuck_jobs,
       DBA_JOBS     
WHERE  v_process.addr = v_session.paddr 
  AND  v_session.type != 'BACKGROUND'
  AND  v_session.sid = stuck_jobs.sid
  AND  DBA_JOBS.job(+) = stuck_jobs.job;
SPOOL OFF  
EXEC DBMS_LOCK.sleep(20);  

@kill_sessions.sql   

EXIT

/

===========================
refresh_All_1by1.sql
===========================
SET SERVEROUTPUT ON;
SET FEEDBACK OFF;

EXEC DBMS_OUTPUT.PUT_LINE('----------------------');
EXEC DBMS_OUTPUT.PUT_LINE('Working on: ALARM_SS');                            
EXEC DBMS_SNAPSHOT.refresh('ALARM_SS','F');                                   
                                                                                
EXEC DBMS_OUTPUT.PUT_LINE('Working on: ALARM_FILE_CONFIG_SS');                  
EXEC DBMS_SNAPSHOT.refresh('ALARM_FILE_CONFIG_SS','F');                         
                                                                                
EXEC DBMS_OUTPUT.PUT_LINE('Working on: ALARM_MAP_SS');                     
EXEC DBMS_SNAPSHOT.refresh('ALARM_MAP_SS','F');                            
                                                                                
EXEC DBMS_OUTPUT.PUT_LINE('Working on: ALARM_NAME_SS');                         
EXEC DBMS_SNAPSHOT.refresh('ALARM_NAME_SS','F');                                
                                                                     
EXEC DBMS_OUTPUT.PUT_LINE('Working on: ALLOCATOR_SS');                          
EXEC DBMS_SNAPSHOT.refresh('ALLOCATOR_SS','F');                                 
                                                                                
EXEC DBMS_OUTPUT.PUT_LINE('Working on: BILLING_SERVICE_SS');                    
EXEC DBMS_SNAPSHOT.refresh('BILLING_SERVICE_SS','F');                           
                                                                                
EXEC DBMS_OUTPUT.PUT_LINE('Working on: WORLDWIDE_LIST_SS');                     
EXEC DBMS_SNAPSHOT.refresh('WORLDWIDE_LIST_SS','F');                            

EXEC DBMS_OUTPUT.PUT_LINE('----------------------');
EXEC DBMS_OUTPUT.PUT_LINE('Completed');
EXEC DBMS_OUTPUT.PUT_LINE('----------------------');

EXIT

/

===========================
create_refresh_job.sql
===========================
SET TERMOUT ON
SET SHOW OFF
SET VERIFY OFF 
SET HEAD ON
SET LINE 500
SET FEEDBACK OFF
SET PAGES 500
SET TRIMS ON

SPOOL &&1

begin
  -- Call the procedure
  REFRESH_PKG.create_refresh_job;
end;
/

SPOOL OFF
EXIT

/

===========================
run_refresh_job.sql 
===========================
SET TERMOUT ON
SET SHOW OFF
SET VERIFY OFF 
SET HEAD ON
SET LINE 500
SET FEEDBACK ON
SET PAGES 500
SET TRIMS ON

SPOOL &&1

EXEC DBMS_OUTPUT.PUT_LINE('Starting REFRESH_PKG.refresh_prc;');    

begin
  -- Call the procedure
  REFRESH_PKG.refresh_prc;
end;
/

EXEC DBMS_OUTPUT.PUT_LINE('Finished REFRESH_PKG.refresh_prc;');    

SPOOL OFF
EXIT

/

===========================
REFRESH_PKG
===========================

CREATE OR REPLACE PACKAGE BODY REFRESH_PKG IS

===========================
REFRESH_PRC
===========================

PROCEDURE REFRESH_PRC IS

g_name  varchar2(30);

BEGIN

    

    -- Create the current entry in the Master log table)

    SELECT DISTINCT SUBSTR(process_name,1,12)

    into g_name 

    FROM process_information

    WHERE process_name like '%REFRESH%';

    INSERT INTO REFRESH_LOG@MASTER_DB(DB_NAME,LAST_REFRESH_START) 

    VALUES (g_name,SYSDATE);

    COMMIT;

    

    DBMS_REFRESH.REFRESH('MASTER_GROUP');

    

    UPDATE REFRESH_LOG@MASTER_DB SET LAST_REFRESH_END=SYSDATE 

    WHERE DB_NAME=g_name 

      AND LAST_REFRESH_START=(SELECT MAX(LAST_REFRESH_START) 

                                FROM REFRESH_LOG@MASTER_DB7 

                               WHERE DB_NAME=g_name);

    COMMIT;

     

END REFRESH_PRC;

===========================
CREATE_REFRESH_JOB
===========================

PROCEDURE CREATE_REFRESH_JOB IS

  X NUMBER;  

BEGIN

    DELETE_REFRESH_JOB;

    

    SYS.DBMS_JOB.SUBMIT

    ( job       => X 

     ,what      => 'REFRESH_PKG.REFRESH_PRC;'

     ,next_date => trunc(sysdate+1)+1/24+10/1440

     ,interval  => 'trunc(sysdate+1) +1/24+10/1440'

     ,no_parse  => TRUE );

    UPDATE DB_REFRESH_MAP@master_db set refresh_flag=1

    WHERE SUBSTR(DB_NAME,1,12) = (SELECT DISTINCT SUBSTR(process_name,1,12) 

                                    FROM process_information 

                                   WHERE process_name like '%REFRESH%');

    COMMIT;

END CREATE_REFRESH_JOB;

===========================
DELETE_REFRESH_JOB
===========================

PROCEDURE DELETE_REFRESH_JOB IS

cursor job_remove is

    select job,what from user_jobs where 

    what like '%REFRESH_PRC%';

BEGIN

    for  rec in job_remove loop

        dbms_job.remove(rec.job);

    end loop;

    UPDATE DB_REFRESH_MAP@master_db set refresh_flag=0

    WHERE SUBSTR(DB_NAME,1,12) = (SELECT DISTINCT SUBSTR(process_name,1,12) 

                                    FROM process_information 

                                   WHERE process_name like '%REFRESH%');

    COMMIT;

    

END DELETE_REFRESH_JOB;

END REFRESH_PKG;