Home » RDBMS Server » Server Administration » ora-01659
ora-01659 [message #283286] Mon, 26 November 2007 10:41 Go to next message
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 #283290 is a reply to message #283286] Mon, 26 November 2007 10:46 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:

ORA-01659: unable to allocate MINEXTENTS beyond string in tablespace string
Cause: Failed to find sufficient contiguous space to allocate MINEXTENTS for the segment being created.
Action: Use ALTER TABLESPACE ADD DATAFILE to add additional space to the tablespace or retry with smaller value for MINEXTENTS, NEXT or PCTINCREASE

Regards
Michel
Re: ora-01659 [message #283295 is a reply to message #283286] Mon, 26 November 2007 10:54 Go to previous messageGo to next message
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 #283300 is a reply to message #283295] Mon, 26 November 2007 10:58 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:

Do you have any query to know exact space used and free in the tablespace and to know how much contiguous space it has?

http://www.dba-village.com/village/dvp_scripts.ScriptDetails?ScriptIdA=1510

Regards
Michel

[Updated on: Mon, 26 November 2007 10:59]

Report message to a moderator

Re: ora-01659 [message #283304 is a reply to message #283286] Mon, 26 November 2007 11:04 Go to previous messageGo to next message
varu123
Messages: 754
Registered: October 2007
Senior Member

I am not a member of that forum
Any other link?
Re: ora-01659 [message #283305 is a reply to message #283304] Mon, 26 November 2007 11:07 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Just register, it is free.

Regards
Michel
Re: ora-01659 [message #283306 is a reply to message #283286] Mon, 26 November 2007 11:17 Go to previous messageGo to next message
varu123
Messages: 754
Registered: October 2007
Senior Member
Give me your username and password Razz
Re: ora-01659 [message #283308 is a reply to message #283306] Mon, 26 November 2007 11:26 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Are you saying you are unable to register?

Regards
Michel
Re: ora-01659 [message #283311 is a reply to message #283286] Mon, 26 November 2007 11:55 Go to previous messageGo to next message
varu123
Messages: 754
Registered: October 2007
Senior Member
I dont have time to register and wait for there confirmation mails for just one query.
BUt i did see your name in the top ten DBAs.
Thats cool Cool
Re: ora-01659 [message #283313 is a reply to message #283286] Mon, 26 November 2007 12:17 Go to previous messageGo to next message
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 #283314 is a reply to message #283313] Mon, 26 November 2007 12:24 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Ask Jonathan Lewis.

Regards
Michel
Re: ora-01659 [message #283316 is a reply to message #283286] Mon, 26 November 2007 12:31 Go to previous messageGo to next message
varu123
Messages: 754
Registered: October 2007
Senior Member
Quote:

Ask Jonathan Lewis

He is dead.. so please tell
Re: ora-01659 [message #283317 is a reply to message #283316] Mon, 26 November 2007 12:44 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Dead? Surely a ghost that writes his blog:
http://jonathanlewis.wordpress.com/

Regards
Michel
Re: ora-01659 [message #283318 is a reply to message #283286] Mon, 26 November 2007 12:44 Go to previous messageGo to next message
joy_division
Messages: 4640
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 Go to previous messageGo to next message
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

Re: ora-01659 [message #283320 is a reply to message #283319] Mon, 26 November 2007 13:15 Go to previous message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:

that blog is not updated since september 2006.

http://jonathanlewis.wordpress.com/all-postings/
Quote:

Jonathan Lewis @ 7:27 pm UTC Nov 25,2007


Quote:

invalid alter tablespace statement
Whats the syntax?

SQL Reference

Regards
Michel

[Updated on: Mon, 26 November 2007 13:16]

Report message to a moderator

Previous Topic: Script dbstart
Next Topic: ORA-00600 error
Goto Forum:
  


Current Time: Fri Dec 02 18:21:35 CST 2016

Total time taken to generate the page: 0.25626 seconds