Re: Help! Oracle problem or my problem?

Once you have unloaded all your data and dropped the corresponding objects (select * from user_objects) such as tables and indices, you need to create a couple "dummy" tables to coalesce all that free space into one large extent.

Oracle, upon object creation, searches for a "exact fit" extent size first, then a "best (next largest) fit" and THEN looks to coalesce contiguous extents together. If your exact fit on some previous object happens to match the size of a free extent in the middle of your free space, then you have split your free space in half.

-Andy

un_at_gdstech.grumman.com (Un Fu) writes:
>Help! I don't know whether this is an Oracle problem or
>my problem. The problem is this: I enlarged our database
>by adding another 10MB data_file to it. Our project requires
>frequent creating and deleting of sets of tables and views.
>After few loading and unloading of our database tables/views
>I see (from dba_free_space) that our database is all fragmented
>into small size of extents. Eventhough I dropped all the
>tables/views I created, the 10MB I added is not reclaimed
>into one extent but into many small size extents right next
>to each other. Later on when I needed an extent of a bigger
>size, Oracle will simply say there is none. Is this correct
>Oracle behavior? I am running an Oracle RDBMS of v6.0.
 

>Any help or info is greatly appreciated!
>--
>****************************************************************************
>Un Un Fu Internet: un_at_gdstech.grumman.com
>Disclaimer: I am solely responsible for what I am saying here!
>****************************************************************************

-- 
Andrew Finkenstadt, Vista-Chrome, Inc., Homes & Land Publishing Corporation
GEnie Unix RoundTable Manager, andy_at_vistachrome.com, andy_at_genie.geis.com.
  Send mail to ora-request_at_vistachrome.com to join Unix, CASE, and 
  Desktop Oracle RDBMS Database discussions.