Index rebuild error [message #275004] |
Thu, 18 October 2007 01:37 |
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 #275053 is a reply to message #275004] |
Thu, 18 October 2007 03:19 |
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 #275060 is a reply to message #275004] |
Thu, 18 October 2007 03:36 |
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 #275074 is a reply to message #275004] |
Thu, 18 October 2007 04:10 |
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 #275131 is a reply to message #275060] |
Thu, 18 October 2007 07:41 |
joy_division
Messages: 4963 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 |
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 #275167 is a reply to message #275144] |
Thu, 18 October 2007 11:48 |
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
|
|
|