Home » SQL & PL/SQL » SQL & PL/SQL » Index rebuild error
Index rebuild error [message #275004] Thu, 18 October 2007 01:37 Go to next message
bhoite_amol83
Messages: 110
Registered: June 2005
Location: Pune
Senior Member
Hi Experts,

While rebuilding index i got the following error.

----------------------------------------------------------------
06:19:36 SQL> SET timing ON
06:19:44 SQL> ALTER INDEX TEST_IDX_1 REBUILD;
ALTER INDEX TEST_IDX_1 REBUILD
*
ERROR AT line 1:
ORA-01652: unable TO extend temp SEGMENT BY 8192 IN TABLESPACE
TEST_IDX_TT


Elapsed: 00:32:34.98
06:52:50 SQL> EXIT
----------------------------------------------------------------
I find one solution to increase temporary tablespace.
Is it a right solution?

If yes please tell me how can find the temporary tablespace in my databse?

Please,can anyone help me out.

Thanks in advance.





[Updated on: Thu, 18 October 2007 01:53]

Report message to a moderator

Re: Index rebuild error [message #275018 is a reply to message #275004] Thu, 18 October 2007 02:18 Go to previous messageGo to next message
Arju
Messages: 1554
Registered: June 2007
Location: Dhaka,Bangladesh. Mobile:...
Senior Member

Another Solution Add datafile.
Re: Index rebuild error [message #275019 is a reply to message #275004] Thu, 18 October 2007 02:19 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
The problem is not in the temporary tablespace but in the index one.
Its name is given in the error message.

Regards
Michel
Re: Index rebuild error [message #275025 is a reply to message #275004] Thu, 18 October 2007 02:31 Go to previous messageGo to next message
Arju
Messages: 1554
Registered: June 2007
Location: Dhaka,Bangladesh. Mobile:...
Senior Member

OP, knows better what is TABLESPACE TEST_IDX_TT ... It may be his temporary tablespace or may not.
Re: Index rebuild error [message #275053 is a reply to message #275004] Thu, 18 October 2007 03:19 Go to previous messageGo to next message
bhoite_amol83
Messages: 110
Registered: June 2005
Location: Pune
Senior Member
Thanks for your suggestions.

TEST_IDX_TT which is mentioned in error message is not a temporary tablsepace.

If you are suggesting me to add datafile then do you mean adding in current tablespace or temporary tablespace ?

If adding in current tablspace then i can do it.

But if you are suggesting to add into temporary tablespace then please let me know how can i find the temporary tablespace in my database.

Thanks once again.
Re: Index rebuild error [message #275054 is a reply to message #275053] Thu, 18 October 2007 03:25 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Michel Cadot wrote on Thu, 18 October 2007 09:19

The problem is not in the temporary tablespace but in the index one.
Its name is given in the error message.

Regards
Michel


Re: Index rebuild error [message #275060 is a reply to message #275004] Thu, 18 October 2007 03:36 Go to previous messageGo to next message
bhoite_amol83
Messages: 110
Registered: June 2005
Location: Pune
Senior Member
Hi Michel,

I am sorry but i do not able to understand what is the error in the index.
The index which i am rebuilding is already working fine on the existing table.

Can you explain me in detail what you want to suggest?

Thanks in advance.
Re: Index rebuild error [message #275062 is a reply to message #275004] Thu, 18 October 2007 03:47 Go to previous messageGo to next message
Arju
Messages: 1554
Registered: June 2007
Location: Dhaka,Bangladesh. Mobile:...
Senior Member

The problem in not in index but in the tablespace TEST_IDX_TT containing the index.
Re: Index rebuild error [message #275074 is a reply to message #275004] Thu, 18 October 2007 04:10 Go to previous messageGo to next message
bhoite_amol83
Messages: 110
Registered: June 2005
Location: Pune
Senior Member
Hi Arju,

Please, Can you give me exact solution so that i can look into it?
I conused what will be final solution.
As i am working on the huge database. Your suggestions are very important.

Thanks.
Re: Index rebuild error [message #275081 is a reply to message #275004] Thu, 18 October 2007 04:16 Go to previous messageGo to next message
Arju
Messages: 1554
Registered: June 2007
Location: Dhaka,Bangladesh. Mobile:...
Senior Member

One solution,
Alter TABLESPACE TEST_IDX_TT Add datafile '/path_name_here' ;
Re: Index rebuild error [message #275086 is a reply to message #275081] Thu, 18 October 2007 04:20 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Hi Arju,

I am too lazy to read the documentation and try to find myself, can you give another solution.

As i am working on the huge database. Your suggestions are very important.

Thanks.

Re: Index rebuild error [message #275090 is a reply to message #275004] Thu, 18 October 2007 04:33 Go to previous messageGo to next message
Arju
Messages: 1554
Registered: June 2007
Location: Dhaka,Bangladesh. Mobile:...
Senior Member

Another one,

select file_name,autoextensible from dba_data_files where tablespace_name='TEST_IDX_TT' ;

Then,

alter database datafile ..... resize ..

alter database datafile ....... autoextend on maxsize unlimited.

Note that in all the cases the user is granted sufficient quota on the tablespace.
Re: Index rebuild error [message #275094 is a reply to message #275090] Thu, 18 October 2007 04:45 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Hi Arju,

Can you give how to see the quota and how to change it.
It is really to hard to search and read the documentation.

As i am working on the huge database. Your suggestions are very important.

Thanks
Re: Index rebuild error [message #275096 is a reply to message #275004] Thu, 18 October 2007 04:58 Go to previous messageGo to next message
Arju
Messages: 1554
Registered: June 2007
Location: Dhaka,Bangladesh. Mobile:...
Senior Member

Oh, no Michel. I am also busy with cricinfo.com and also with my task.
Re: Index rebuild error [message #275121 is a reply to message #275096] Thu, 18 October 2007 06:50 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Arju, it was just a joke, I was mimicing OP.

Regards
Michel
Re: Index rebuild error [message #275131 is a reply to message #275060] Thu, 18 October 2007 07:41 Go to previous messageGo to next message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
bhoite_amol83 wrote on Thu, 18 October 2007 04:36

Hi Michel,

I am sorry but i do not able to understand what is the error in the index.
The index which i am rebuilding is already working fine on the existing table.



Just a little helpful (I think) info.

An index can temporarily take up to 3 times the amount of space (possibly more) for a rebuild.

[edit] added the word 'temporarily'

[Updated on: Thu, 18 October 2007 07:42]

Report message to a moderator

Re: Index rebuild error [message #275144 is a reply to message #275131] Thu, 18 October 2007 08:57 Go to previous messageGo to next message
ora110
Messages: 42
Registered: September 2007
Location: China
Member

you should extend your temporary tablespace size .
there has two methods
1. alter database tempfile 'your tempfile' resize xxxxM;
2.alter tablespace temp add tempfile 'your tempfile path/name' size xxxxM ;
you can get the temporary information by using query "select * from v$tempfile"

hope you can manage to deal with it .

sorry ,my english ability is low.
Re: Index rebuild error [message #275146 is a reply to message #275144] Thu, 18 October 2007 09:02 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Why are you talking about temporary tablespace?
There is no issue (currently at least) with the temprary tablespace.

Regards
Michel
Re: Index rebuild error [message #275167 is a reply to message #275144] Thu, 18 October 2007 11:48 Go to previous message
ora_balan
Messages: 21
Registered: January 2007
Location: Mumbai, India
Junior Member
It would be better to know what kind of storage management is employed on your database, specifically your tablespace.

can you run the following queries and post the result?
SELECT extent_management
,allocation_type
,segment_space_management
FROM user_tablespaces
WHERE tablespace_name = '<your index tablespace>'

select * from v$version

based on the result, we can determine how well can we play with the storage clauses available while rebuilding your index.
An increase in your tablespace datafile may not be needed hopefully.

Also, try to find out from your DBA if the SYSTEM Tablespace is also a locally managed tablespace.
Will help in deciding the further course of action.

[Updated on: Fri, 19 October 2007 10:29]

Report message to a moderator

Previous Topic: How to drop all constraints on a schema at once?
Next Topic: Index stats in oracle 8i
Goto Forum:
  


Current Time: Sun Dec 04 04:18:03 CST 2016

Total time taken to generate the page: 0.07880 seconds