Forum Stats

  • 3,783,352 Users
  • 2,254,762 Discussions
  • 7,880,372 Comments

Discussions

Schema Compare Script - Index Columns

948207
948207 Member Posts: 13
edited Mar 20, 2014 6:21PM in SQL & PL/SQL

Hi,

I am trying to build a schema compare script which will compare and alert on changes on a single schema. I have a batch job which inserts all objects from the DBA_ views into my custom compare tables. I am having trouble with the INDEXED COLUMNS compare SQL.

My compare table (T_COMP_IND_COLS) looks like the following. Batch ID 1 was run yesterday when CUST_NAME_I index only had one column. Batch ID 2 was run today and now CUST_NAME_I has 2 columns.

DB NameOwnerIndex NameTable NameColumn NameColumn PositionBatch ID
TESTDBROBCUST_NAME_ICUSTOMERCUST_NAME11
TESTDBROBORDER_TYPE_IORDERSORDER_TYPE11
TESTDBROBCUST_NAME_ICUSTOMERCUST_ID12
TESTDBROBCUST_NAME_ICUSTOMERCUST_NAME22
TESTDBROBORDER_TYPE_IORDERSORDER_TYPE12

I am trying to write a query on T_COMP_IND_COLS that will allow me to show differences including the index name and the new columns added (or deleted).

I have tried the below SQL but this is not returning the data the way I want.

select t1.owner as own1, t1.table_name as Tbl1, t1.index_name as Ind1, t1.column_name as col1, t1.column_position as pos1, t1.batch_id,

       t2.owner as own2, t2.table_name as Tbl2, t2.index_name as Ind2, t2.column_name as col2, t2.column_position as pos2, t2.batch_id

from t_cmp_ind_col t1, t_cmp_ind_col t2

     where t1.batch_id = 2

     and t2.batch_id =  1

     and t1.owner = t2.owner

     and t1.index_name = t2.index_name

     and t1.column_name != t2.column_name

order by ind2,pos2;

Does anybody know of what logic I cna use here or if there are any SQL functions that I can use to show only the index that has changed and the old and new column structures (eg Batch ID 1 index structure and Batch ID 2 index structure)? Any help would be greatly appreciated.

Thanks.

Rob

Best Answer

  • 948207
    948207 Member Posts: 13
    Accepted Answer

    Hi figured it out with the below PL/SQL.

    @rp0428, the idea for this project was to also improve the PL/SQL skills of the interns too. I am aware of the auditing utilities of Oracle, it did not apply to what was sought after here. Thankfully my management didnt kill this 'intern project' because I have now learned Oracle's LISTAGG() function. Thanks for your valuable input.

    For everyone else, this is how I did it:


    DECLARE
    CURSOR IND_COL_DIFFS IS
        select distinct(t1.index_name) as Ind1
        from t_cmp_ind_col t1, t_cmp_ind_col t2
        where t1.batch_id = 1
            and t2.batch_id =  2
            and t1.owner = t2.owner
            and t1.index_name = t2.index_name
            and (t1.column_name != t2.column_name
            OR (t1.column_name = t2.column_name AND t1.column_position != t2.column_position));
           
    v_max_batch_id number(10):= 2;
    v_nonmax_batch_id number(10):= 1;
    v_index_name varchar2(50);
    v_col_agg varchar2(50);
           
    BEGIN
        FOR ind_col_rec in IND_COL_DIFFS
        LOOP
                v_index_name:= ind_col_rec.ind1;           
               
                SELECT listagg (column_name, ',') WITHIN GROUP (ORDER BY column_position) column_name into v_col_agg
                FROM t_cmp_ind_col
                where batch_id = v_nonmax_batch_id
                and index_name = v_index_name
                GROUP BY index_name;       

                dbms_output.put_line(ind_col_rec.ind1||' was ('||v_col_agg||')');
               
                SELECT listagg (column_name, ',') WITHIN GROUP (ORDER BY column_position) column_name into v_col_agg
                FROM t_cmp_ind_col
                where batch_id = v_max_batch_id
                and index_name = v_index_name
                GROUP BY index_name;       

                dbms_output.put_line(ind_col_rec.ind1||' is now('||v_col_agg||')');

        END LOOP;
    END;
    /

    Output:

    CUST_NAME_I was (CUST_NAME)

    CUST_NAME_I is now (CUST_ID,CUST_NAME)

Answers

  • I am trying to build a schema compare script which will compare and alert on changes on a single schema.

    Why? Oracle has spents millions of dollars to create the DBMS_METADATA and DBMS_METADATA_DIFF packages that can compare metadata correctly.

    Don't reinvent the wheel - use the functionality that already exists.

    http://docs.oracle.com/cd/E11882_01/appdev.112/e40758/d_metadiff.htm

    There are plenty of examples on the internet.j

    Oracle also provides FREE the sql developer tool and it has menu options that will use those Oracle packages to compare the objects.

    http://www.oracle.com/technetwork/developer-tools/sql-developer/downloads/index.html?ssSourceSiteId=otnpt

  • 948207
    948207 Member Posts: 13

    Hi rp028,

    This is an intern project with the goal of upskilling the DBA interns on the Oracle concepts and schema structures.

    Rgs,

    Rob

  • This is an intern project with the goal of upskilling the DBA interns on the Oracle concepts and schema structures.

    Ok - but I don't understand the point you are trying to make with that.

    Isn't knowledge of the availability of the DBMS_METADATA and DBMS_METADATA_DIFF packages an important Oracle concept? Especially if it eliminates the need to write complex roll-your-own solutions?

    Learning how to use EXISTING packages is far more valuable than spending time trying to duplicate what has already been done.

    Besides - you said your problem statement was this:

    I am trying to build a schema compare script which will compare and alert on changes on a single schema

    What about this 'concept' - you do NOT need to compare anything to 'alert on changes on a single schema'.

    Oracle has functionality for that also - the AUDIT functionality.

    See the AUDIT command in the SQL Language doc

    http://docs.oracle.com/cd/E11882_01/server.112/e26088/statements_4007.htm

    The 2 day DBA doc has additional information

    http://docs.oracle.com/cd/B28359_01/server.111/b28337/tdpsg_auditing.htm

    Using AUDIT functionality you can 'alert' whenever ANY ddl is issued or modified in a schema. Then you can use those other two packages I mentioned to get the DDL.

    IMHO, the most important skill developers/dbas can learn is how to use Oracle's existing functionality. The majority of questions we get on these forums are generally 'doc' questions; that is, questions that can easily be answered by just referring to the documentation.

    Many posters are just too lazy to search and read the documentation.

    If I were consulting at your organization I would make every effort I could to kill that 'intern project' you describe and replace it will something much more useful to both the interns and your company.

  • 948207
    948207 Member Posts: 13
    Accepted Answer

    Hi figured it out with the below PL/SQL.

    @rp0428, the idea for this project was to also improve the PL/SQL skills of the interns too. I am aware of the auditing utilities of Oracle, it did not apply to what was sought after here. Thankfully my management didnt kill this 'intern project' because I have now learned Oracle's LISTAGG() function. Thanks for your valuable input.

    For everyone else, this is how I did it:


    DECLARE
    CURSOR IND_COL_DIFFS IS
        select distinct(t1.index_name) as Ind1
        from t_cmp_ind_col t1, t_cmp_ind_col t2
        where t1.batch_id = 1
            and t2.batch_id =  2
            and t1.owner = t2.owner
            and t1.index_name = t2.index_name
            and (t1.column_name != t2.column_name
            OR (t1.column_name = t2.column_name AND t1.column_position != t2.column_position));
           
    v_max_batch_id number(10):= 2;
    v_nonmax_batch_id number(10):= 1;
    v_index_name varchar2(50);
    v_col_agg varchar2(50);
           
    BEGIN
        FOR ind_col_rec in IND_COL_DIFFS
        LOOP
                v_index_name:= ind_col_rec.ind1;           
               
                SELECT listagg (column_name, ',') WITHIN GROUP (ORDER BY column_position) column_name into v_col_agg
                FROM t_cmp_ind_col
                where batch_id = v_nonmax_batch_id
                and index_name = v_index_name
                GROUP BY index_name;       

                dbms_output.put_line(ind_col_rec.ind1||' was ('||v_col_agg||')');
               
                SELECT listagg (column_name, ',') WITHIN GROUP (ORDER BY column_position) column_name into v_col_agg
                FROM t_cmp_ind_col
                where batch_id = v_max_batch_id
                and index_name = v_index_name
                GROUP BY index_name;       

                dbms_output.put_line(ind_col_rec.ind1||' is now('||v_col_agg||')');

        END LOOP;
    END;
    /

    Output:

    CUST_NAME_I was (CUST_NAME)

    CUST_NAME_I is now (CUST_ID,CUST_NAME)

  •  the idea for this project was to also improve the PL/SQL skills of the interns too.

    Then, IMHO, it has failed miserably. My comments are mainly directed at whoever gave you this lame assignment. If management has given you busy work to do as an intern then, if you want to keep your intership, you should just do it and keep your mouth shut.

    But if management somehow thinks that the project you describe is actually going to teach you anything useful in the real world (or even in their own org) then that management is either ignorant or seriously deluding themselves. It makes me suspect that the real purpose in giving such an assignment is to keep the interns away from the real work the company is doing so they don't get in the way. And also to allow your manager to tell their manager that hey, we have an intern!

    Teaching anyone to use slow-by-slow (row by row) PL/SQL processing can NOT be considered to be 'improving PL/SQL skills' by any stretch of the imagination. That type of processing is very rarely appropriate but is very often misused when simple SQL or simple PL/SQL could more easily get the job done.

    The code you posted above is incomplete and, for some data (including the sample data you provided) will give poor and incorrect results. I can easily add just one more row to your sample table that will cause your code to fail completely. That's because your queries are expecting only ONE row to be returned but they don't take into account the fact that there can be multiple indexes with the same name owned by different users.

    So It is only 'successful' to the extent that it compiles without error and actually produces 'some' output. But it terms of actually being useful for any real business purpose or teaching useful PL/SQL skills the project is nothing but a red herring.

    I stand by what I said above:

    If I were consulting at your organization I would make every effort I could to kill that 'intern project' you describe and replace it will something much more useful to both the interns and your company.
    

    Best of luck with your internship! I suggest you try to spend as much time as possible picking the brains of the top-tier developers there to find out what problems they REALLY have to deal with and how they go about doing that. That info will serve you well later.

This discussion has been closed.