Re: Tablespace-Datafiles unused space ?

"Stefan Leidinger" <stefan.leidinger_at_aon.at> schrieb im Newsbeitrag news:3f0a83fc$0$18666$91cee783_at_newsreader01.highway.telekom.at...
> Hi,
>
> I have a problem with the datafiles of an Oracle-tablespace:
>
> There are three datafiles, if I want to create an index with 200MB
> it will be created in the 3rd datafile. I think the 1st datafile is very
> fragmented ...
>
> How can I compress or defragment the datafiles? (V 8.1.7)
>
> Example: www.schwarzmueller.com/tablespace.jpg
>
> Every answer is welcome,
>
> Greetings from Austria
>
> Stefan
>
>

Hi, Stefan (Grüss Gott),

according to your jpg (You use dba studio ...), the first dbf is rather small compared to the other ones. Your new index is _likely_ to be built in the third file.

If You are afraid of fragmentation, check out first:

• are You still using DMTs or LMTs ? -> dba_tablespaces, dba_freespace
• are You having many segments of different sizes (DMTs) -> dba_segments, dba_tables, dba_indexes

and if necessary, You can defragment table with ALTER TABLE ... MOVE or indexes with ALTER INDEX ... REBUILD. And there are some splendid articles out there how to avoid fragmentation in the first place ...

I assume that You use Version 8i or higher ;-).

hth, Jan Received on Tue Jul 08 2003 - 05:11:05 CDT