Re: extent allocation?

In <1992Oct28.165739.20340_at_gdstech.grumman.com>, un_at_gdstech.grumman.com (Un Fu):
> Looked into the dba_free_space and saw many blocks of smaller sizes. ...
> Also, is there a quick way to consolidate the memory without backup
> of the database?

And in <1992Oct31.103337.1_at_us.oracle.com>, sstephen_at_us.oracle.com (Scott Stephens):
> ... I had to "de-fragment" my tablespace into 1 large extent
> again, before I started importing. To do this, (assuming that my tablespace
> was one datafile, exactly 250000 blocks.
>
> CREATE TABLE BIG_TEMP STORAGE (INITIAL 250000 MINEXTENT 1);
> -- table created
> DROP TABLE BIG_TEMP;
>
> et voile, my tablespace's extents were re-combined into one big extent, and
> my import would always succeed.

RTSS Bulletin 100954.708 includes a dynamite script, CONTIG_FREE_SPACE.sql, that reports contiguous free space. The stragegy is the same as Scott describes above. Create objects of the contiguous sizes reported, then drop them. It typically takes me about an hour to coalesce 600 fragments into 100--far less time consuming than exporting all objects in a tablespace simply to defrag free space.

+-----------------------------------------------------------------------+
| Jean Anderson, DBA                       email:  jean_at_esosun.css.gov  |
| SAIC Open Systems Division, MS A2-F                                   |
| 10210 Campus Point Drive                 phone:  (619)458-2727        |
| San Diego, CA  92121                       fax:  (619)458-4993        |
+-----------------------------------------------------------------------+