pak_slm
asked on
ORA-01654: unable to extend index error
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
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
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;
by increasing the size of SYSTEM tablespace
alter database datafile '<system file path>' resize <a higher size>M;
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.
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.
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
you have to drop the old index and build a new one after you change the default tablespace of the user
@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
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
ASKER
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
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
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.
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.
ASKER
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
Actually i want to change the tablespaces of some tables
ASKER
SDutta
i execute ALTER INDEX REBUILD
it run successfully without error
Please tell command like ALTER INDEX REBUILD to alter tablespaces of a table
i execute ALTER INDEX REBUILD
it run successfully without error
Please tell command like ALTER INDEX REBUILD to alter tablespaces of a table
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.
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.
---->?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.
You should re-create table in the new tablespace, drop the old one.
ASKER
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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>;