-
Hello all,
I have a tablespace for index with 25G.
This tablespace has got four files. That is G:\indx\indx1.dbf, indx2.dbf, etc ...
I ran a query to see the free space i have around 2G of free space dispersed in three different files (indx1.dbf 731Mb, indx2.dbf 780mb, indx3.dbf 711mb).
But this table space is fragmented like anything.
So i did a coalesce to defragment it.
When i run again the query to see the fragmented tablespaces i always see my index tablespace.
I tried to create an index in this tablespace it says it dosent have much place to extend.
Can some one explain me how oracle manages the tablespace.
Thanks
Naeem
-
coalesce will not completely defragment, it will join adjacent chunks of free space.
just because you have 2GB of free space, doesn't mean it is in one big piece. You need to find out how big your pieces are. If a segment needs to exetend by say 10Mb and a 10mb piece of freespace cannot be found then you will get error.
To overcome, defragment via export/import.
change tablespace to LMT.
This script will help you find out how much freespace, how many pieces and biggest/smallest chunk.
prompt Listing Available Free Space In Tablespaces/Fragmentation
prompt ---------------------------------------------------------
pause
select substr(f.tablespace_name,1,40) "Tablespace Name",
to_char(d.bytes/1024/1024,'9999.99')||' MB' Total,
to_char(sum(f.bytes/1024/1024),'9999.99')||' MB' Free,
to_char(round(max(f.bytes/1024/1024),2),'9999.99')||' MB' Biggest_Chunk,
to_char(round(min(f.bytes/1024/1024),2),'9999.99')||' MB' Smallest_Chunk,
count(f.bytes/1024/1024) Pieces
from dba_free_space f,
dba_data_files d
where f.file_id = d.file_id
group by f.tablespace_name,
to_char(d.bytes/1024/1024,'9999.99');
-
Thanks Suresh,
I have Biggest pices is 703.50MB and my smallest on is 10.88MB.
and i have 6 pieces.
I am creating an index with an initial extend of 100MB.
Since it has 703.50MB piece availabe, i don't understand why it is giving me an error.
When I do export do i have to say compress=y.
does this makes any difference?
naeem
-
don't think you need to defragment with those stats.
Check other index sizes on that table to determine size of index that will be created.
what is your next extent size ?
are you sure you are specifying correct tablespace to store index when creating ?
-
My next extent size is 100MB
I am sure that i am specifying correct tablespace to store index when creating, because i am getting the folowing error
ORA-01652: unable to extend temp segment by 16 in tablespace INDX"
I am checking the size of the other indexes.
Thanks for your help.
-
To avoid fragmentation I recomand you to use localy managed tablespace with an unique extent size (CREATE TABLESPACE ...EXTENT MANAGEMENT LOCAL UNIFORM SIZE ???K).
In addition to that you could create a TBS for small indexes with an exstent size of 512k and one for large indexes with an extent size of 20M (this is only an example).
Instead of an export you could also rebuild the index to defragment it(ALTER INDEX idx_name REBUILD TABLESPACE tbs_name STORAGE(INITIAL 10M NEXT 10M)
Could please give the exacte error number and text you get?
-
To me it seems that your index becomes very large.
How many rows has your table?
How many columns are you using for the index?
What is the average length of these columns?
Where do you specify the next extent size?
Could you give the statment you are executing?
-
Mike,
here are the answers for your questions.
How many rows has your table?
90 565 373
This table is partioned by range.
How many columns are you using for the index?
One column
What is the average length of these columns?
10b
Where do you specify the next extent size?
i don't understant your question
Could you give the statment you are executing?
ALTER INDEX inhis_hicod_hitxt REBUILD
COMPUTE STATISTICS
-
sorry i didn't see that you want to rebuild your index, I was thinking you want to create it.
Could you give the number of blocks used by this index.
You need to execute an
ANALYZE INDEX inhis_hicod_hitxt VALIDATE STRUCTURE
and then have a look at the INDEX_STATS view and get the number of leaf blocks in the B-Tree (LF_BLKS) and the number of branch blocks (BR_BLKS).
Which is the default storage for this tablespace (view DBA_TABLESPACES: INITIAL_EXTENT, NEXT_EXTENT, PCT_INCREASE) ?
And one more question: in your first message you say that you have a tablespace with 25GB based on four files. Is 25GB correct isn't it 2.5GB?
>>Where do you specify the next extent size?
>i don't understant your question
Your error message says that it can not allocate 16 blocks which means from my point of view that you are using extents of 16*block_size which is equal to 128k (if you are using 8k db blocks). With the statement below you would specifiy the extent size:
ALTER INDEX idx_pk_dept
REBUILD
TABLESPACE idx
STORAGE(INITIAL 100M NEXT 100M);
