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  |
trantuananh24hg
Messages: 744
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 #277136 is a reply to message #277116] |
Mon, 29 October 2007 01:31  |
trantuananh24hg
Messages: 744
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 #277200 is a reply to message #277144] |
Mon, 29 October 2007 03:59 |
trantuananh24hg
Messages: 744
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 #277206 is a reply to message #277203] |
Mon, 29 October 2007 04:19 |
trantuananh24hg
Messages: 744
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 #277244 is a reply to message #277227] |
Mon, 29 October 2007 05:29 |
trantuananh24hg
Messages: 744
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 #277389 is a reply to message #277308] |
Mon, 29 October 2007 21:37 |
trantuananh24hg
Messages: 744
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 #277468 is a reply to message #277431] |
Tue, 30 October 2007 04:42 |
trantuananh24hg
Messages: 744
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 |
|
|
|
Goto Forum:
Current Time: Sat Nov 30 04:51:58 CST 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
