In our database, we have a table which has 12 partitions and each partitions has 4 subpartitions, defrag.pl can not handle this table, in their defrag_USER_add_tbl.sql, all the storage parameters do not have value, which will return error, maybe somebody can fix this?
For your convinence, I am enclosing the perl scripts to create the table if you want to test it yourself.
#!/usr/local/bin/perl
use strict;
use DBI;
my $MAX_TRY = 50;
my ( $mon,$year,$day,$monname);
my $dbh = DBI->connect ( 'dbi:Oracle:dev',
'scott',
'tiger',
{
PrintError => 0,
RaiseError => 0
}
) || die "Database Connection not made $DBI::errstr" ;
my $monname = "Juan";
my $mon = 1;
my $year = 2002;
$day=31;
#drop table historical_rtms
my $sql = qq{ drop table historical_rtms };
$dbh->do ( $sql );
if (( $dbh->state != 0 ) || ( $dbh->errstr =~ "ORA"))
{
print "Error drop table historical_rtms = ", $dbh->errstr, " and error value ", $dbh->state, "\n";
}
#create table historical_rtms
my $sql = qq{ create table historical_rtms (long_vehicle number(3),occupancy number(3),
speed number(3), lanenum number(1), sensor_id number(6),polltime date,rtms_sensor_data_id number(38),
metro_id number(3)) partition by range ( polltime ) subpartition by hash ( metro_id) subpartitions 4 (partition YEAR_2002_1 values less than (to_date('2002/12/31 23:59:59','yyyy/mm/dd hh24:mi:ss')))
tablesapce users };
$dbh->do ( $sql );
if (( $dbh->state != 0 ) || ( $dbh->errstr =~ "ORA"))
{
print "Error alter table rtms_historical is = ", $dbh->errstr, " and error value ", $dbh->state, "\n";
}
#split partition
while ( $mon < 13 ) {
my $i = $mon + 1;
if ( $mon != 12 ) {
my $sql = qq{ alter table historical_rtms split partition YEAR\_$year\_$mon at (to_date('$year/$mon/$day 23:59:59','yyyy/mm/dd hh24:mi:ss')) into (partition YEAR\_$year\_$mon, partition YEAR\_$year\_$i) };
print "$sql\;\n";
$dbh->do ( $sql );
if (( $dbh->state != 0 ) || ( $dbh->errstr =~ "ORA"))
{
print "Error alter table rtms_historical is = ", $dbh->errstr, " and error value ", $dbh->state, "\n";
}
}
#rename subpartition
my $j = "YEAR\_$year\_$mon";
my $jj = "YEAR\_$year\_$monname";
my $sql = qq{ select subpartition\_name from user_tab_subpartitions where partition\_name = \'$j\' };
my $sth = $dbh->prepare($sql);
if ( $dbh->state != 0 )
{
die "error getting date from traffic.metro_area", $dbh->errstr," and error value", $dbh->state,"\n";
}
$sth->execute();
if (( $dbh->state != 0 ) || ( $dbh->errstr =~ "ORA"))
{
print "Error select from traffic.metro\_area = ", $dbh->errstr, " and error value ", $dbh->state, "\n";
}
my $counter = 0;
my $i=0;
while ( my @row = $sth -> fetchrow_array ) {# print "$row[$i], $j\n" ;
#rename the subpartition inside the partition
my $sql = qq{ alter table historical_rtms rename subpartition $row[$i] to $jj\_$counter };
print "$sql\;\n";
$counter = $counter + 1;
$dbh->do ( $sql );
if (( $dbh->state != 0 ) || ( $dbh->errstr =~ "ORA"))
{
print "Error renam historical_rtms = ", $dbh->errstr, " and error value ", $dbh->state, "\n";
}
}
In our database, we have a table which has 12 partitions and each partitions has 4 subpartitions, defrag.pl can not handle this table, in their defrag_USER_add_tbl.sql, all the storage parameters do not have value, which will return error, maybe somebody can fix this?
For your convinence, I am enclosing the perl scripts to create the table if you want to test it yourself.
#!/usr/local/bin/perl
use strict;
use DBI;
my $MAX_TRY = 50;
my ( $mon,$year,$day,$monname);
my $dbh = DBI->connect ( 'dbi:Oracle:dev',
'scott',
'tiger',
{
PrintError => 0,
RaiseError => 0
}
) || die "Database Connection not made $DBI::errstr" ;
my $monname = "Juan";
my $mon = 1;
my $year = 2002;
$day=31;
#drop table historical_rtms
my $sql = qq{ drop table historical_rtms };
$dbh->do ( $sql );
if (( $dbh->state != 0 ) || ( $dbh->errstr =~ "ORA"))
{
print "Error drop table historical_rtms = ", $dbh->errstr, " and error value ", $dbh->state, "\n";
}
#create table historical_rtms
my $sql = qq{ create table historical_rtms (long_vehicle number(3),occupancy number(3),
speed number(3), lanenum number(1), sensor_id number(6),polltime date,rtms_sensor_data_id number(38),
metro_id number(3)) partition by range ( polltime ) subpartition by hash ( metro_id) subpartitions 4 (partition YEAR_2002_1 values less than (to_date('2002/12/31 23:59:59','yyyy/mm/dd hh24:mi:ss')))
tablesapce users };
$dbh->do ( $sql );
if (( $dbh->state != 0 ) || ( $dbh->errstr =~ "ORA"))
{
print "Error alter table rtms_historical is = ", $dbh->errstr, " and error value ", $dbh->state, "\n";
}
#split partition
while ( $mon < 13 ) {
my $i = $mon + 1;
if ( $mon != 12 ) {
my $sql = qq{ alter table historical_rtms split partition YEAR\_$year\_$mon at (to_date('$year/$mon/$day 23:59:59','yyyy/mm/dd hh24:mi:ss')) into (partition YEAR\_$year\_$mon, partition YEAR\_$year\_$i) };
print "$sql\;\n";
$dbh->do ( $sql );
if (( $dbh->state != 0 ) || ( $dbh->errstr =~ "ORA"))
{
print "Error alter table rtms_historical is = ", $dbh->errstr, " and error value ", $dbh->state, "\n";
}
}
#rename subpartition
my $j = "YEAR\_$year\_$mon";
my $jj = "YEAR\_$year\_$monname";
my $sql = qq{ select subpartition\_name from user_tab_subpartitions where partition\_name = \'$j\' };
my $sth = $dbh->prepare($sql);
if ( $dbh->state != 0 )
{
die "error getting date from traffic.metro_area", $dbh->errstr," and error value", $dbh->state,"\n";
}
$sth->execute();
if (( $dbh->state != 0 ) || ( $dbh->errstr =~ "ORA"))
{
print "Error select from traffic.metro\_area = ", $dbh->errstr, " and error value ", $dbh->state, "\n";
}
my $counter = 0;
my $i=0;
while ( my @row = $sth -> fetchrow_array ) {# print "$row[$i], $j\n" ;
#rename the subpartition inside the partition
my $sql = qq{ alter table historical_rtms rename subpartition $row[$i] to $jj\_$counter };
print "$sql\;\n";
$counter = $counter + 1;
$dbh->do ( $sql );
if (( $dbh->state != 0 ) || ( $dbh->errstr =~ "ORA"))
{
print "Error renam historical_rtms = ", $dbh->errstr, " and error value ", $dbh->state, "\n";
}
}
$mon = $mon + 1;
if ($mon == 1 ) { $day=31; $monname = 'Juan';}
elsif ( $mon == 2) {$day = 28; $monname = 'Feb';}
elsif ( $mon == 3) {$day=31; $monname = 'March';}
elsif ( $mon == 4) {$day=30; $monname = 'April';}
elsif ( $mon == 5) {$day=31; $monname = 'May';}
elsif ( $mon == 6) {$day=30; $monname = 'June';}
elsif ( $mon == 7) {$day=31; $monname = 'July';}
elsif ( $mon == 8) {$day=31; $monname = 'August';}
elsif ( $mon == 9) {$day=30; $monname = 'Sept';}
elsif ( $mon == 10) {$day=31; $monname = 'Oct';}
elsif ( $mon == 11) {$day=30; $monname = 'Nov';}
elsif ( $mon == 12) {$day=31; $monname = 'Dec';}
}
#$sth->finish();
$dbh->disconnect();
1;
--------------------------------
just run it and you will create a table in schema
scott, and you can test it yourself
thanks