Monday, June 24, 2013

Simple schema compare scripts

Assuming connected to "test" database and want to compare to "product" via database link "prod".


Object difference:

select p.object_name prod_obj_name, p.object_type prod_obj_type, t.object_name test_obj_name, t.object_type test_obj_type
from user_objects@prod p
full outer join user_objects t
on p.object_name=t.object_name
and t.object_type=p.object_type
order by decode(p.object_name,NULL, 0, 1), decode(t.object_name,NULL, 0, 1),
p.object_name;

The results are shown in 3 sections: NULL first 2 columns - objects exist in "test" not in "prod"; NULL last 2 columns - objects exist in "prod" not in "test"; no NULL columns - objects exist in both. "Where" clause can be added to show only the difference instead of thousands of identical objects.


Table column difference:

WITH X as (select p.table_name, p.column_name
from user_tab_columns@prod p
join user_tables tt
on tt.table_name=p.table_name
),
Y as
(select t.table_name, t.column_name
from user_tab_columns t
join user_tables@prod pt
on pt.table_name=t.table_name
)
select x.table_name prod_table_name, x.column_name prod_column_name, y.table_name test_table_name, y.column_name test_column_name
from x full outer join y
on x.table_name=y.table_name
and x.column_name=y.column_name
where x.table_name is null or y.table_name is null
;


Similar to object difference, but it has to be done in a more complex query.

Index columns or even procedures etc can also be checked similarly if needed.

1 comment:

  1. F(_)llz avaialable in bulk quantity
    USA UK CANADA all states available

    S_S_N F(_)LLZ
    S_I_N F(_)LLZ
    N_I_N F(_)LLZ
    DL F(_)LLZ

    HERE YOU'LL FIND US

    TG > AT LEADSUPPLIER / AT KILLHACKS
    TG CHANL > t.me/leadsproviderworldwide
    WHT's APP > +1 7.2.7 78.8.6 12.9
    VK MSNGR > AT LEADSUPPLIER

    F(_)LLZ SS.N DOB D.L
    UK N.I.N D.OB Sort Code
    Canada SI.N MM.N Email
    D.L FRONT BACK USA UK CANADA

    MANY LEADS DATABASES AVAILABLE
    CRYPTO & PAYDAY LEADS
    MORTGAGE & LOAN LEADS
    SWEEP STAKES LEADS
    USA EMPLOYEMENT LEADS, PERSONAL INFO LEADS
    UK PHONE NUMBERS & EMAIL LEADS
    CANADA PHONE NUMBER & EMAIL LEADS
    SPECIFIC PROFESSIONALS LEADS
    FACEBOOK, LINKEDIN, AMAZON LEADS
    WORLDWIDE DOMAINS LEADS
    EDUCATION LEADS
    CAR DATABASE LEADS
    W-2 FORMS
    BUSINESS EIN COMPANY LEADS
    HIGH CREDIT SCORES LEADS
    BUSINESS LEADS
    MEDICAL LEADS
    DOCTORS DATABASE LEADS

    #SSN #SSNDOBDL #SellSSN #CCShop #CCSELLCVV #ShopSSNDOBDLADDRESS #FULLZ #SSNFULLZ
    #REALDLSCAN #YoungAgeFullz
    #SIN #SINDOBDL #SellSIN #SINMMNFULLZ #MMNPROSSIN #MMNSIN #CCShop #CCSELLCVV #ShopSINDOBDLADDRESS #FULLZ #SINFULLZ
    #REALDLSCAN #YoungAgeFullz #Fullzseller #CANADAFULLZ #FULLZCANADA
    #NIN #NINDOBDL #SellNIN #CCShop #CCSELLCVV #ShopNINDOBDLADDRESS #FULLZ #NINFULLZ

    ReplyDelete