Programming fun at startup

Assume there exists a table DEPARTMENT. Table EMPLOYEE references DEPARTMENT using field SYS_DEPT_ID. When SYS_DEPT_ID is not indexed, and EMPLOYEE has large no of rows, this can cause long-running TM lock on delete of a DEPARTMENT row because oracle has to figure out if there are any employees working on this department or not. You can use Oracle Grid control to see the wait events and if you see a TM contention wait event than use the following query to identify these types of missing indexes so they can be added. SELECT * FROM ( SELECT c.table_name, cc.column_name, cc.position column_position FROM user_constraints c, user_cons_columns cc WHERE c.constraint_name = cc.constraint_name AND c.constraint_type = 'R' MINUS SELECT i.table_name, ic.column_name, ic.column_position FROM user_indexes i, user_ind_columns ic WHERE i.index_name = ic.index_name ) ORDER BY table_name, column_position;

How to find constraints with same columns but different name between two databases. select prodschema.cons prod_constraint, prodschema.tbl prod_table, prodschema.cols prod_cols, localschema.cons local_constraint, localschema.tbl local_table, localschema.cols local_cols, 'ALTER table '|| prodschema.tbl ||' rename constraint ' || prodschema.cons || ' TO ' || localschema.cons || ';' sqlchg from (select constraint_name cons, constraint_type cons_type, table_name tbl, ltrim(max(sys_connect_by_path(column_name, ',')) keep (dense_rank last order by curr), ',') as cols from (select ucc.constraint_name, uc.constraint_type, ucc.table_name, ucc.column_name, row_number() over (partition by ucc.constraint_name, ucc.table_name order by ucc.position) as curr, row_number() over (partition by ucc.constraint_name, ucc.table_name order by ucc.position) -1 as prev from user_cons_colu...

How to find index with same columns but different name between two databases. select targetDB.idx targetDB_index, targetDB.tbl targetDB_table, targetDB.cols targetDB_cols, sourceDB.idx sourceDB_index, sourceDB.tbl sourceDB_table, sourceDB.cols sourceDB_cols, 'ALTER INDEX ' || targetDB.idx || ' RENAME TO ' || sourceDB.idx || ';' sqlchg from (select index_name idx, table_name tbl, ltrim(max(sys_connect_by_path(column_name, ',')) keep (dense_rank last order by curr), ',') as cols from (select index_name, table_name, column_name, row_number() over (partition by index_name, table_name order by column_position) as curr, row_number() over (partition by index_name, table_name order by column_position) -1 as prev from user_ind_columns@sourceDB where index_name not like 'SYS_%') group by index_name, table_name connect by prev = PRIOR curr and index_name = PRIOR index_n...

SELECT ROWNUM AS rn FROM dual CONNECT BY ROWNUM