Re: Tablespace problem

Steven Rorabaugh (dogbert_at_lamar.ColoState.EDU) wrote:
: We are experiencing problems with ORACLE version 6. The database is very
: fragmented and attempts to defragment, such as running 'GEN_CREATES' have not
: been successful. Could anyone please explain to me what ORACLE version 6
: really does when trying to create a table. For example: A database that has
: 11,145 blocks of free space (not continuous), and a user tries to create a
: table of 6,000 blocks. ORACLE displays an error 'ORA-01547: FAILED TO
: ALLOCATE EXTENT OF SIZE 6,000 IN TABLESPACE 'tablespace_name''. According to
: the manuals, ORACLE will go out and get non-contiguous free space if one
: continuous free space is not available. That does not seem to be what is
: happening. We have exported, recreated the tables, and imported the data, but
: it does not decrease the fragmentation. It only eliminated the various
: extents. We tried to drop the tablespace but ran into other problems with
: that process, and therefore we were not able to drop it (ORACLE is checking
: into that problem).
:
: I would appreciate any comments or suggestions that anyone might have
: regarding this problem.
:
: Thank you,
:
: Shari Bishop
: Database Analyst
 

: (Direct replies to sbishop_at_tasc.usda.gov)

1. When oracle tries to create a table it looks for a continuous space equal to size of initial extent. use: select max(blocks) from sys.dba_free_space where tablespace_name = your_table_space_name;
2. Export the table (with COMPRESS EXTENTS to Y, which is the default). Drop the tables and import the tables from the export file. ( don't recreate the tables from DDL scripts, unless you use proper initial extent.). The best way is : to find proper INITIAL extent , NEXT extent, PCTINCREASE parameter for the tables. And use these parameters in your DDL scripts to create your tables.

I hope it helps..

Srini Kunamneni
Consulant Received on Mon Jul 22 1996 - 00:00:00 CEST