Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> comp.databases.oracle -> Re: Defrag the darn thing

Re: Defrag the darn thing

From: Brian A. Christensen <baco_at_ecsa01.encon.pge.com>
Date: 25 Nov 92 00:27:37 GMT
Message-ID: <215@ecsa01.encon.pge.com>

jjolly_at_jjolly.b24a.ingr.com (Jack G. Jolly) writes:
: Had a (flawed) strategy to defrag a tablespace in the db.
:
: 1) Export db
: 2) Drop tablespace
: 3) Create tablespace
: 4) Import users
:
: The problem was in step (2) - dropping a 300MB tablespace (non-empty)
: generated so many transactions that I got the infamous "snapshot too
: old" error. I have 10 rollback seg's in their own (20MB) tablespace. The
: whole deal is on raw files.
:
: I also read here about another strategy - create a table whose "initial"
: parm is equal to the sum of all freespace extents in the db - the idea
: was that the db would "collect the smaller extents into one big one",
: and then you would drop the table. I tried this idea without success -
: came back with "unable to allocate extent of size XXX" where XXX was
: the sum of all freespace in the tablespace.
:
: Any ideas?? Should the second strategy work??
:
: JJ
:
: ------------------
:
: ^^^^^^^^^ Jack Jolly @ jjolly_at_jjolly.b24a.ingr.com
: (| 0 - |)
: | * | Intergraph Corporation, Huntsville AL
: \_______/ AEC Project Management Support

Our group has written a oraperl program to do deframentation among other things. This program uses the second strategy at defraging. This program does not completely defragment the database but just removes free space that is immediately adjacent to each other. This program also has some other features to keep track of database usage and growth. We use it daily to monitor our database activity. If you haven't used Perl or Oraperl (an extension of Perl for Oracle databases) I highly recommend it as an alternative to writing Pro*C programs. Hope this helps.

&INIT(); &GET_TABLESPACE_SIZES(); &INSERT_TABLESPACE_HISTORY(); &GET_TABLESPACE_HISTORY(); &EXAMINE_OBJECTS(); &CLEANUP(); exit;

#-------------------------------------------------------------------------
#
#	subroutines

#
#-------------------------------------------------------------------------
sub GET_TABLESPACE_SIZES
{

    # This subroutine produces a report listing free space, by tablespace.

format TS_TOP =

                        ECMS Database Administration
                        Tablespace Free Space Report
                        @|||||||||||||||||||||||||||
$date
Tablespace  Max Contiguous     Total Free     Tablespace  Pct    # Free  Honey-
                Free Space          Bytes           Size  Free  Extents   combs
---------- ------------- ------------- ------------- ---- ------- ------

.

    format TS =
@<<<<<<<<<<< @>>>>>>>>>>>> @>>>>>>>>>>>> @>>>>>>>>>>>> @## @>>>>>> @>>>> $tablespace_name, $max_contiguous_spaceX, $total_free_bytesX, $tablespace_sizeX, $percent_available, $total_free_extentsX, $honeycomb_count_tsX .

    format TS_BOTTOM =


                                                                          @>>>>
$honeycomb_countX

.

$- = 0;	     # force top-of-form
$^ = "TS_TOP";
$~ = "TS";

    $honeycomb_count    = 0;

    $total_free_bytes = 0;
    $total_free_extents = 0;

    # this query will get size information about each tablespace.     

    $tablespace_query = <<EOFEOF;
select tablespace_name, sum(bytes)
from sys.dba_data_files
where status = 'AVAILABLE'
group by tablespace_name
order by tablespace_name
EOFEOF     # open the cursor
    $tablespace_csr = &ora_open($lda, $tablespace_query) || die $ora_errstr;          

    # get all tablespaces
    while (($tablespace_name, $tablespace_size) = &ora_fetch($tablespace_csr))     {         

	# Build associative array %ts_max with tablespace_name and max_free.
	$max_contiguous_space = &COALESCE_FREE_SPACE($tablespace_name);
	$ts_max{$tablespace_name} = $max_contiguous_space;

	# Calculate free space information
	&SUM_FREE_SPACE($tablespace_name); 

	$percent_available = 100 * ($total_free_bytes / $tablespace_size);
	
	# Build associative array %ts_current with tablespace_name and
	# pct_free.  It is used for inserting history.
	$ts_current{$tablespace_name} = $percent_available;

	# Format the big numbers by adding commas.
	$max_contiguous_spaceX = &commas($max_contiguous_space);
	$total_free_bytesX     = &commas($total_free_bytes);
	$tablespace_sizeX      = &commas($tablespace_size);
	$total_free_extentsX   = &commas($total_free_extents);
        $honeycomb_count_tsX   = &commas($honeycomb_count_ts);
	write;
	
        # -r "repair honeycombs" is a command line option.
	# Only repair honeycombs when they exist and are requested.
	&REPAIR_HONEYCOMBS() if ($opt_r &&  $honeycomb_count_ts > 0);
	

    }     

    # close the cursor
    &ora_close($tablespace_csr) || die $ora_errstr;

    $honeycomb_countX = &commas($honeycomb_count);     # Write the final report total.
    $~ = 'TS_BOTTOM';
    write;
}

sub COALESCE_FREE_SPACE
{     

    # This subroutine calculates the size of the largest piece of
    # contiguous free space in a tablespace.
    # It requires tablespace_name as an input argument.
    # It returns the size of the largest piece of contiguous free space.

    local ($ts) = @_;

    $previous_end_block   = 0;
    $previous_start_block = 0;
    $previous_end_id   = 0;
    $previous_start_id = 0;
    $honeycomb_count_ts = 0;

    undef %free_list;
    undef @free_bytes;     

    # This query will get free space information about one tablespace.     $free_space_query = <<EOFEOF;
select space.file_id, block_id "start block",

       block_id + space.blocks -1 "end block" from sys.dba_free_space space, sys.dba_data_files files

where space.tablespace_name = files.tablespace_name
and   space.file_id         = files.file_id
and   files.tablespace_name = '$ts'

and status = 'AVAILABLE'
order by 1,2
EOFEOF          # open the cursor
    $free_space_csr = &ora_open($lda, $free_space_query) || die $ora_errstr;          

    # Get all free_space segments for one tablespace     while (($file_id, $start_block, $end_block) = &ora_fetch($free_space_csr))     {

	# Combine file id and block address.  This will allow support
	# for tablespaces with more than one datafile.
	$start_id = "$file_id:$start_block";
	$end_id   = "$file_id:$end_block";
	$start_id_minus_1 = "$file_id:" . ($start_block - 1);

	# Build associative array %free_list of start_id and
	# end_id file ids and block addresses.
	if ($start_id_minus_1 eq $previous_end_id)
	{
	    # This free space is adjacent to the previous free space.
	    # Oracle will coalesce two adjacent free spaces into a larger
	    # free space, if it cannot find a single free space of the
	    # needed size.
	    $free_list{$previous_start_id} = $end_id;

	    # uptdate saved key
            # do not change $previous_start_id
            $previous_end_id   = $end_id;
	    $honeycomb_count++;
            $honeycomb_count_ts++;
	}
	else
        {
	    # This free space is not immediately preceeded by a free segment.
	    # Create a new entry in the free_list.
	    $free_list{$start_id} = $end_id;

	    # update saved keys
            $previous_end_id   = $end_id;
            $previous_start_id = $start_id;
	}

    }     

    # close the cursor
    &ora_close($free_space_csr) || die $ora_errstr;

    # Look at each entry in the %free_list.     # Identify the largest piece of contiguous free space.

    $max_free_bytes = 0;
    $start_biggest  = 0;
    $end_biggest    = 0;

    while (($start_id, $end_id) = each %free_list)     {

	($file_id, $start_block) = split (/:/, $start_id);
	($file_id, $end_block)   = split (/:/, $end_id);
	
	$bytes = ($end_block - $start_block + 1) * $ORA_BLOCK_SIZE;
        if ($max_free_bytes < $bytes)
	{
            $max_free_bytes = $bytes;
	    $start_biggest  = $start_block;
	    $end_biggest    = $end_block;
        }
	# Save the size of each free space.
	push(@free_bytes, $bytes);

    }

    # Return the size the largest piece of contiguous free space.     return $max_free_bytes;
}

sub NUMERICALLY
{

    # This subroutine is used to sort by number value, rather than string     # contents.
    $a <=> $b;
}

sub REPAIR_HONEYCOMBS
{

    # This subroutine fills up all free space with dummy tables, then
    # drops the dummy tables.  This will force the RDBMS to coalesce the
    # honeycombed free space.  Honeycombs are adjacent free spaces.

    # Sort the sizes of the free spaces in descending order.     undef @sorted_free_bytes;
    @sorted_free_bytes = reverse sort NUMERICALLY @free_bytes;

    # Create the dummy tables from the largest size down to the smallest size.     $i = 0;
    while (@sorted_free_bytes)
    {

	$dropme_size = shift @sorted_free_bytes;
	last if $dropme_size <= $ORA_BLOCK_SIZE; # don't create one-block table
	$i++;
	$create_statement = "CREATE TABLE DROPME${i} (DUMMY NUMBER)
             STORAGE(INITIAL $dropme_size) TABLESPACE $tablespace_name";
        &ora_do($lda, $create_statement)
	    ||  warn "$create_statement\n$ora_errstr";
    }

    # Drop the dummy tables.
    while ($i)
    {

	$drop_statement = "DROP TABLE DROPME${i}";
        &ora_do($lda, $drop_statement)
	    ||  warn "$drop_statement\n$ora_errstr";
	$i--;

    }
}

sub SUM_FREE_SPACE
{     

    # This subroutine calculates the sum of free bytes and the count of 
    # free extents in a tablespace.
    # It requires tablespace_name as an input argument.
    # The calculated values are stored in global variables.

    local ($ts) = @_;     

    # This query will get free space information about one tablespace.     $sum_free_query = <<EOFEOF;
select sum(space.bytes), count('x')
from sys.dba_free_space space, sys.dba_data_files files

where space.tablespace_name = files.tablespace_name
and   space.file_id         = files.file_id
and   files.tablespace_name = '$ts'

and status = 'AVAILABLE'
EOFEOF          # open the cursor
    $sum_free_csr = &ora_open($lda, $sum_free_query) || die $ora_errstr;          

    # Get summary free_space info for one tablespace. There should only     # be one row.
    ($total_free_bytes, $total_free_extents) = &ora_fetch($sum_free_csr);     warn $ora_errstr if ($ora_errno);          

    # close the cursor
    &ora_close($sum_free_csr) || die $ora_errstr; }

sub INSERT_TABLESPACE_HISTORY
{

    # This subroutine inserts current statistics into TABLESPACE_HIST.

    # get current date in Oracle default date format     @MoY = ('JAN','FEB','MAR','APR','MAY','JUN',

            'JUL','AUG','SEP','OCT','NOV','DEC');     ($sec, $min, $hour, $mday, $mon, $year) = localtime(time);     $dd_mon_yy = sprintf("%02d-%s-%02d", $mday, $MoY[$mon], $year);

    while (($tablespace_name, $percent_free) = each %ts_current)     {

        $delete_statement = <<EOFEOF;
delete from TABLESPACE_HIST
where tablespace_name = '$tablespace_name' and check_date = '$dd_mon_yy' EOFEOF
        $insert_statement = <<EOFEOF;
insert into TABLESPACE_HIST
values ('$tablespace_name', $percent_free, '$dd_mon_yy') EOFEOF

        &ora_do($lda, $delete_statement) || die "$delete_statement\n$ora_errstr";
        &ora_do($lda, $insert_statement) || die "$insert_statement\n$ora_errstr";
    }
}

sub GET_TABLESPACE_HISTORY
{

    # This subroutine produces a report listing historical percent_available,     # by tablespace.

format HIST_TOP =

                        ECMS Database Administration
                         Tablespace History Report
                        @|||||||||||||||||||||||||||
$date

.

    format HIST =
@<<<<<<<<<<<<< @>> @>> @>> @>> @>> @>> $tablespace_name, $current, $yesterday, $last_week, $last_month, $three_months_ago, $six_months_ago .

$- = 0;	     # force top-of-form
$^ = "HIST_TOP";
$~ = "HIST";


    # this query will get historical information about each tablespace.     

    $historical_query = <<EOFEOF;

select 	tablespace_name,
	max(decode(check_date,to_char(sysdate),
	    round(pct_available),NULL)) "current",
	max(decode(check_date,to_char(sysdate-1),
	    round(pct_available),NULL)) yesterday,
	max(decode(check_date,to_char(sysdate-7),
	    round(pct_available),NULL)) last_week,
	max(decode(check_date,to_char(sysdate-30),
	    round(pct_available),NULL)) last_month,
	max(decode(check_date,to_char(sysdate-90),
	    round(pct_available),NULL)) three_months_ago,
	max(decode(check_date,to_char(sysdate-180),
	    round(pct_available),NULL)) six_months_ago
from tablespace_hist
group by tablespace_name
order by tablespace_name
EOFEOF               # open the cursor
    $historical_csr = &ora_open($lda, $historical_query) || die $ora_errstr;     

    # get the history for each tablespace     while (($tablespace_name, $current, $yesterday, $last_week, $last_month,

	    $three_months_ago, $six_months_ago) =
	   &ora_fetch($historical_csr))
    {
	# Print the report.  
	write;

    }     

    # close the cursor
    &ora_close($historical_csr) || die $ora_errstr;

    print "\n\n\n";

}

sub EXAMINE_OBJECTS
{

    # This subroutine examines the future space needs of each object.
    # It produces a report listing each object that will not find enough space,
    # the next time it tries to extend.

    $problem = 0;
    $no_problem = 0;

format PROBLEM_TOP =

                        ECMS Database Administration
                        Tablespace Examination Report
                        @|||||||||||||||||||||||||||
$date
                                                                            Pct
Owner         Object Name                    Tablespace   Ext     Next Ext  Inc
------------  ------------------------------ -----------  --- ------------  ---

.

    format PROBLEM =

@<<<<<<<<<<<  @<<<<<<<<<<<<<<<<<<<<<<<<<<<<< @<<<<<<<<<<  @## @>>>>>>>>>>>  @##
$owner, $segment_name, $tablespace_name, $extents, $next_extentX, $pct_increase
          @>>>>>>>>>>>> will be requested, but only @>>>>>>>>>>>> is available.
           $calculated_next_extentX, $ts_maximumX

.

$- = 0;	     # force top-of-form
$^ = "PROBLEM_TOP";
$~ = "PROBLEM";

    format PROBLEM_BOTTOM =

              @>>>>>> Objects will have a problem extending. $problemX

              @>>>>>> Objects will not have a problem extending. $no_problemX

.

    # this query will get size information about each object.     

    $object_query = <<EOFEOF;
    select seg.tablespace_name, seg.owner, seg.segment_name, seg.extents,

           nvl(tab.next_extent, ind.next_extent),
           nvl(tab.pct_increase, ind.pct_increase)
    from sys.dba_segments seg, sys.dba_tables tab, sys.dba_indexes ind     where seg.owner = tab.owner(+)
    and seg.segment_name = tab.table_name(+)
    and seg.owner = ind.owner(+)
    and seg.segment_name = ind.index_name(+)
    and (ind.index_name(+) is not null or tab.table_name(+) is not null)     order by 2,1
EOFEOF          # open the cursor
    $object_csr = &ora_open($lda, $object_query) || die $ora_errstr;          

    # get all objects
    while (($tablespace_name, $owner, $segment_name, $extents, $next_extent,

            $pct_increase) = &ora_fetch($object_csr))     {

	# examine the object
	# The calculation used for the size of the next extent is:
	# NEXT * ((1 + PERCENT_INCREASE/100) to the power of (new_ext_num -2))
	# where:
	# NEXT is the NEXT parameter from the "create object" storage clause
	
	$calculated_next_extent =
	    $next_extent * ( (1 + ($pct_increase/100)) ** (($extents + 1) - 2) );

        # round up to an even multiple of ORA_BLOCK_SIZE.
	$calculated_next_extent =
	(int($calculated_next_extent/$ORA_BLOCK_SIZE) + 1) * $ORA_BLOCK_SIZE
	    unless ($calculated_next_extent % $ORA_BLOCK_SIZE == 0);

	if ($calculated_next_extent <= $ts_max{$tablespace_name})
	{
	    # this object will be able to extend
	    $no_problem++;
	}
	else
	{
	    # This object will not be able to extend.  Write the report.
	    $problem++;

	    # Format the big numbers, by adding commas.
	    $next_extentX            = &commas($next_extent);
	    $calculated_next_extentX = &commas($calculated_next_extent);
	    $ts_maximumX             = &commas($ts_max{$tablespace_name});

	    write;
	}

    }     

    # close the cursor
    &ora_close($object_csr) || die $ora_errstr;

    # Format the big numbers, by adding commas.     $problemX = &commas($problem);
    $no_problemX = &commas($no_problem);     # Write the final report totals.
    $~ = 'PROBLEM_BOTTOM';
    write;
}

sub INIT
{

    # This subroutine defines constants, processes command line arguments,     # and connects to the database.

    # Hide the command line passwords from anyone using the "ps" command.
    # Users of the "ps" command will only see "my_prog", instead of
    # "/usr/local/bin/my_prog my_passwd".
    $ARGV[0] =~ s/\s+.*//;	# get rid of arguments in ps listing
    $0 =~ s=.*/==;	        # get rid of path to program name

    

    $ORA_BLOCK_SIZE = 2048;
    require "ctime.pl";
    require "getopts.pl";     

    #get command line options
    &Getopts('hr');     

    $sid = "$ARGV[0]";
    $userid = "$ARGV[1]";
    $password = "$ARGV[2]";

    $userid =~ tr/a-z/A-Z/; # change $userid to upper case.     

    chop($date = &ctime(time));          

    $usage = <<EOFEOF;

Usage: $0 [-r] [-h] sid userid password

        -r = repair honeycombed free space
        -h = help

	sid      = $ORACLE_SID instance name
	userid   = oracle userid
	password = oracle password
	    
	    

Description:

This oraperl program identifies database objects that will not be able to find enough freespace on their next extension. It creates these reports:

The Oracle userid must have select access to the SYS.DBA% tables.     

EOFEOF     if ($opt_h)
    {

	print $usage;
	exit;

    }     

    # argv[0] = sid, argv[1] = userid, argv[2] = password     # $#ARGV == 2 means 3 command line arguments were entered     if ($#ARGV != 2)
    {

	print "Error:  Incorrect number of command line arguments\n";
	print $usage;
	exit;

    }     

    warn ("Userid is not SYSTEM. It is $userid\n")

        unless ($userid =~ /SYSTEM/);

    # connect to the database
    $lda = &ora_login("$sid", "$userid", "$password")

        || die "$sid, $userid, $password $ora_errstr";     

    select(STDERR); $| = 1;         # make unbuffered
    select(STDOUT); $| = 1;         # make unbuffered


}

sub CLEANUP
{

    if ($opt_r)
    {

        print "\n\nThe repair of $honeycomb_count honeycombed free spaces is complete.\n";     }     

    format EOR =

End of Report

.     

    # Write the End of Report trailer.
    $~ = 'EOR';
    write;

    # disconnect from the database
    &ora_logoff($lda) || warn "REM $ora_errstr";

}

# from the Camel book, chapter 5
sub commas {

    local($_) = @_;
    1 while s/(.*\d)(\d\d\d)/$1,$2/;
    $_;
}

#-------------------------------------------------------------------------
#
#	end of source

#
#-------------------------------------------------------------------------
--- CUT HERE --- Received on Tue Nov 24 1992 - 18:27:37 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US