DOITLIKEITSYOURJOB - DB Compare

DECLARE v_owner VARCHAR2(30) := 'YOUR_SCHEMA_OWNER';BEGIN -- Create a table to store object details EXECUTE IMMEDIATE 'CREATE TABLE schema_objects (object_type VARCHAR2(50), object_name VARCHAR2(100), details CLOB)'; -- Insert table details FOR t IN (SELECT table_name FROM all_tables WHERE owner = v_owner) LOOP INSERT INTO schema_objects (object_type, object_name, details) VALUES ('Table', t.table_name, 'Additional table details here'); END LOOP; -- Insert view details FOR v IN (SELECT view_name FROM all_views WHERE owner = v_owner) LOOP INSERT INTO schema_objects (object_type, object_name, details) VALUES ('View', v.view_name, 'Additional view details here'); END LOOP; -- Insert index details FOR i IN (SELECT index_name FROM all_indexes WHERE owner = v_owner) LOOP INSERT INTO schema_objects (object_type, object_name, details) VALUES ('Index', i.index_name, 'Additional index details here'); END LOOP; -- Insert procedure details FOR p IN (SELECT object_name FROM all_objects WHERE owner = v_owner AND object_type = 'PROCEDURE') LOOP INSERT INTO schema_objects (object_type, object_name, details) VALUES ('Procedure', p.object_name, 'Additional procedure details here'); END LOOP; -- Add more object types and details as needed EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('An error occurred: ' || SQLERRM);END;/

import oracledbimport csvfrom datetime import datetime
# Path to your SQL file (assumed to be in the same folder as this script)# sql_file_path = r'C:\Users\NR67092\OneDrive - Citi\Admin\DataGlobe\Components\Company Pred Search\10184\V06_02_23_10184_34_METADATA_PKG.sql'
def compare(object_type, owner, object_name, extractfolder, jira):    sql_cmd = """    SELECT DBMS_METADATA.get_ddl(object_type, object_name, owner) AS object_ddl,           DBMS_METADATA.get_granted_ddl('OBJECT_GRANT', object_name, owner) AS object_permissions,           DBMS_METADATA.get_dependent_ddl('INDEX', object_name, owner) AS object_indexes    FROM dba_objects    WHERE 1=1    AND OBJECT_TYPE = '{object_type}'    AND OWNER = '{owner}'    AND OBJECT_NAME = '{object_name}'    AND SUBOBJECT_NAME IS NULL -- remove table partitions etc    """.format(object_type=object_type, owner=owner, object_name=object_name)
    print(sql_cmd)
    # Connect to the database        # dev    dev_env = "DEV - DGB1DEV.oraas.dyn.nsroot.net 8889 DGB1DEV"    dev_dsn = oracledb.makedsn("DGB1DEV.oraas.dyn.nsroot.net", 8889, service_name="DGB1DEV")    # dev_conn = oracledb.connect(user="DGLOBE", password="ZXN6CuVm", dsn=dev_dsn)    # dev_conn = oracledb.connect(user="DGSTREAM", password="yL1fn3up", dsn=dev_dsn)    dev_conn = oracledb.connect(user="NR67092", password="Hormon33c123", dsn=dev_dsn)
    # uat    uat_env = "UAT - oraasswp183-scan.nam.nsroot.net 8889 DGB2UAT"    uat_dsn = oracledb.makedsn("oraasswp183-scan.nam.nsroot.net", 8889, service_name="DGB2UAT")    uat_conn = oracledb.connect(user="NR67092", password="Hormon33c123", dsn=uat_dsn)
    # prod    prod_env = "PROD - oraasmup176-scan.nam.nsroot.net 8889 DGB2PRD"    prod_dsn = oracledb.makedsn("oraasmup176-scan.nam.nsroot.net", 8889, service_name="DGB2PRD")    prod_conn = oracledb.connect(user="NR67092", password="Hormon33c123", dsn=prod_dsn)
    # Create cursors for each environment    dev_cursor = dev_conn.cursor()    uat_cursor = uat_conn.cursor()    prod_cursor = prod_conn.cursor()
    try:        dev_cursor.execute(sql_cmd)        dev_results = dev_cursor.fetchall()
        uat_cursor.execute(sql_cmd)        uat_results = uat_cursor.fetchall()
        prod_cursor.execute(sql_cmd)        prod_results = prod_cursor.fetchall()
        # Process results        dev_ddl_text = ''        uat_ddl_text = ''        prod_ddl_text = ''
        for ddl_row in dev_results:            dev_clob_data = ddl_row[0]            dev_ddl_text = dev_clob_data.read()
        for ddl_row in uat_results:            uat_clob_data = ddl_row[0]            uat_ddl_text = uat_clob_data.read()
        for ddl_row in prod_results:            prod_clob_data = ddl_row[0]            prod_ddl_text = prod_clob_data.read()
        dev_ddl_lines = dev_ddl_text.splitlines()        uat_ddl_lines = uat_ddl_text.splitlines()        prod_ddl_lines = prod_ddl_text.splitlines()
        path = 'C:/Users/NR67092/OneDrive - Citi/Projects/PythonTools/EnvCompare/{extractfolder}/'.format(extractfolder=extractfolder)
        dev_csv_file_path = '{path}{jira}_{object_type}_{owner}_{object_name}_1DEV.sql'.format(jira=jira, object_type=object_type, path=path, owner=owner, object_name=object_name)        uat_csv_file_path = '{path}{jira}_{object_type}_{owner}_{object_name}_2UAT.sql'.format(jira=jira, object_type=object_type, path=path, owner=owner, object_name=object_name)        prod_csv_file_path = '{path}{jira}_{object_type}_{owner}_{object_name}_3PROD.sql'.format(jira=jira, object_type=object_type, path=path, owner=owner, object_name=object_name)
        # Write results to files        with open(dev_csv_file_path, mode='w', newline='\n', encoding='utf-8') as file:            file.write('--DEV')            for row in dev_ddl_lines:                file.write(row)                file.write("\n")
        with open(uat_csv_file_path, mode='w', newline='\n', encoding='utf-8') as file:            file.write('--UAT')            for row in uat_ddl_lines:                file.write(row)                file.write("\n")
        with open(prod_csv_file_path, mode='w', newline='\n', encoding='utf-8') as file:            file.write('--PROD')            for row in prod_ddl_lines:                file.write(row)                file.write("\n")
    finally:        # Close cursors and connections        dev_cursor.close()        uat_cursor.close()        prod_cursor.close()        dev_conn.close()        uat_conn.close()        prod_conn.close()

import oracledbimport csvfrom datetime import datetime
# Path to your SQL file (assumed to be in the same folder as this script)# sql_file_path = r'C:\Users\NR67092\OneDrive - Citi\Admin\DataGlobe\Components\Company Pred Search\10184\V06_02_23_10184_34_METADATA_PKG.sql'
def compare(object_type, owner, object_name, extractfolder, jira):    # Extract the main DDL    sql_cmd_ddl = """    SELECT DBMS_METADATA.get_ddl(object_type, object_name, owner) AS object_ddl    FROM dba_objects    WHERE 1=1    AND OBJECT_TYPE = '{object_type}'    AND OWNER = '{owner}'    AND OBJECT_NAME = '{object_name}'    AND SUBOBJECT_NAME IS NULL    """.format(object_type=object_type, owner=owner, object_name=object_name)
    # Extract dependent indexes    sql_cmd_indexes = """    SELECT DBMS_METADATA.get_dependent_ddl('INDEX', object_name, owner) AS object_indexes    FROM dba_objects    WHERE 1=1    AND OBJECT_TYPE = '{object_type}'    AND OWNER = '{owner}'    AND OBJECT_NAME = '{object_name}'    AND SUBOBJECT_NAME IS NULL    """.format(object_type=object_type, owner=owner, object_name=object_name)
    print(sql_cmd_ddl)
    # Use context manager for database connections and cursors    dev_dsn = oracledb.makedsn("DGB1DEV.oraas.dyn.nsroot.net", 8889, service_name="DGB1DEV")    uat_dsn = oracledb.makedsn("oraasswp183-scan.nam.nsroot.net", 8889, service_name="DGB2UAT")    prod_dsn = oracledb.makedsn("oraasmup176-scan.nam.nsroot.net", 8889, service_name="DGB2PRD")
    try:        with oracledb.connect(user="NR67092", password="Hormon33c123", dsn=dev_dsn) as dev_conn, \             oracledb.connect(user="NR67092", password="Hormon33c123", dsn=uat_dsn) as uat_conn, \             oracledb.connect(user="NR67092", password="Hormon33c123", dsn=prod_dsn) as prod_conn:
            with dev_conn.cursor() as dev_cursor, uat_conn.cursor() as uat_cursor, prod_conn.cursor() as prod_cursor:                # Execute DDL query                dev_cursor.execute(sql_cmd_ddl)                dev_results = dev_cursor.fetchall()
                uat_cursor.execute(sql_cmd_ddl)                uat_results = uat_cursor.fetchall()
                prod_cursor.execute(sql_cmd_ddl)                prod_results = prod_cursor.fetchall()
                # Execute indexes query                dev_cursor.execute(sql_cmd_indexes)                dev_indexes_results = dev_cursor.fetchall()
                uat_cursor.execute(sql_cmd_indexes)                uat_indexes_results = uat_cursor.fetchall()
                prod_cursor.execute(sql_cmd_indexes)                prod_indexes_results = prod_cursor.fetchall()
                # Process results for DDL                dev_ddl_text = ''                uat_ddl_text = ''                prod_ddl_text = ''
                for ddl_row in dev_results:                    dev_clob_data = ddl_row[0]                    dev_ddl_text = dev_clob_data.read()
                for ddl_row in uat_results:                    uat_clob_data = ddl_row[0]                    uat_ddl_text = uat_clob_data.read()
                for ddl_row in prod_results:                    prod_clob_data = ddl_row[0]                    prod_ddl_text = prod_clob_data.read()
                dev_ddl_lines = dev_ddl_text.splitlines()                uat_ddl_lines = uat_ddl_text.splitlines()                prod_ddl_lines = prod_ddl_text.splitlines()
                path = 'C:/Users/NR67092/OneDrive - Citi/Projects/PythonTools/EnvCompare/{extractfolder}/'.format(extractfolder=extractfolder)
                dev_csv_file_path = '{path}{jira}_{object_type}_{owner}_{object_name}_1DEV.sql'.format(jira=jira, object_type=object_type, path=path, owner=owner, object_name=object_name)                uat_csv_file_path = '{path}{jira}_{object_type}_{owner}_{object_name}_2UAT.sql'.format(jira=jira, object_type=object_type, path=path, owner=owner, object_name=object_name)                prod_csv_file_path = '{path}{jira}_{object_type}_{owner}_{object_name}_3PROD.sql'.format(jira=jira, object_type=object_type, path=path, owner=owner, object_name=object_name)
                # Write results to files                with open(dev_csv_file_path, mode='w', newline='\n', encoding='utf-8') as file:                    file.write('--DEV\n')                    for row in dev_ddl_lines:                        file.write(row)                        file.write("\n")
                with open(uat_csv_file_path, mode='w', newline='\n', encoding='utf-8') as file:                    file.write('--UAT\n')                    for row in uat_ddl_lines:                        file.write(row)                        file.write("\n")
                with open(prod_csv_file_path, mode='w', newline='\n', encoding='utf-8') as file:                    file.write('--PROD\n')                    for row in prod_ddl_lines:                        file.write(row)                        file.write("\n")    except oracledb.DatabaseError as e:        print(f"Database error occurred: {e}")

Compare Indexes

-- Query indexes from DEVWITH dev_indexes AS (    SELECT        i.table_name,        i.index_name,        i.uniqueness,        i.index_type,        i.tablespace_name,        i.status,        LISTAGG(c.column_name, ', ') WITHIN GROUP (ORDER BY c.column_position) AS columns    FROM        all_indexes@DEV_LINK i    JOIN        all_ind_columns@DEV_LINK c ON i.index_name = c.index_name AND i.table_name = c.table_name    GROUP BY        i.table_name, i.index_name, i.uniqueness, i.index_type, i.tablespace_name, i.status),
-- Query indexes from UATuat_indexes AS (    SELECT        i.table_name,        i.index_name,        i.uniqueness,        i.index_type,        i.tablespace_name,        i.status,        LISTAGG(c.column_name, ', ') WITHIN GROUP (ORDER BY c.column_position) AS columns    FROM        all_indexes@UAT_LINK i    JOIN        all_ind_columns@UAT_LINK c ON i.index_name = c.index_name AND i.table_name = c.table_name    GROUP BY        i.table_name, i.index_name, i.uniqueness, i.index_type, i.tablespace_name, i.status)
-- Compare DEV and UAT indexesSELECT    'DEV' AS environment,    d.table_name,    d.index_name,    d.uniqueness,    d.index_type,    d.tablespace_name,    d.status,    d.columnsFROM    dev_indexes dMINUSSELECT    'UAT' AS environment,    u.table_name,    u.index_name,    u.uniqueness,    u.index_type,    u.tablespace_name,    u.status,    u.columnsFROM    uat_indexes u
UNION ALL
SELECT    'UAT' AS environment,    u.table_name,    u.index_name,    u.uniqueness,    u.index_type,    u.tablespace_name,    u.status,    u.columnsFROM    uat_indexes uMINUSSELECT    'DEV' AS environment,    d.table_name,    d.index_name,    d.uniqueness,    d.index_type,    d.tablespace_name,    d.status,    d.columnsFROM    dev_indexes dORDER BY    table_name, index_name, environment;

Compare Indexes

VSCODe PYTHON INDEX COMPARE