Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

ORA-01654: unable to extend index error

Posted on 2004-10-07
13
Medium Priority
?
15,956 Views
Last Modified: 2011-08-18
i have running Oracle 8 on Linux, it gives following error

 ORA-01654: unable to extend index abc.TRANSACTION_INDEX by 12144 in tablespace SYSTEM

i also want to change the default tablespaces of my user.

can i change default tablespaces in running system



0
Comment
Question by:pak_slm
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 4
  • 4
  • +1
13 Comments
 
LVL 23

Expert Comment

by:seazodiac
ID: 12251141
No offense, it's really bad to put indexes on SYSTEM tablespace.

i see the change of user default tablespace is long overdue...


SQL>alter user <user_name> default tablespace <tablespace_name> quota unlimited on <tablespace_name>;
0
 
LVL 23

Expert Comment

by:seazodiac
ID: 12251171
before rebuilding this index, you can workaround that ORA-1654 error,
by increasing the size of SYSTEM tablespace

alter database datafile '<system file path>' resize <a higher size>M;

0
 
LVL 10

Expert Comment

by:SDutta
ID: 12251256
You can rebuild the index on a running system without any ill effects. If the table is locked by another process it will give you a "resource busy" error and you can try it again later.

ALTER INDEX ABC.TRANSACTION_INDEX REBUILD TABLESPACE new_tablespace STORAGE (INITIAL 1M NEXT 1M PCTINCREASE 50);

You could increase the size of INITIAL and NEXT to larger values if the table has a lot of data in it. If you do increase it to larger values, you may be better off with PCTINCREASE 0.

Of course you should also follow the guidelines suggested by seazodiac so that you dont have this problem in future.
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 23

Expert Comment

by:seazodiac
ID: 12251375
No, you cannot do that in Oracle8.

you have to drop the old index and build a new one after you change the default tablespace of the user
0
 
LVL 10

Expert Comment

by:SDutta
ID: 12251477
@seazodiac

You can definitely do ALTER INDEX REBUILD in Oracle 8.
Maybe you are thinking of ALTER TABLE MOVE ... which was not allowed in 8.0
0
 
LVL 1

Author Comment

by:pak_slm
ID: 12251941
SDutta
tell me one thing more
if i give command create table table2 as select * from a table1 in new tablespaces
then drop table1 and rename table2 as table1
it is possible? if yes then tell me rename table command otherwise i try ALTER INDEX REBUILD

what is time comparison




0
 
LVL 10

Expert Comment

by:SDutta
ID: 12252380
Yes you can do it but as soon as you rename the old table its dependent objects may become invalid and have to be recompiled.

You can do "create table table2 as select * from a table1" with storage clause specifying new tablespace name.
Then you can :
RENAME TABLE1 TO TABLE1_BKUP; -- drop it later after you are absolutely sure
RENAME TABLE2 TO TABLE1;

Then create the indexes etc. check for invalid dependents and make them valid.
0
 
LVL 1

Author Comment

by:pak_slm
ID: 12252530
is it better procedure or tell another procedure to change the tablespaces of a table

Actually i want to change the tablespaces of some tables
0
 
LVL 1

Author Comment

by:pak_slm
ID: 12252601
SDutta

i execute ALTER INDEX REBUILD
it run successfully without error

Please tell command like ALTER INDEX REBUILD  to alter tablespaces of a table  
0
 
LVL 35

Expert Comment

by:Mark Geerlings
ID: 12253707
Oracle8.1.5 (and higher) supports this command:
alter table [table_name] move [new_tablespace_name];

If you have Oracle8.0.x, that may not be supported.

It is a **BAD** idea to have user tables or indexes in the SYSTEM tablespace!  The problem is, you cannot defragment the SYSTEM tablespace unless you are willing to export the entire database, shut it down, drop the database, create a new database, then run a full import.  Of course, you should never do this unless you take a full backup before you drop it!

All of your tables and indexes that belong to schemas other than SYS and SYSTEM should be in other tablespaces, since you can defragment them, if necessary, with much less risk and effort.
0
 
LVL 23

Expert Comment

by:seazodiac
ID: 12254045
---->?Please tell command like ALTER INDEX REBUILD  to alter tablespaces of a table

You should re-create table in the new tablespace, drop the old one.
0
 
LVL 1

Author Comment

by:pak_slm
ID: 12255566

if i create table by create table as command then what about
as per SDutta

Yes you can do it but as soon as you rename the old table its dependent objects may become invalid and have to be recompiled.

i do not understand recompiled dependent objects.

tell the procedure if i followed this way
0
 
LVL 10

Accepted Solution

by:
SDutta earned 1000 total points
ID: 12259111
@pak_slm

You cannot change the tablespace of a TABLE with ALTER INDEX REBUILD. It can be used to change the tablespace of an INDEX.

The error you reported was :
ORA-01654: unable to extend index abc.TRANSACTION_INDEX by 12144 in tablespace SYSTEM

Which specifically says the problem is with the INDEX not the TABLE (that could be a problem too but is out of this context).

I thought I had already mentioned in my first post the command to change the tablespace of the index by : ALTER INDEX ABC.TRANSACTION_INDEX REBUILD TABLESPACE new_tablespace STORAGE (INITIAL 1M NEXT 1M PCTINCREASE 50);

If you want to change the tablespace of a TABLE you cannot do it so easily in 8.0, the best options are : EXPORT/IMPORT or CREATE NEW_TBL AS SELECT * FROM OLD_TBL (and drop OLD_TBL).
0

Featured Post

Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

705 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question