| ora-01659 [message #283286] |
Mon, 26 November 2007 10:41  |
varu123
Messages: 754
Registered: October 2007
|
Senior Member |
|
|
I have to create four indexes on three tables.
I am getting ora-01659 error:unable to extend minextents beyond 8 in tablespace rpli.
I checked in dba_segmnets
SQL>select bytes/1024/1024 from dba_tablespaces where tablespace_name='RPLI';
SQL>6324.4376
that means the tablespace has enough free space.i tried
alter tablespace rpli coalesce;
BUt the same error.
I add a datafile of 300m but got the same error sometimes beyond 6 or 8.
The minextents of the tablespace is 10.
Don't know what is taking up space.
HOw can i know exactly how much space is used and is free in the tablespace?
|
|
|
|
|
|
| Re: ora-01659 [message #283295 is a reply to message #283286] |
Mon, 26 November 2007 10:54  |
varu123
Messages: 754
Registered: October 2007
|
Senior Member |
|
|
yes Michel after reading this in ora-code.com,i did:
alter tablespace coalesce but got the same error.
Do you have any query to know exact space used and free in the tablespace and to know how much contiguous space it has?
| Quote: |
or retry with smaller value for MINEXTENTS, NEXT or PCTINCREASE
|
Would altering tablespace and decreasing storage parameters help?
[Updated on: Mon, 26 November 2007 10:58] Report message to a moderator |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| Re: ora-01659 [message #283313 is a reply to message #283286] |
Mon, 26 November 2007 12:17 |
varu123
Messages: 754
Registered: October 2007
|
Senior Member |
|
|
So i looked around and got this
http://www.jlcomp.demon.co.uk/faq/freespace.html
and this is the query
select tablespace_name, to_char(sum(bytes)/1048576, '99,999,990.99') as MB_FREE,
count(*) as FRAGMENTS, to_char(max(bytes)/1048576, '99,999,990.99') as BIGGEST_BIT
from dba_free_space
group by tablespace_name;
how does count(*) give no. of fragmnets?
What is biggest bit?
|
|
|
|
|
|
|
|
|
|
| Re: ora-01659 [message #283318 is a reply to message #283286] |
Mon, 26 November 2007 12:44 |
joy_division
Messages: 4963
Registered: February 2005
Location: East Coast USA
|
Senior Member |
|
|
| varu123 wrote on Mon, 26 November 2007 11:41 |
I checked in dba_segmnets
SQL>select bytes/1024/1024 from dba_tablespaces where tablespace_name='RPLI';
SQL>6324.4376
that means the tablespace has enough free space.i tried
|
No it doesn't. That shows how much physical space the tablespace RPLI is using on the filesystem.
And I think if Jonathan Lewis died we would know about it.
And did you really ask for someone else's username and password?
|
|
|
|
| Re: ora-01659 [message #283319 is a reply to message #283286] |
Mon, 26 November 2007 13:01 |
varu123
Messages: 754
Registered: October 2007
|
Senior Member |
|
|
Yeah not dba_tablespaces but i meant dba_free_space.
and that blog is not updated since september 2006.
Coming back to the problem,
How to decrease/increase storage parameters in tablespace.
alter tablespace rpli offline;
alter tablespace rpli storage(min_extents 20);
invalid alter tablespace statement
Whats the syntax?
[Updated on: Mon, 26 November 2007 13:08] Report message to a moderator |
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
