Pro*C program to defragment free space in a given tablespace
Date: Wed, 5 May 1993 14:44:57 GMT
Message-ID: <1993May5.144457.1793_at_oracle.us.oracle.com>
Included at the end of this article is a Pro*C program which will coalesce free extents in a given tablespace. This operation is often required when database objects with differing storage clauses have been created and dropped leaving large numbers of fragmented free extents. Oracle works best if the free space in a tablespace is made up of single large extents rather than numerous small ones.
The program relies on the fact that when an object, in this case a temporary table, is dropped its extents are put on the free list as single coalesced extents even if when the extents were originally allocated they were made up of multiple contiguous free blocks.
The program works by creating temporary tables with initial extents calculated to occupy any adjacent free extents. Since Oracle will break up a large extent in preference to using adjacent extents, the program creates tables to occupy these extents to force Oracle to use adjacent extents. When there are no more adjacent free extents in the tablespace the program drops all the tables it has created.
This program is not required with Oracle7 which delegates this task to the SMON process.
The program can be compiled using the proc.mk file supplied in the Pro*C demo directory. I have tested the program on:
Sun-4 Sun0s 4.1.X
Pyramid OSx 5.1
Pyramid DC/OSx 1.1
IBM RS6000 AIX 3.2
NCR 3000 V.4 Rel 2.0
Limitations:
- Does not accept command line arguments
- echos password
- Only does 1 tablespace at a time
- Assumes Oracle block size of 2K (this can be changed by recompilation - see source file)
- May need to increase dc_free_extents init.ora parameter
This program is NOT supported by Oracle, however I will attempt to deal with any suggestions, problems or enhancements you may have.
Enjoy
David
David Ruthven, Unix Post Sales Support, Oracle UK, The Ring, Bracknell RG12 1BW Email: druthven_at_uk.oracle.com
/*
- PROGRAM: defrag.pc
*
- AUTHOR: David Ruthven (Oracle UK)
*
- DATE: 05-MAY-93
*
- STATUS: This program is unsupported.
*
- PURPOSE: Eliminate free extent fragmentation for a given
- tablespace
*
- USAGE: Prompts for dba username, password, tablespace and
- temporary table name template
*
- LIMITATIONS: 1. Does not accept command line arguments
- 2. echos password
- 3. Only does 1 tablespace at a time
- 4. Assumes Oracle block size of 2K (this can be
- changed by recompilation)
*
*
- PLATFORMS: program compiles and runs on:
*
- Sun-4 Sun0s 4.1.X
- Pyramid OSx 5.1
- Pyramid DC/OSx 1.1
- IBM RS6000 AIX 3.2
- NCR 3000 V.4 Rel 2.0
*
- APPROACH:
*
- get name of tablespace to defragment
- get temporary table name template
- if any tables match template
- request if ok to continue
- LOOP
- initialise variables
- LOOP
- identify largest contiguous extent
- note if any adjacent free extents
- END LOOP
- IF there are adjacent free extents THEN
- CREATE TABLE to occupy largest extent(s)
- ELSE
- EXIT
- END LOOP
- DROP all temporary tables
*/
/* ---< User configurable defines >---------------------------------------- */ #define ORA_BLK_SIZE 2048 /* Oracle database block size */#define TMP_TABLE "DEFRAG$TAB_" /* name template for temporary tables */
/* ---< End of User configurable defines >--------------------------------- */ #include <stdio.h> #include <ctype.h>
#include <sys/types.h>
#include <time.h>
static char prog_name[] = "DEFRAG"; static char prog_stat[] = "Unsupported"; static char prog_ver[] = "1.0"; #define SUCCESS 1 #define FAILURE 0
#define EXTENT struct extent_details
EXTENT {
int file_id;
int block_id;
int blocks;
int extents;
};
EXTENT le; /* largest contiguous extent so far */ EXTENT cl; /* current contiguous extent */ int nxt_block_id; int extent_set; /* true if >1 contig. free extents */ int table_no; /* no. of last created temporary table */
EXEC SQL INCLUDE SQLCA;
/*
The SQL Communications Area is a structure through which ORACLE makes run- time status information such as error codes, warning flags, and diagnostic text available to the program.
*/
EXEC SQL BEGIN DECLARE SECTION;
/*
All host variables used in embedded SQL must appear in the DECLARE SECTION.
*/
VARCHAR sql_stmt[512];
int file_id; int block_id; int blocks; int extent_size;
VARCHAR tablespace[32];
VARCHAR table_name[32];
VARCHAR template[64];
VARCHAR user[32];
VARCHAR pswd[32];
EXEC SQL END DECLARE SECTION; void banner();
int ora_connect(); int declare_cursor(); int defragment(); int get_tablespace_name(); int get_template(); int create_table(); int drop_tables();
void disconnect();
void show_error();
int asks();
void uppstr();
main()
{
int status;
banner(prog_name, prog_ver, prog_stat); if (ora_connect() != SUCCESS)
disconnect(FAILURE);
if (get_tablespace_name() != SUCCESS)
disconnect(FAILURE);
if (get_template() != SUCCESS)
disconnect(FAILURE);
if (declare_cursor() != SUCCESS)
disconnect(FAILURE);
if (defragment() != SUCCESS)
disconnect(FAILURE);
status = drop_tables();
disconnect(status);
}
/*
- display banner showing program name, version and status
*/
void banner(prog_name, prog_ver, prog_stat) char *prog_name, *prog_ver, *prog_stat; { time_t today; char *date_str;
today = time((long *) 0);
date_str = ctime(&today);
printf("\n%s: Version %s - %s on %s",
prog_name, prog_ver, prog_stat, date_str); printf("\nClassification: AT USERS RISK (UNSUPPORTED)\n\n"); }
/*
- prompt for password for Oracle DBA user
*
- connect to database
*/
ora_connect()
{
int answer;
answer = asks("DBA Username: ", user.arr);
if (answer <= 0)
return (FAILURE);
user.len = strlen(user.arr);
answer = asks("Password: ", pswd.arr);
if (answer <= 0)
return (FAILURE);
pswd.len = answer;
EXEC SQL WHENEVER SQLERROR GOTO SQL_ERROR; EXEC SQL CONNECT :user IDENTIFIED BY :pswd;
return (SUCCESS);
SQL_ERROR:
{
show_error("CONNECT");
return (FAILURE);
}
}
/*
FUNCTION: int declare_cursor()
ARGUMENTS: none
RETURN: SUCCESS
FAILURE
PURPOSE: declare cursor to fetch rows from free extents list for
the current tablespace.
APPROACH: rows are returned in file# and block# order to make
identification of rows for amalgamation a single-pass
operation
TABLES:
SYS.DBA_FREE_SPACE
Name Null? Type
------------------------------- -------- ----
TABLESPACE_NAME NOT NULL CHAR(30)
FILE_ID NOT NULL NUMBER
BLOCK_ID NOT NULL NUMBER
BYTES NUMBER
BLOCKS NOT NULL NUMBER
-----------------------------------------------------------------------------
*/
declare_cursor()
{
EXEC SQL WHENEVER SQLERROR GOTO SQL_ERROR; EXEC SQL DECLARE C1 CURSOR FOR
SELECT file_id
, block_id
, blocks
FROM sys.dba_free_space
WHERE tablespace_name = :tablespace
ORDER BY file_id, block_id;
return (SUCCESS);
SQL_ERROR:
{
show_error("DECLARE CURSOR");
return (FAILURE);
}
}
defragment()
{
for (;;)
{
EXEC SQL WHENEVER SQLERROR GOTO CURSOR_OPEN_ERROR; EXEC SQL OPEN C1;
EXEC SQL WHENEVER SQLERROR GOTO FETCH_ERROR;
EXEC SQL WHENEVER NOT FOUND GOTO NO_FREE_EXTENTS;
EXEC SQL FETCH C1
INTO :file_id,
:block_id,
:blocks;
cl.file_id = file_id;
cl.block_id = block_id;
cl.blocks = blocks;
cl.extents = 1;
/* initialise current largest extent with first row */
nxt_block_id = cl.block_id + cl.blocks;
/* calculate block_id of next potential adjacent free extent */
extent_set = 0;
le.file_id = 0;
le.block_id = 0;
le.blocks = 0;
le.extents = 1;
/* initialise details for largest extent */
for (;;)
{
EXEC SQL WHENEVER NOT FOUND GOTO END_OF_FETCH;
/* no more rows */
EXEC SQL FETCH C1
INTO :file_id,
:block_id,
:blocks;
/* get next row */
/*
* locate largest contiguous
* set of free extents
*/
if (file_id == cl.file_id
&& block_id == nxt_block_id)
{
/*
* we can accumulate
*/
cl.blocks += blocks;
cl.extents += 1;
extent_set++;
/* more than one extent used */
}
else
{
/*
* cannot accumulate any more extents for this set
* check if this is now the largest free extent
*/
if (cl.blocks > le.blocks)
{
le.block_id = cl.block_id;
le.blocks = cl.blocks;
le.extents = cl.extents;
}
cl.file_id = file_id;
cl.block_id = block_id;
cl.blocks = blocks;
cl.extents = 1;
}
nxt_block_id = cl.block_id + cl.blocks;
}
END_OF_FETCH:
EXEC SQL WHENEVER NOT FOUND CONTINUE;
EXEC SQL WHENEVER SQLERROR GOTO CURSOR_CLOSE_ERROR;
EXEC SQL CLOSE C1;
/*
* check if this is now the largest free extent
*/
if (cl.blocks > le.blocks)
{
le.block_id = cl.block_id;
le.blocks = cl.blocks;
le.extents = cl.extents;
}
/*
* if there are no adjacent free extents then no more
* defragmentation can be done
*/
if (extent_set > 0)
{
if (create_table() == FAILURE)
return(FAILURE);
}
else
{
EXEC SQL CLOSE C1;
return (SUCCESS);
}
}
EXEC SQL WHENEVER SQLERROR CONTINUE;
EXEC SQL WHENEVER NOT FOUND CONTINUE;
NO_FREE_EXTENTS:
{
if (le.block_id == 0)
printf("No free extents for tablespace '%s'\n", tablespace.arr);
EXEC SQL CLOSE C1;
return (SUCCESS);
}
FETCH_ERROR:
{
show_error("FETCH");
EXEC SQL CLOSE C1;
return (FAILURE);
}
CURSOR_OPEN_ERROR:
{
show_error("OPENING CURSOR");
return (FAILURE);
}
CURSOR_CLOSE_ERROR:
{
show_error("CLOSING CURSOR");
return (FAILURE);
}
}
/*
- prompt for tablespace name
*
*/
get_tablespace_name()
{
int answer;
answer = asks("Tablespace name: ", tablespace.arr);
if (answer <= 0)
return (FAILURE);
tablespace.len = answer;
uppstr(tablespace.arr);
return (SUCCESS);
}
/*
- get template for temporary table names
*
- if some tables match
- list names of tables which match template
- if tables should not be dopped
- then
- exit
- else
- drop the tables
*
*/
get_template()
{
char sql_1[64];
char sql_2[64];
char sql_3[64];
char prompt[80];
unsigned char yesno[32];
int answer;
int count; /* count of table names matching template */
sprintf(prompt, "Temporary table template [%s]: ", TMP_TABLE);
answer = asks(prompt, template.arr);
if (answer < 0)
return (FAILURE);
if (answer == 0)
{
/* user chose default */
strcpy(template.arr, TMP_TABLE);
}
strcat(template.arr, "%");
/* ensure template is a pattern stem for matching table names */
template.len = answer;
uppstr(template.arr);
EXEC SQL WHENEVER SQLERROR GOTO SQL_ERROR; /*
- build sql statement to select table
- names matching template
*/ strcpy(sql_1, "select table_name from sys.dba_tables"); sprintf(sql_2, "where tablespace_name = '%s'", tablespace.arr); sprintf(sql_3, "and table_name like '%s'", template.arr);
sprintf(sql_stmt.arr, "%s %s %s", sql_1, sql_2, sql_3); sql_stmt.len = strlen(sql_stmt.arr);
/*
* PREPARE, DECLARE, OPEN
*/
EXEC SQL PREPARE S3 FROM :sql_stmt;
EXEC SQL DECLARE C3 CURSOR FOR S3;
EXEC SQL OPEN C3;
EXEC SQL WHENEVER NOT FOUND GOTO END_OF_FETCH;
/*
* display matching table names
*/
for (count=0;;count++)
{
EXEC SQL FETCH C3 INTO :table_name;
table_name.arr[table_name.len] = '\0';
if (count == 0)
printf("The following table name(s) match the template:\n");
printf("\t%s\n", table_name.arr);
}
END_OF_FETCH:
EXEC SQL WHENEVER NOT FOUND CONTINUE;
EXEC SQL CLOSE C3;
EXEC SQL WHENEVER SQLERROR CONTINUE;
EXEC SQL WHENEVER SQLWARNING CONTINUE;
if (count > 0)
{
answer = asks("\nOk to drop these tables [Yes] ", yesno);
uppstr(yesno);
if (strncmp(yesno, "YES", strlen(yesno)) == 0)
return (drop_tables());
return (FAILURE);
}
SQL_ERROR:
{
EXEC SQL CLOSE C3;
if (sqlca.sqlcode == 0)
{
/* no table names matched template */
return (SUCCESS);
}
show_error("GET TEMPLATE");
return (FAILURE);
}
return (SUCCESS);
/* should never reach here */
}
/*
- create table to occupy largest free extent
*/
create_table()
{
char sql_1[64];
char sql_2[64];
char sql_3[64];
EXEC SQL WHENEVER SQLERROR GOTO SQL_ERROR;
EXEC SQL WHENEVER NOT FOUND CONTINUE;
table_no++;
extent_size = le.blocks * ORA_BLK_SIZE;
sprintf(sql_1, "create table %s%d (afield char(1))", TMP_TABLE, table_no);
sprintf(sql_2, "tablespace %s", tablespace.arr);
sprintf(sql_3, "storage (initial %d)", extent_size);
sprintf(sql_stmt.arr, "%s %s %s", sql_1, sql_2, sql_3); sql_stmt.len = strlen(sql_stmt.arr);
printf("Coalescing extent of %3d fragments, %3d blocks\n",
le.extents, le.blocks);
/*
* create table to use up largest extent
*/
EXEC SQL EXECUTE IMMEDIATE :sql_stmt;
return (SUCCESS);
SQL_ERROR:
{
show_error("CREATE TABLE");
return (FAILURE);
}
}
/*
- drop all temporary tables created by defrag
*/
drop_tables()
{
char sql_1[64];
char sql_2[64];
char sql_3[64];
EXEC SQL WHENEVER SQLERROR GOTO SQL_ERROR;
/*
* build sql statement to select names of 'temporary' tables
*/
strcpy(sql_1, "select table_name from sys.dba_tables"); sprintf(sql_2, "where tablespace_name = '%s'", tablespace.arr); sprintf(sql_3, "and table_name like '%s'", template.arr);
sprintf(sql_stmt.arr, "%s %s %s", sql_1, sql_2, sql_3); sql_stmt.len = strlen(sql_stmt.arr);
/*
* PREPARE, DECLARE, OPEN
*/
EXEC SQL PREPARE S2 FROM :sql_stmt;
EXEC SQL DECLARE C2 CURSOR FOR S2;
EXEC SQL OPEN C2;
EXEC SQL WHENEVER NOT FOUND GOTO END_OF_FETCH;
/*
* drop each table retrieved
*/
for (;;)
{
EXEC SQL FETCH C2 INTO :table_name;
table_name.arr[table_name.len] = '\0';
sprintf(sql_stmt.arr, "drop table %s", table_name.arr);
sql_stmt.len = strlen(sql_stmt.arr);
/*
* drop the table
*/
EXEC SQL EXECUTE IMMEDIATE :sql_stmt;
}
END_OF_FETCH:
EXEC SQL WHENEVER NOT FOUND CONTINUE;
{
EXEC SQL CLOSE C2;
return (SUCCESS);
}
SQL_ERROR:
{
show_error("DROP TABLE");
return (FAILURE);
}
}
/*
- commit if no problems encountered, otherwise rollback
*
- disconnect from database
*
- exit with appropriate status (1 = error, 0 = ok)
*/
void
disconnect(status)
{
int exit_status;
EXEC SQL WHENEVER SQLERROR CONTINUE;
EXEC SQL WHENEVER SQLWARNING CONTINUE;
if (status == FAILURE)
{
EXEC SQL ROLLBACK RELEASE;
if (sqlca.sqlcode != 0)
show_error("ROLLBACK RELEASE");
exit_status = 1;
}
else
{
EXEC SQL COMMIT RELEASE;
if (sqlca.sqlcode != 0)
show_error("COMMIT RELEASE");
exit_status = 0;
}
exit (exit_status);
}
/*
- display prompt and error returned by SQL statement
*/
void
show_error(str)
char *str;
{
printf("%s %-70.70s\n", str, sqlca.sqlerrm.sqlerrmc); }
/*
- print the 'text' on STDOUT and read up to 'len' characters into
- the buffer pointed to by variable from STDIN.
*
- text points to the null terminated string to be printed
- variable points to a buffer of at least 'len'+1 characters
*
- asks returns the number of character read into the string, or a
- -1 if -eof- was encountered
*/
int
asks(text,variable)
char text[];
unsigned char variable[];
{
printf(text);
return ( gets(variable) == (char *)0 ? EOF : strlen(variable) );
}
/*
- convert string, in place, to upper case
*/
void
uppstr(str)
unsigned char *str;
{
unsigned char *p;
for (p=str; *p; p++)
if (islower(*p))
*p = toupper(*p);
} Received on Wed May 05 1993 - 16:44:57 CEST