Home » RDBMS Server » Server Administration » I don't rebuild index, return with ORA-01652 error?
I don't rebuild index, return with ORA-01652 error? [message #277106] Sun, 28 October 2007 23:23 Go to next message
trantuananh24hg
Messages: 668
Registered: January 2007
Location: Ha Noi, Viet Nam
Senior Member
Dear!

When I rebuild the index, I have encountered following:
SQL> alter index vina.AX rebuild;
alter index vina.AX rebuild
*
ERROR at line 1:
ORA-01652: unable to extend temp segment by 128 in tablespace TMP


Then, I followed the Oracle guide
Quote:


ORA-01652: unable to extend temp segment by string in tablespace string
Cause: Failed to allocate an extent of the required number of blocks for a temporary segment in the tablespace indicated.
Action: Use ALTER TABLESPACE ADD DATAFILE statement to add one or more files to the tablespace indicated.


...to increased
SQL> select name
  2  from v$tempfile
  3  ;

NAME
----------------------------

E:\ORADATA\NEO\TMP.DBF
E:\ORADATA\NEO\TMP01.DBF


Adding datafile to TMP tablespace..
SQL> alter tablespace TMP add tempfile 'E:\ORADATA\NEO\TMP02.DBF' size 400M reuse;
Tablespace altered.

SQL>


Finally, rebuild index again, but...
SQL> alter index vina.AX rebuild;
alter index vina.AX rebuild
*
ERROR at line 1:
ORA-01652: unable to extend temp segment by 128 in tablespace TMP


Have you got any idea to help me?

Thanks a lot!

[Updated on: Sun, 28 October 2007 23:24]

Report message to a moderator

Re: I don't rebuild index, return with ORA-01652 error? [message #277108 is a reply to message #277106] Sun, 28 October 2007 23:35 Go to previous messageGo to next message
Arju
Messages: 1554
Registered: June 2007
Location: Dhaka,Bangladesh. Mobile:...
Senior Member

check out the user temporary tablespace.
Re: I don't rebuild index, return with ORA-01652 error? [message #277113 is a reply to message #277108] Mon, 29 October 2007 00:09 Go to previous messageGo to next message
trantuananh24hg
Messages: 668
Registered: January 2007
Location: Ha Noi, Viet Nam
Senior Member
Thank you, Arju!

But I don't really understand what you mean?
This database is startup mount and there are no user connect to this database.

What would you like to advice me any thing else?

Thank you!
Re: I don't rebuild index, return with ORA-01652 error? [message #277116 is a reply to message #277106] Mon, 29 October 2007 00:15 Go to previous messageGo to next message
Arju
Messages: 1554
Registered: June 2007
Location: Dhaka,Bangladesh. Mobile:...
Senior Member

Unless you open your database(startup mount) how you can rebuild index?
Re: I don't rebuild index, return with ORA-01652 error? [message #277136 is a reply to message #277116] Mon, 29 October 2007 01:31 Go to previous messageGo to next message
trantuananh24hg
Messages: 668
Registered: January 2007
Location: Ha Noi, Viet Nam
Senior Member
Yes, I know!

Because of the started mount Database, this is the reason made me surprise when error occurred

SQL> alter index vina.AX rebuild;
alter index vina.AX rebuild
*
ERROR at line 1:
ORA-01652: unable to extend temp segment by 128 in tablespace TMP


I will try again and send the result to you.

Thanks
Re: I don't rebuild index, return with ORA-01652 error? [message #277138 is a reply to message #277106] Mon, 29 October 2007 01:36 Go to previous messageGo to next message
Arju
Messages: 1554
Registered: June 2007
Location: Dhaka,Bangladesh. Mobile:...
Senior Member

!

[Updated on: Mon, 29 October 2007 01:36]

Report message to a moderator

Re: I don't rebuild index, return with ORA-01652 error? [message #277144 is a reply to message #277136] Mon, 29 October 2007 01:59 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Do you mean you tried to execute this command when the database is not open?

Regards
Michel
Re: I don't rebuild index, return with ORA-01652 error? [message #277200 is a reply to message #277144] Mon, 29 October 2007 03:59 Go to previous messageGo to next message
trantuananh24hg
Messages: 668
Registered: January 2007
Location: Ha Noi, Viet Nam
Senior Member
First time, I tried to rebuild this index when database is already open, but I can not. And... I'm sorry for my idiot operation, I shutdown and startup Database mount to rebuild this, but failed to rebuild.

And,when I tried to rebuild index with option "Online", I can not.

I've not really understood about this problem ORA-01652: unable to extend temp segment by 128 in tablespace TMP.


Re: I don't rebuild index, return with ORA-01652 error? [message #277203 is a reply to message #277106] Mon, 29 October 2007 04:03 Go to previous messageGo to next message
Arju
Messages: 1554
Registered: June 2007
Location: Dhaka,Bangladesh. Mobile:...
Senior Member

select TEMPORARY_TABLESPACE from dba_users where username='user_name_by_which_you_invoke_command'; 
Re: I don't rebuild index, return with ORA-01652 error? [message #277206 is a reply to message #277203] Mon, 29 October 2007 04:19 Go to previous messageGo to next message
trantuananh24hg
Messages: 668
Registered: January 2007
Location: Ha Noi, Viet Nam
Senior Member
Following your guide:

C:\>set oracle_sid=neoibm

C:\>sqlplus /nolog

SQL*Plus: Release 10.2.0.1.0 - Production on Mon Oct 29 16:07:07 2007

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

SQL> conn sys/vina@neoibm as sysdba
Connected.
SQL> select temporary_tablespace
  2  from dba_users
  3  where username='VINA';

TEMPORARY_TABLESPACE
------------------------------
TMP

SQL> alter index vina.ax rebuild online;
alter index vina.ax rebuild online
*
ERROR at line 1:
ORA-01652: unable to extend temp segment by 128 in tablespace TMP

SQL>










Re: I don't rebuild index, return with ORA-01652 error? [message #277227 is a reply to message #277206] Mon, 29 October 2007 04:58 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
What is the current status of your database? Open or not?
Anyway, if it is close the operation is silly, if it is open then enlarge tablespace TMP or try normal (not online) rebuild.

Regards
Michel
Re: I don't rebuild index, return with ORA-01652 error? [message #277244 is a reply to message #277227] Mon, 29 October 2007 05:29 Go to previous messageGo to next message
trantuananh24hg
Messages: 668
Registered: January 2007
Location: Ha Noi, Viet Nam
Senior Member
Thank you, Michael!

This database is open, of course.

SQL> select status from v$instance;

STATUS
------------
OPEN

SQL> desc v$tempfile
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------

 FILE#                                              NUMBER
 CREATION_CHANGE#                                   NUMBER
 CREATION_TIME                                      DATE
 TS#                                                NUMBER
 RFILE#                                             NUMBER
 STATUS                                             VARCHAR2(7)
 ENABLED                                            VARCHAR2(10)
 BYTES                                              NUMBER
 BLOCKS                                             NUMBER
 CREATE_BYTES                                       NUMBER
 BLOCK_SIZE                                         NUMBER
 NAME                                               VARCHAR2(513)

SQL> set pagesize 1000
SQL> set linesize 200
SQL> col name format a20
SQL> col MB format 999,999,999
SQL> ed
Wrote file afiedt.buf

  1  select (bytes/1024/1024) as "MB"
  2  ,name
  3* from v$tempfile
SQL>
SQL> /

          MB NAME
------------ --------------------
       1,000 E:\ORADATA\NEO\TMP.D
             BF

         100 E:\ORADATA\NEO\TMP01
             .DBF

         400 E:\ORADATA\NEO\TMP02
             .DBF


SQL> alter index vina.ax rebuild;
alter index vina.ax rebuild
*
ERROR at line 1:
ORA-01652: unable to extend temp segment by 128 in tablespace TMP

SQL> 

[Updated on: Mon, 29 October 2007 05:33] by Moderator

Report message to a moderator

Re: I don't rebuild index, return with ORA-01652 error? [message #277246 is a reply to message #277244] Mon, 29 October 2007 05:35 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Add tablespace name in your output (and enlarge your name column to 25 characaters).

By the way, the files you named are only of 1.5GB what is the size of the index?

Regards
Michel
Re: I don't rebuild index, return with ORA-01652 error? [message #277308 is a reply to message #277244] Mon, 29 October 2007 08:51 Go to previous messageGo to next message
joy_division
Messages: 4644
Registered: February 2005
Location: East Coast USA
Senior Member
You need up to three times the space for a rebuild of an index.
Re: I don't rebuild index, return with ORA-01652 error? [message #277389 is a reply to message #277308] Mon, 29 October 2007 21:37 Go to previous messageGo to next message
trantuananh24hg
Messages: 668
Registered: January 2007
Location: Ha Noi, Viet Nam
Senior Member
Yes! Thank you Michael, Joy and Arju!
This is the index size
SQL> select (bytes/1024/1024) as "MB"
  2  ,segment_type
  3  ,blocks
  4  from dba_segments
  5 where segment_name='AX';

          MB SEGMENT_TYPE                         BLOCKS
------------ ------------------------------ ------------
       8,024 INDEX                             1,027,072

SQL>


Must I increase the temp file to three times space?

The tablespace TMP has UNIFORM allocation type
SQL> select tablespace_name, allocation_type
  2  from dba_tablespaces
  3  where tablespace_name='TMP';

TABLESPACE_NAME                ALLOCATIO
------------------------------ ---------
TMP                            UNIFORM

SQL>


And, what should I do to format the column name into 25 characters?

I'm sorry about my lately answer.

Additional question: What will you do to know exactly which index need to be rebuilt? I mean that, when you have a DB with very many indexes, you can not execute one by one statement for all indexes manually!

Thank you very much!

[Updated on: Mon, 29 October 2007 22:07]

Report message to a moderator

Re: I don't rebuild index, return with ORA-01652 error? [message #277431 is a reply to message #277389] Tue, 30 October 2007 01:48 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:

What will you do to know exactly which index need to be rebuilt?

An index never needs to be rebuilt unless you make a purge and don't want to reinsert rows.

(Actually, there are some few cases with Oracle applications that need index rebuild but these are exceptions and you quickly know it as performances dramatically decrease)

Regards
Michel
Re: I don't rebuild index, return with ORA-01652 error? [message #277468 is a reply to message #277431] Tue, 30 October 2007 04:42 Go to previous message
trantuananh24hg
Messages: 668
Registered: January 2007
Location: Ha Noi, Viet Nam
Senior Member
Thank you for your reply, Michael!
I've known about the index and what's time to rebuild from you. Your advice took my sad away.Thank again.

As a junior DBA,I assume that the application (middle application) performs its good, (maybe request, returns, and anything else), and the network architecture, the storage and any hardware have good operations, but some sql statement still perform very slowly when they retrieve data from a healthy Database passed to AWR or STATPACK utility cheking, however, these statement have not any problem which require to be tuned. Why? Why does the result display slowly during everything that said I below are in normal operation?

In this case, what's the first task will you do? Please take an advice to me.

Come back to the index discussion, please look at my result with the schema in my database
SQL> analyze index vinaphone.ax validate structure;
 
Index analyzed.



And, I want to know how skewed that index is...
SQL> select DEL_LF_ROWS*100/decode(LF_ROWS, 0, 1, LF_ROWS) PCT_DELETED,
  2 (LF_ROWS-DISTINCT_KEYS)*100/ decode(LF_ROWS,0,1,LF_ROWS)   DISTINCTIVENESS
  3         from index_stats
  4 where  name='AX'

PCT_DELETED DISTINCTIVENESS
----------- ---------------
         22      8.00640144
SQL>


Metalink Note77574.1

Removed by MC, material is copyrighted


And I found the Note 122008.1 in Metalink.

Thank you!

[Updated on: Tue, 30 October 2007 05:05]

Report message to a moderator

Previous Topic: charset
Next Topic: Automatic archival value
Goto Forum:
  


Current Time: Fri Dec 09 15:35:53 CST 2016

Total time taken to generate the page: 0.17234 seconds