RE: ORA-1658 even though there is enough contig free (SOLVED)
Ron, Thanks for info...We are aware of it but management doesn't want such change and want to stay as it is. Regards Rafiq Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Date: Wed, 31 Jul 2002 14:41:24 -0800 FYI, you do have the choice of running server partitioned. I'm running 10.7 character with 8.1.7.4 with no problems. Ron Thomas Hypercom, Inc [EMAIL PROTECTED] The problem with some people is that when they aren't drunk, they're sober. --William Butler Yeats. rafiq9857@hotmail .com To: [EMAIL PROTECTED] Sent by: cc: [EMAIL PROTECTED] Subject: RE: ORA-1658 even though there is enough contig free (SOLVED) 07/31/02 03:03 PM Please respond to ORACLE-L Waleed, We are running different versions from 7.3.4.5 to 9i. Ver 7.3.4.5 is for Oracle Financials 10.7 Char and company not yet decided to go for Application 11i so we have to support it for a while and face all such limitations ver 7.3.4. Sorry no choice but to live with it. Regards Rafiq Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Date: Wed, 31 Jul 2002 11:39:50 -0800 Any plans for Oracle upgrade? :) Waleed -Original Message- Sent: Wednesday, July 31, 2002 3:17 PM To: Multiple recipients of list ORACLE-L Rich, Thanks for reply and your explanation. For ver 7.3.4 dba_free_space specify large extent available but when using initial extent of around that size , it never creates initial extent of that size and generate error so I am not relying it and instead using this query. However I am not using any partitioning as 7.3.4 don't have this option Regards Rafiq Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Date: Wed, 31 Jul 2002 10:50:59 -0800 Hi Rafiq, Right, PARALLEL does do that, which is why I had stated in the original post that I had tried the CREATE without PARALLEL after coalescing the TS. In the meantime, I have figured out what the problem is. My problem is on a test system. This test system's MYCOOLTBL happens to be partitioned and the index was being created LOCAL. So, with 4 partitions, the CREATE needed 4 extents of 140MB each. Thus, a single 140MB free extent wasn't enough. Once I fixed the partitioned table, the index created fine in 140MB. Your query, however, still only returns a max value of 36MB, which is not true for me. After quickly looking thru your query, I'd say it has to do with going after max(block_id) in the subquery. The maximum block_id for freespace in this TS probably points to a 36MB extent, but it is not the largest free extent. Use DBA_FREE_SPACE. :) Thanks! Rich Jesse System/Database Administrator [EMAIL PROTECTED] Quad/Tech International, Sussex, WI USA -Original Message- From: Mohammad Rafiq [mailto:[EMAIL PROTECTED]] Sent: Wednesday, July 31, 2002 12:56 PM To: Multiple recipients of list ORACLE-L Subject: RE: ORA-1658 even though there is enough contig free That query give u perfect size in shape of FAT_MB and that's the maximun size of intitial extent... I never relied on DBA_FREE_SPACE Have you coalesed yr tablespace before and after running this query? Besides using parallel 2 , it requires 2 extents of 140M instead of one extent of 140M so u may reduce yr initial extent size to start with 20M for initial and next... If u have any specific question, please let me know Regards Rafiq Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Date: Wed, 31 Jul 2002 09:26:08 -0800 Hmmm...I'm confused. Your query returns the second biggest chunk from DBA_FREE_SPACE on that TS. So, what happened to the 147MB chunk I had? And why is my query against DBA_FREE_SPACE invalid for testing the max size of an initial extent? BTW, this TS only has one datafile. Rich Jesse System/Database Administrator [EMAIL PROTECTED] Quad/Tech International, Sussex, WI USA -Original Message- From: Mohammad Rafiq [mailto:[EMAIL PROTECTED]] Sent: Tuesday, July 30, 2002 5:20 PM To: Multiple recipients of list ORACLE-L Subject: Re: ORA-1658 even though there is enough contig free Rich, 1)Run following script to check fat size which will indicate how big your initial extent may be as you are not finding contigous space in your tablespace. when it ask for value give your tablespace name.. undefine table_space set verify off prompt This script provides a report useful
RE: ORA-1658 even though there is enough contig free (SOLVED)
Hi Rafiq,
Right, PARALLEL does do that, which is why I had stated in the original post
that I had tried the CREATE without PARALLEL after coalescing the TS.
In the meantime, I have figured out what the problem is. My problem is on a
test system. This test system's MYCOOLTBL happens to be partitioned and
the index was being created LOCAL. So, with 4 partitions, the CREATE
needed 4 extents of 140MB each. Thus, a single 140MB free extent wasn't
enough. Once I fixed the partitioned table, the index created fine in
140MB.
Your query, however, still only returns a max value of 36MB, which is not
true for me. After quickly looking thru your query, I'd say it has to do
with going after max(block_id) in the subquery. The maximum block_id for
freespace in this TS probably points to a 36MB extent, but it is not the
largest free extent. Use DBA_FREE_SPACE. :)
Thanks!
Rich Jesse System/Database Administrator
[EMAIL PROTECTED] Quad/Tech International, Sussex, WI USA
-Original Message-
From: Mohammad Rafiq [mailto:[EMAIL PROTECTED]]
Sent: Wednesday, July 31, 2002 12:56 PM
To: Multiple recipients of list ORACLE-L
Subject: RE: ORA-1658 even though there is enough contig free
That query give u perfect size in shape of FAT_MB and that's
the maximun
size of intitial extent... I never relied on DBA_FREE_SPACE
Have you coalesed yr tablespace before and after running this
query? Besides
using parallel 2 , it requires 2 extents of 140M instead of
one extent of
140M so u may reduce yr initial extent size to start with 20M
for initial
and next...
If u have any specific question, please let me know
Regards
Rafiq
Reply-To: [EMAIL PROTECTED]
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
Date: Wed, 31 Jul 2002 09:26:08 -0800
Hmmm...I'm confused. Your query returns the second biggest chunk from
DBA_FREE_SPACE on that TS. So, what happened to the 147MB
chunk I had? And
why is my query against DBA_FREE_SPACE invalid for testing
the max size of
an initial extent?
BTW, this TS only has one datafile.
Rich Jesse System/Database Administrator
[EMAIL PROTECTED] Quad/Tech International,
Sussex, WI USA
-Original Message-
From: Mohammad Rafiq [mailto:[EMAIL PROTECTED]]
Sent: Tuesday, July 30, 2002 5:20 PM
To: Multiple recipients of list ORACLE-L
Subject: Re: ORA-1658 even though there is enough contig free
Rich,
1)Run following script to check fat size which will indicate
how big your
initial extent may be as you are not finding contigous
space in your
tablespace. when it ask for value give your tablespace name..
undefine table_space
set verify off
prompt This script provides a report useful for resizing datafiles
prompt You should perform a 'ALTER TABLESPACE tsname
COALESCE' before
running
prompt this script to ensure you are getting all free space
at end of the
file
accept table_space prompt Enter a tablespace_name or all:
select ddf.file_name,
dfs.file_id,
ddf.blocks,
(ddf.blocks*value)/1024/1024 file_size_mb,
dfs.block_id block_hwm,
ddf.blocks-dfs.block_id fat_blocks,
floor(((ddf.blocks-dfs.block_id)*value)/1024/1024)
fat_mb,
ceil(((ddf.blocks*value)/1024/1024 -
((ddf.blocks-dfs.block_id)*value)/1024/1024)) resize_to
from dba_free_space dfs,
dba_data_files ddf,
v$parameter
where v$parameter.name = 'db_block_size'
and (ddf.tablespace_name = UPPER('table_space')
or 'ALL' = UPPER('table_space'))
and dfs.tablespace_name = ddf.tablespace_name
and dfs.file_id = ddf.file_id
and dfs.block_id = (select max(block_id)
from dba_free_space
where file_id = dfs.file_id)
order by fat_blocks desc;
/
2) Coalesce your tablespace and run this script again..
3) or reduce yr initial extent size and don't use parallel clause
HTH,
Regards
Rafiq
Reply-To: [EMAIL PROTECTED]
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
Date: Tue, 30 Jul 2002 13:18:28 -0800
Hi all,
On 8.1.7.2.0 on HP/UX 11.0, I need to create an index online.
So, I see how
much space I have:
select max(bytes/1024/1024)
from dba_free_space
where tablespace_name = 'MY_IDX_TS';
...and it returns 147.3475. So I create my index:
CREATE INDEX myschema.mycoolidx
ON myschema.mycooltbl(mycoolcol) PCTFREE 15
STORAGE(
INITIAL 140 M
NEXT 30 M
)
NOLOGGING ONLINE TABLESPACE my_idx_ts PARALLEL 2;
But I keep getting ORA-01658 unable to create initial extent
in MY_IDX_TS,
even though there is ample room. I thought it was because I
had originally
tried it with PARALLEL 2 and there was
RE: ORA-1658 even though there is enough contig free (SOLVED)
Rich,
Thanks for reply and your explanation. For ver 7.3.4 dba_free_space specify
large extent available but when using initial extent of around that size ,
it never creates initial extent of that size and generate error so I am not
relying it and instead using this query. However I am not using any
partitioning as 7.3.4 don't have this option
Regards
Rafiq
Reply-To: [EMAIL PROTECTED]
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
Date: Wed, 31 Jul 2002 10:50:59 -0800
Hi Rafiq,
Right, PARALLEL does do that, which is why I had stated in the original post
that I had tried the CREATE without PARALLEL after coalescing the TS.
In the meantime, I have figured out what the problem is. My problem is on a
test system. This test system's MYCOOLTBL happens to be partitioned and
the index was being created LOCAL. So, with 4 partitions, the CREATE
needed 4 extents of 140MB each. Thus, a single 140MB free extent wasn't
enough. Once I fixed the partitioned table, the index created fine in
140MB.
Your query, however, still only returns a max value of 36MB, which is not
true for me. After quickly looking thru your query, I'd say it has to do
with going after max(block_id) in the subquery. The maximum block_id for
freespace in this TS probably points to a 36MB extent, but it is not the
largest free extent. Use DBA_FREE_SPACE. :)
Thanks!
Rich Jesse System/Database Administrator
[EMAIL PROTECTED] Quad/Tech International, Sussex, WI USA
-Original Message-
From: Mohammad Rafiq [mailto:[EMAIL PROTECTED]]
Sent: Wednesday, July 31, 2002 12:56 PM
To: Multiple recipients of list ORACLE-L
Subject: RE: ORA-1658 even though there is enough contig free
That query give u perfect size in shape of FAT_MB and that's
the maximun
size of intitial extent... I never relied on DBA_FREE_SPACE
Have you coalesed yr tablespace before and after running this
query? Besides
using parallel 2 , it requires 2 extents of 140M instead of
one extent of
140M so u may reduce yr initial extent size to start with 20M
for initial
and next...
If u have any specific question, please let me know
Regards
Rafiq
Reply-To: [EMAIL PROTECTED]
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
Date: Wed, 31 Jul 2002 09:26:08 -0800
Hmmm...I'm confused. Your query returns the second biggest chunk from
DBA_FREE_SPACE on that TS. So, what happened to the 147MB
chunk I had? And
why is my query against DBA_FREE_SPACE invalid for testing
the max size of
an initial extent?
BTW, this TS only has one datafile.
Rich Jesse System/Database Administrator
[EMAIL PROTECTED] Quad/Tech International,
Sussex, WI USA
-Original Message-
From: Mohammad Rafiq [mailto:[EMAIL PROTECTED]]
Sent: Tuesday, July 30, 2002 5:20 PM
To: Multiple recipients of list ORACLE-L
Subject: Re: ORA-1658 even though there is enough contig free
Rich,
1)Run following script to check fat size which will indicate
how big your
initial extent may be as you are not finding contigous
space in your
tablespace. when it ask for value give your tablespace name..
undefine table_space
set verify off
prompt This script provides a report useful for resizing datafiles
prompt You should perform a 'ALTER TABLESPACE tsname
COALESCE' before
running
prompt this script to ensure you are getting all free space
at end of the
file
accept table_space prompt Enter a tablespace_name or all:
select ddf.file_name,
dfs.file_id,
ddf.blocks,
(ddf.blocks*value)/1024/1024 file_size_mb,
dfs.block_id block_hwm,
ddf.blocks-dfs.block_id fat_blocks,
floor(((ddf.blocks-dfs.block_id)*value)/1024/1024)
fat_mb,
ceil(((ddf.blocks*value)/1024/1024 -
((ddf.blocks-dfs.block_id)*value)/1024/1024)) resize_to
from dba_free_space dfs,
dba_data_files ddf,
v$parameter
where v$parameter.name = 'db_block_size'
and (ddf.tablespace_name = UPPER('table_space')
or 'ALL' = UPPER('table_space'))
and dfs.tablespace_name = ddf.tablespace_name
and dfs.file_id = ddf.file_id
and dfs.block_id = (select max(block_id)
from dba_free_space
where file_id = dfs.file_id)
order by fat_blocks desc;
/
2) Coalesce your tablespace and run this script again..
3) or reduce yr initial extent size and don't use parallel clause
HTH,
Regards
Rafiq
Reply-To: [EMAIL PROTECTED]
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
Date: Tue, 30 Jul 2002 13:18:28 -0800
Hi all,
On 8.1.7.2.0 on HP/UX 11.0, I need to create an index online.
So, I see how
much space I have:
RE: ORA-1658 even though there is enough contig free (SOLVED)
Any plans for Oracle upgrade? :)
Waleed
-Original Message-
Sent: Wednesday, July 31, 2002 3:17 PM
To: Multiple recipients of list ORACLE-L
Rich,
Thanks for reply and your explanation. For ver 7.3.4 dba_free_space specify
large extent available but when using initial extent of around that size ,
it never creates initial extent of that size and generate error so I am not
relying it and instead using this query. However I am not using any
partitioning as 7.3.4 don't have this option
Regards
Rafiq
Reply-To: [EMAIL PROTECTED]
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
Date: Wed, 31 Jul 2002 10:50:59 -0800
Hi Rafiq,
Right, PARALLEL does do that, which is why I had stated in the original post
that I had tried the CREATE without PARALLEL after coalescing the TS.
In the meantime, I have figured out what the problem is. My problem is on a
test system. This test system's MYCOOLTBL happens to be partitioned and
the index was being created LOCAL. So, with 4 partitions, the CREATE
needed 4 extents of 140MB each. Thus, a single 140MB free extent wasn't
enough. Once I fixed the partitioned table, the index created fine in
140MB.
Your query, however, still only returns a max value of 36MB, which is not
true for me. After quickly looking thru your query, I'd say it has to do
with going after max(block_id) in the subquery. The maximum block_id for
freespace in this TS probably points to a 36MB extent, but it is not the
largest free extent. Use DBA_FREE_SPACE. :)
Thanks!
Rich Jesse System/Database Administrator
[EMAIL PROTECTED] Quad/Tech International, Sussex, WI USA
-Original Message-
From: Mohammad Rafiq [mailto:[EMAIL PROTECTED]]
Sent: Wednesday, July 31, 2002 12:56 PM
To: Multiple recipients of list ORACLE-L
Subject: RE: ORA-1658 even though there is enough contig free
That query give u perfect size in shape of FAT_MB and that's
the maximun
size of intitial extent... I never relied on DBA_FREE_SPACE
Have you coalesed yr tablespace before and after running this
query? Besides
using parallel 2 , it requires 2 extents of 140M instead of
one extent of
140M so u may reduce yr initial extent size to start with 20M
for initial
and next...
If u have any specific question, please let me know
Regards
Rafiq
Reply-To: [EMAIL PROTECTED]
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
Date: Wed, 31 Jul 2002 09:26:08 -0800
Hmmm...I'm confused. Your query returns the second biggest chunk from
DBA_FREE_SPACE on that TS. So, what happened to the 147MB
chunk I had? And
why is my query against DBA_FREE_SPACE invalid for testing
the max size of
an initial extent?
BTW, this TS only has one datafile.
Rich Jesse System/Database Administrator
[EMAIL PROTECTED] Quad/Tech International,
Sussex, WI USA
-Original Message-
From: Mohammad Rafiq [mailto:[EMAIL PROTECTED]]
Sent: Tuesday, July 30, 2002 5:20 PM
To: Multiple recipients of list ORACLE-L
Subject: Re: ORA-1658 even though there is enough contig free
Rich,
1)Run following script to check fat size which will indicate
how big your
initial extent may be as you are not finding contigous
space in your
tablespace. when it ask for value give your tablespace name..
undefine table_space
set verify off
prompt This script provides a report useful for resizing datafiles
prompt You should perform a 'ALTER TABLESPACE tsname
COALESCE' before
running
prompt this script to ensure you are getting all free space
at end of the
file
accept table_space prompt Enter a tablespace_name or all:
select ddf.file_name,
dfs.file_id,
ddf.blocks,
(ddf.blocks*value)/1024/1024 file_size_mb,
dfs.block_id block_hwm,
ddf.blocks-dfs.block_id fat_blocks,
floor(((ddf.blocks-dfs.block_id)*value)/1024/1024)
fat_mb,
ceil(((ddf.blocks*value)/1024/1024 -
((ddf.blocks-dfs.block_id)*value)/1024/1024)) resize_to
from dba_free_space dfs,
dba_data_files ddf,
v$parameter
where v$parameter.name = 'db_block_size'
and (ddf.tablespace_name = UPPER('table_space')
or 'ALL' = UPPER('table_space'))
and dfs.tablespace_name = ddf.tablespace_name
and dfs.file_id = ddf.file_id
and dfs.block_id = (select max(block_id)
from dba_free_space
where file_id = dfs.file_id)
order by fat_blocks desc;
/
2) Coalesce your tablespace and run this script again..
3) or reduce yr initial extent size and don't use parallel clause
HTH,
Regards
Rafiq
Reply-To: [EMAIL PROTECTED]
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
Date: Tue, 30
RE: ORA-1658 even though there is enough contig free (SOLVED)
Waleed,
We are running different versions from 7.3.4.5 to 9i. Ver 7.3.4.5 is for
Oracle Financials 10.7 Char and company not yet decided to go for
Application 11i so we have to support it for a while and face all such
limitations ver 7.3.4.
Sorry no choice but to live with it.
Regards
Rafiq
Reply-To: [EMAIL PROTECTED]
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
Date: Wed, 31 Jul 2002 11:39:50 -0800
Any plans for Oracle upgrade? :)
Waleed
-Original Message-
Sent: Wednesday, July 31, 2002 3:17 PM
To: Multiple recipients of list ORACLE-L
Rich,
Thanks for reply and your explanation. For ver 7.3.4 dba_free_space specify
large extent available but when using initial extent of around that size ,
it never creates initial extent of that size and generate error so I am not
relying it and instead using this query. However I am not using any
partitioning as 7.3.4 don't have this option
Regards
Rafiq
Reply-To: [EMAIL PROTECTED]
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
Date: Wed, 31 Jul 2002 10:50:59 -0800
Hi Rafiq,
Right, PARALLEL does do that, which is why I had stated in the original post
that I had tried the CREATE without PARALLEL after coalescing the TS.
In the meantime, I have figured out what the problem is. My problem is on a
test system. This test system's MYCOOLTBL happens to be partitioned and
the index was being created LOCAL. So, with 4 partitions, the CREATE
needed 4 extents of 140MB each. Thus, a single 140MB free extent wasn't
enough. Once I fixed the partitioned table, the index created fine in
140MB.
Your query, however, still only returns a max value of 36MB, which is not
true for me. After quickly looking thru your query, I'd say it has to do
with going after max(block_id) in the subquery. The maximum block_id for
freespace in this TS probably points to a 36MB extent, but it is not the
largest free extent. Use DBA_FREE_SPACE. :)
Thanks!
Rich Jesse System/Database Administrator
[EMAIL PROTECTED] Quad/Tech International, Sussex, WI USA
-Original Message-
From: Mohammad Rafiq [mailto:[EMAIL PROTECTED]]
Sent: Wednesday, July 31, 2002 12:56 PM
To: Multiple recipients of list ORACLE-L
Subject: RE: ORA-1658 even though there is enough contig free
That query give u perfect size in shape of FAT_MB and that's
the maximun
size of intitial extent... I never relied on DBA_FREE_SPACE
Have you coalesed yr tablespace before and after running this
query? Besides
using parallel 2 , it requires 2 extents of 140M instead of
one extent of
140M so u may reduce yr initial extent size to start with 20M
for initial
and next...
If u have any specific question, please let me know
Regards
Rafiq
Reply-To: [EMAIL PROTECTED]
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
Date: Wed, 31 Jul 2002 09:26:08 -0800
Hmmm...I'm confused. Your query returns the second biggest chunk from
DBA_FREE_SPACE on that TS. So, what happened to the 147MB
chunk I had? And
why is my query against DBA_FREE_SPACE invalid for testing
the max size of
an initial extent?
BTW, this TS only has one datafile.
Rich Jesse System/Database Administrator
[EMAIL PROTECTED] Quad/Tech International,
Sussex, WI USA
-Original Message-
From: Mohammad Rafiq [mailto:[EMAIL PROTECTED]]
Sent: Tuesday, July 30, 2002 5:20 PM
To: Multiple recipients of list ORACLE-L
Subject: Re: ORA-1658 even though there is enough contig free
Rich,
1)Run following script to check fat size which will indicate
how big your
initial extent may be as you are not finding contigous
space in your
tablespace. when it ask for value give your tablespace name..
undefine table_space
set verify off
prompt This script provides a report useful for resizing datafiles
prompt You should perform a 'ALTER TABLESPACE tsname
COALESCE' before
running
prompt this script to ensure you are getting all free space
at end of the
file
accept table_space prompt Enter a tablespace_name or all:
select ddf.file_name,
dfs.file_id,
ddf.blocks,
(ddf.blocks*value)/1024/1024 file_size_mb,
dfs.block_id block_hwm,
ddf.blocks-dfs.block_id fat_blocks,
floor(((ddf.blocks-dfs.block_id)*value)/1024/1024)
fat_mb,
ceil(((ddf.blocks*value)/1024/1024 -
((ddf.blocks-dfs.block_id)*value)/1024/1024)) resize_to
from dba_free_space dfs,
dba_data_files ddf,
v$parameter
where v$parameter.name = 'db_block_size'
and (ddf.tablespace_name = UPPER('table_space')
or 'ALL' = UPPER('table_space'))
and dfs.tablespace_name = ddf.tablespace_name
and
RE: ORA-1658 even though there is enough contig free (SOLVED)
FYI, you do have the choice of running server partitioned. I'm running 10.7 character with 8.1.7.4 with no problems. Ron Thomas Hypercom, Inc [EMAIL PROTECTED] The problem with some people is that when they aren't drunk, they're sober. --William Butler Yeats. rafiq9857@hotmail .com To: [EMAIL PROTECTED] Sent by: cc: [EMAIL PROTECTED] Subject: RE: ORA-1658 even though there is enough contig free (SOLVED) 07/31/02 03:03 PM Please respond to ORACLE-L Waleed, We are running different versions from 7.3.4.5 to 9i. Ver 7.3.4.5 is for Oracle Financials 10.7 Char and company not yet decided to go for Application 11i so we have to support it for a while and face all such limitations ver 7.3.4. Sorry no choice but to live with it. Regards Rafiq Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Date: Wed, 31 Jul 2002 11:39:50 -0800 Any plans for Oracle upgrade? :) Waleed -Original Message- Sent: Wednesday, July 31, 2002 3:17 PM To: Multiple recipients of list ORACLE-L Rich, Thanks for reply and your explanation. For ver 7.3.4 dba_free_space specify large extent available but when using initial extent of around that size , it never creates initial extent of that size and generate error so I am not relying it and instead using this query. However I am not using any partitioning as 7.3.4 don't have this option Regards Rafiq Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Date: Wed, 31 Jul 2002 10:50:59 -0800 Hi Rafiq, Right, PARALLEL does do that, which is why I had stated in the original post that I had tried the CREATE without PARALLEL after coalescing the TS. In the meantime, I have figured out what the problem is. My problem is on a test system. This test system's MYCOOLTBL happens to be partitioned and the index was being created LOCAL. So, with 4 partitions, the CREATE needed 4 extents of 140MB each. Thus, a single 140MB free extent wasn't enough. Once I fixed the partitioned table, the index created fine in 140MB. Your query, however, still only returns a max value of 36MB, which is not true for me. After quickly looking thru your query, I'd say it has to do with going after max(block_id) in the subquery. The maximum block_id for freespace in this TS probably points to a 36MB extent, but it is not the largest free extent. Use DBA_FREE_SPACE. :) Thanks! Rich Jesse System/Database Administrator [EMAIL PROTECTED] Quad/Tech International, Sussex, WI USA -Original Message- From: Mohammad Rafiq [mailto:[EMAIL PROTECTED]] Sent: Wednesday, July 31, 2002 12:56 PM To: Multiple recipients of list ORACLE-L Subject: RE: ORA-1658 even though there is enough contig free That query give u perfect size in shape of FAT_MB and that's the maximun size of intitial extent... I never relied on DBA_FREE_SPACE Have you coalesed yr tablespace before and after running this query? Besides using parallel 2 , it requires 2 extents of 140M instead of one extent of 140M so u may reduce yr initial extent size to start with 20M for initial and next... If u have any specific question, please let me know Regards Rafiq Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Date: Wed, 31 Jul 2002 09:26:08 -0800 Hmmm...I'm confused. Your query returns the second biggest chunk from DBA_FREE_SPACE on that TS
