Friday, March 16, 2012

OMB OWB TCL Sorting table partitions

When migrating from OWB10.1 to 11gR2 the  partition order (sometimes) gets scrambeld and the table will not validate.  This scripts sets the partitions back in (alphabetical) order .     

[script]
###########################################################
#                                                         #
# Sort Table Partitions                                   #
#                                                          #
# Note: When migrating from OWB10.1 to 11gR2 the          #
#       partition order gets scrambeld and the table      #
#       will not validate                                 #
#       This scripts sets the partitions back in          #
#       alphabetical order                                #
# (C) Ciber Nederland John Minkjan    OMB101.blogspot.com      #
#        V001    Init Version                              #
#        V002    Validated against OWB11gR2                #
#                                                         #
###########################################################

set ProjectList [list ********]
foreach Project $ProjectList {
    OMBCC '/$Project'
    set OracleModuleList [OMBLIST ORACLE_MODULES]
        foreach OracleModule $OracleModuleList {
        puts $OracleModule
        OMBCC '/$Project/$OracleModule';
        set TableList [OMBLIST TABLES];
        foreach Table $TableList {
            set PartitionList [OMBRETRIEVE TABLE '$Table' GET PARTITIONS];
            if { $PartitionList != ""} {
                puts "-- $Table "
                set PartitionListS [lsort $PartitionList]
                if { $PartitionList != $PartitionListS} {
                    set partno 1
                    foreach Partition $PartitionListS {
                        OMBALTER TABLE '$Table' MODIFY PARTITION '$Partition' MOVE TO POSITION $partno
                        puts "$OracleModule - $Table - $Partition  - $partno"
                        set partno [expr $partno + 1 ]
                    }
                    OMBCOMMIT;
                }
            }
        }
    }
}
[\script]

No comments:

Post a Comment