A lot of extents in temp tables

Hi everyone, I have many of tables with high extents what can I do to fix it? Follow attached print ox tables and my dbspaces (onstat -d)

Leandro: You can defragment tables and indexes most easily using the "defragment" SQL API function in the sysadmin database. For tables: execute function task( 'defragment', 'database:owner.table' );For indexes (or to defragment a single partition of a partitioned table): execute function task( 'defragment partition', partnum );You can find the partnum for table partitions and for indexes (and fragments of partitioned indexes) in the sysmaster:systabnames table. Art ------------------------------ Art S. Kagel, President and Principal Consultant ASK Database Management Corp. www.askdbmgt.com ------------------------------

Hey Art, thanks. So, look at this error when perform defrag: Failed to defrag the partition 4194355 - ISAM error 21539

Here's the error message for that error: -21539 Defragment: You can't run this command on catalog/pseudo/temp tables ACTION Run defragment on a regular table/partition So, did you run this against a true temp table, or a catalog table, e.g. usually a table beginning with "sys" or a table in sysmaster? If these are truly temporary tables, then are they always around? The next time the temp table is created, consider specifying a first and next extent size so that it preallocates more space when created, e.g.: create temp table mwtesttmp(a serial, b char(10)) extent size 10000 next size 1000;This will create the table initially with 10,000 KB of space, and when that fills, then it will grow by 1,000 KB each time. ------------------------------ Mike Walker xDB Systems, Inc www.xdbsystems.com ------------------------------

Humm ok great But, this table is part of a partner's ERP system over which I have no control. Is it possible to specify this space at the instance level?

No, there isn't. Is the large number of extents causing a problem? ------------------------------ Mike Walker xDB Systems, Inc www.xdbsystems.com ------------------------------

Leandro: I didn't realize that you were only concerned about the temp tables. There isn't much you can do if you do not have access to the application code. However, unless you do not have sufficient cache configured a temp table with many extents should not be a problem. In the current releases of the engine, the limit on the number of extents is over 32000 so with extent size doubling that is virtually unlimited. It should also not be a performance issue because if you have sufficient cache configured, those large temp tables should reside completely in memory and not actually flush to disk. ------------------------------ Art S. Kagel, President and Principal Consultant ASK Database Management Corp. www.askdbmgt.com ------------------------------

I understand better now. It definitely won't be a problem. Thank you both!