Working with Github

Github is most popular  free project hosting site. You can git repository and upload your project with following steps.

1.   Create Project repository in  in Github and copy the repository http URL 
2.    Install git client in you local machine.
3.    Goto directory where you want to checkout  the code. Right click on directory and select 'Git Bash here' from menu. It will open git bash console
   
4. In git console run git clone command.  
   $ git clone https://github.com/sujanctg2005/oracle.git
5.  Change directory to  oracle
   $ cd oracle/
6. Check  git status
   $ git status
7. Modify the files you want to push in git hub
8. Add modified files in local git repository
   $ git add *
9. Commit modified files in local git repository
   $ git status
   $ git commit -m 'comments' *
10. Now you can push modified files in git repository
    $ git status
    $ git push https://github.com/sujanctg2005/oracle.git
    This push command will prompt for Github user-name and password. You can verify the push just browsing that git repository in online.

Oracle Dynamic SQL

You can create SQL statement dynamically and make the script more generic to make it work with any table.

  DECLARE
    TYPE COL_TYPE IS TABLE OF VARCHAR2(40);
    COL_NAME COL_TYPE;
    COL_DATA_TYPE COL_TYPE;
    TAB1_NAME VARCHAR2(30) :='A_USERS'; 
  BEGIN
      SQL_STMT := 'SELECT COLUMN_NAME,DATA_TYPE FROM USER_TAB_COLUMNS WHERE TABLE_NAME = '''|| TAB1_NAME ||'''';
       EXECUTE IMMEDIATE SQL_STMT   BULK COLLECT INTO COL_NAME ,COL_DATA_TYPE;
  END;
 /

Oracle 'EXECUTE IMMEDIATE' will help you to run dynamic sql. If   sql command return data  you can store it in a variable. Above example, SQL statement will return  columns name and data type of a table and it will be store in COL_NAME ,COL_DATA_TYPE variable. You can access value from COL_NAME array in following way.

FOR INDX IN 1 .. COL_NAME.COUNT 
   LOOP   
       DBMS_OUTPUT.PUT_LINE ('COL_NAME: ' || COL_NAME(index));
  END LOOP;

Also if you can create separate code block and pass bind variable   P_REC and IN T_REC  value while executing the statement and output will be store in MISS_MATCH variable.

 
 DECLARE
 P_REC  A_USERS%ROWTYPE;
  T_REC  A_USERS%ROWTYPE;
 BEGIN
               SQL_STMT :=  'DECLARE 
                          P_REC  '|| TAB1_NAME ||'%ROWTYPE; 
                          T_REC  '|| TAB2_NAME ||'%ROWTYPE;  
                       BEGIN    P_REC := :P_REC;  T_REC := :T_REC;   
                       SELECT DECODE(P_REC.' || COL_NAME(INDX) || ',T_REC.' || COL_NAME(INDX) || ',0,1)
INTO :MISS_MATCH FROM DUAL;                       
        
           EXECUTE IMMEDIATE SQL_STMT USING IN  P_REC , IN T_REC , OUT MISS_MATCH;
  END;
/

Replication data compare for oracle golden gate

For high availability  we need database replication, so during fail-over we can switch to standby database. Oracle golden gate provide data replication between data-centers. Replication is mainly depend on last updated time stamp. Oracle golden gate will check transaction time-stamp and replay it in another data-center. But sometime replication doesn't happen properly because of  application' defect on updating time stamp.


One or more applications can use same database and they may be locate in different time-zone. If one application is updating data using one time-zone in a active database and another application is updating data with different  time-zone in another database,  such case replication between these two database  will make database in-consistent state.


To find out data in-consistency we can compare one site data table with another site same table. For example  we have table name 'customer' in database and data is replicating for this table on two active database A and B . If we want to compare data for this table, we can export customer table from A and B into a new database schema, so data comparison job will not impact the running database.

If the table contain only some configuration data, number of records might be less. But customer table can contain millions of record, therefore comparing data become very time consuming.
We can overcome this problem using oracle 'DBMS_PARALLEL_EXECUTE' package. This will allow you to logically split table using table rowid and create job for each partition and run them parallel. You can partition table using different columns as well.

Below snapshot is to create job using oracle 'DBMS_PARALLEL_EXECUTE' package.

 
 DECLARE 
  TASK_NAME VARCHAR2(40) :='A_B_DATA_COMPARE';
  UPDATE_STATEMENT CONSTANT VARCHAR2 (200)
      :=  'BEGIN DATA_COMP(:start_id, :end_id); END;';

 BEGIN

         DBMS_PARALLEL_EXECUTE.CREATE_TASK (TASK_NAME);       
         DBMS_PARALLEL_EXECUTE.CREATE_CHUNKS_BY_ROWID (TASK_NAME => TASK_NAME, TABLE_OWNER => USER , TABLE_NAME => TAB1_NAME , BY_ROW => TRUE , CHUNK_SIZE => 800000  );
         DBMS_PARALLEL_EXECUTE.RUN_TASK (TASK_NAME => TASK_NAME , SQL_STMT => C_UPDATE_STATEMENT  , LANGUAGE_FLAG => DBMS_SQL.NATIVE  , PARALLEL_LEVEL =>64  );
       EXCEPTION
              WHEN OTHERS  THEN 
                  DBMS_OUTPUT.PUT_LINE('FAIL TO RUN  JOBS, ERROR CODE:' ||SQLCODE);
                  RETURN;
 
    

         
 END;

You can pass number concurrent jobs, partition size and more importantly which procedure or function it will call with start rowid and end rowid.  In the above example DBMS_PARALLEL_EXECUTE will partition customer table by rowid and each partition will contain 800000 record. Then it will call DATA_COMP procedure with a partition start rowid and end rowid. You can select record using rowid and loop through them using oracle cursor.

 
      DECLARE 
   TASK_NAME VARCHAR2(40) :='A_B_DATA_COMPARE';
  UPDATE_STATEMENT CONSTANT VARCHAR2 (200)
      :=  'BEGIN DATA_COMP(:start_id, :end_id); END;';
     BEGIN
         DBMS_PARALLEL_EXECUTE.CREATE_TASK (TASK_NAME);       
         DBMS_PARALLEL_EXECUTE.CREATE_CHUNKS_BY_ROWID (TASK_NAME => TASK_NAME, TABLE_OWNER => USER , TABLE_NAME => TAB1_NAME , BY_ROW => TRUE , CHUNK_SIZE => 800000  );
         DBMS_PARALLEL_EXECUTE.RUN_TASK (TASK_NAME => TASK_NAME , SQL_STMT => C_UPDATE_STATEMENT  , LANGUAGE_FLAG => DBMS_SQL.NATIVE  , PARALLEL_LEVEL =>64  );
       EXCEPTION
              WHEN OTHERS  THEN 
                  DBMS_OUTPUT.PUT_LINE('FAIL TO RUN  JOBS, ERROR CODE:' ||SQLCODE);
                  RETURN;
       END;
    
    

This way you compare data more faster way, since it will parallel.   Inside the cursor loop you can pull record from another table using primary key and compare the record by each column of A and B database customer table.

You can can get complete oracle  PL/SQL example in Github. I make the script dynamic, so only you need to pass compare  two tables  and report  table name.