INDEXES X TABLESPACE 1

[ofsouto]

I list all indexes on a database and I notice that majority indexes was created on System
tablespace and the objects tablespace is another.
Is it a problem? How can I change the tablespace? Is it necessary?
Thanks in advance.

 

[alexhu]

Create a new tablespace for the indeses and REBUILD them in there.

You should not have objects other than owned by SYSTEM and SYS in the SYSTEM tablespace.

Alex

 

[karluk]

The command to do this is

alter index index_name rebuild tablespace your_tablespace_name;

You can generate the alter index statements by running a query like the following

select 'alter index ' || index_name || ' rebuild tablespace your_tablespace_name;' from user_indexes
where tablespace_name = 'SYSTEM';

You should also investigate WHY most of your indexes are in the system tablespace. Most likely one or more ids has a default tablespace of SYSTEM. To find such users, run the query,

select username from dba_users where default_tablespace='SYSTEM';

Then switch any application users to another default tablespace.

alter user user_name default tablespace your_tablespace_name;

 

[Scunningham99]

you will need to export them in to your new tablespace.

Check which user owns the indexes and make sure their default tablespace is not set to system. Instead set it to the tablespace you have created. By default users are set up to use system as their default tablespace, if no tablespace clause is used when the user was created!!

 

[ofsouto]

Thanks. I solve the problem with these replies.