Compare Oracle tables with linux md5

Comparison between two table with same name in different schema 
Lets say MEMBERS be the table that need to be compared on schemas SCHEMA_A and SCHEMA_B.

Enabling timing to track down the time taken.

set timing on;

Dropping external table if exists from SCHEMA_A
drop table SCHEMA_A.MEMBERS_EXT_A;

Create external table from first schema, SCHEMA_A in our case  
CREATE TABLE SCHEMA_A.MEMBERS_EXT_A
ORGANIZATION EXTERNAL
(
TYPE ORACLE_DATAPUMP
DEFAULT DIRECTORY EXT_DIR
ACCESS PARAMETERS
(
nologfile
)
LOCATION ('MEMBERS_EXT_A.csv')
)
AS SELECT * FROM SCHEMA_A.MEMBERS; 



Dropping external table if exists from SCHEMA_B
drop table SCHEMA_B.MEMBERS_EXT_B; 

Create external table from second schema, SCHEMA_B in our case
CREATE TABLE SCHEMA_B.MEMBERS_EXT_B
ORGANIZATION EXTERNAL
(
TYPE ORACLE_DATAPUMP
DEFAULT DIRECTORY EXT_DIR
ACCESS PARAMETERS
(
nologfile
)
LOCATION ('MEMBERS_EXT_B.csv')
)
AS SELECT * FROM SCHEMA_B.MEMBERS;

Check the MD5 before removing header from each external csv file
time md5sum MEMBERS_EXT_A.csv
time md5sum MEMBERS_EXT_B.csv

Remove header from the external files
time sed -i '1,3d' MEMBERS_EXT_A.csv
time sed -i '1,3d' MEMBERS_EXT_A.csv

Compare md5 with following commands, If both tables contains exactly same data then md5 values will be same.
time md5sum MEMBERS_EXT_A.csv
time md5sum MEMBERS_EXT_B.csv


Here is the execution output

drop table SCHEMA_A.MEMBERS_EXT_A;

table MEMBERS_EXT_A dropped.
Elapsed: 00:00:00.385
CREATE TABLE SCHEMA_A.MEMBERS_EXT_A
ORGANIZATION EXTERNAL
(
TYPE ORACLE_DATAPUMP
DEFAULT DIRECTORY EXT_DIR

ACCESS PARAMETERS
(
nologfile
)
LOCATION ('MEMBERS_EXT_A.csv')
)
AS SELECT * FROM SCHEMA_A.MEMBERS;

table MEMBERS_EXT_A created.

Elapsed: 00:00:48.268


drop table SCHEMA_B.MEMBERS_EXT_B;

table MEMBERS_EXT dropped.

Elapsed: 00:00:00.386


CREATE TABLE SCHEMA_B.MEMBERS_EXT_B
ORGANIZATION EXTERNAL
(
TYPE ORACLE_DATAPUMP
DEFAULT DIRECTORY EXT_DIR
ACCESS PARAMETERS
(
nologfile
)
LOCATION ('MEMBERS_EXT_B.csv')
)
AS SELECT * FROM SCHEMA_B.MEMBERS;

table MEMBERS_EXT_B created.

Elapsed: 00:00:45.530


[dilli@dilliraj mydir]$ time md5sum MEMBERS_EXT_A.csv
a0d831508aca62dec56db1e1148afd06 MEMBERS_EXT_A.csv

real 0m8.005s
user 0m7.136s
sys 0m0.867s


[dilli@dilliraj mydir]$ time md5sum MEMBERS_EXT_B.csv
78d0d09b757e97b6d9609709c3193270 MEMBERS_EXT_B.csv

real 0m7.990s
user 0m7.019s
sys 0m0.971s


[dilli@dilliraj mydir]$ time sed -i '1,3d' MEMBERS_EXT_A.csv

real 0m25.588s
user 0m5.089s
sys 0m20.489s


[dilli@dilliraj mydir]$ time sed -i '1,3d' MEMBERS_EXT_B.csv

real 0m26.335s
user 0m5.110s
sys 0m20.528s


[dilli@dilliraj mydir]$ time md5sum MEMBERS_EXT_A.csv
10c7b4d9b8cb4506f97a542e16da5f0b MEMBERS_EXT_A.csv

real 0m8.276s
user 0m7.245s
sys 0m0.953s

[dilli@dilliraj mydir]$ time md5sum MEMBERS_EXT_B.csv
10c7b4d9b8cb4506f97a542e16da5f0b MEMBERS_EXT_B.csv

real 0m8.389s
user 0m7.331s
sys 0m0.983s

Read More

DDL trigger that will fire on create table

create or replace trigger
DDLTrigger
AFTER create or drop ON schema
declare
TRIG_NAME varchar2(32):='TRG_' || ora_dict_obj_name;
BEGIN
    if ora_sysevent='CREATE' and ora_dict_obj_type='TABLE'  and ora_dict_obj_name != 'MY_DML_LOG'  THEN
        Execute immediate('CREATE OR REPLACE TRIGGER ' || TRIG_NAME ||
            ' BEFORE INSERT OR UPDATE OR DELETE ON ' || ora_dict_obj_owner || '.' || ora_dict_obj_name ||
            ' declare
            begin
            if UPDATING then
                insert into my_dml_log values(''UPDATE'',sysdate ,''' || ora_dict_obj_owner ||''','''|| ora_dict_obj_name || ''');
            elsif DELETING then
                insert into my_dml_log values(''DELETE'',sysdate ,''' || ora_dict_obj_owner ||''','''|| ora_dict_obj_name || ''');
            elsIF INSERTING then
                insert into my_dml_log values(''INSERT'',sysdate ,''' || ora_dict_obj_owner ||''','''|| ora_dict_obj_name || ''');
            end if;
            end;');
        END IF;
END;
/

Read More