Re: moving tables from system tablespace

On May 5, 8:08 am, JACKY <zhp..._at_gmail.com> wrote:
> On 4ÔÂ30ÈÕ, ÉÏÎç5ʱ25·Ö, GS <G..._at_GS.com> wrote:
>
>
>
>
>
> > Checking out a new vendor installed database I see that there are
> > several tables residing in the system tablespace, non have any LOB or
> > BLOB columns. There is also a few indexes that have somehow ended up in
> > the same tablespace. Having never had to use "alter table move" before,
> > I am thinking this is the best way to move said tables to a proper
> > tablespace, but from what I understand the indexes with these
> > tablespaces will be rendered useless and have to be rebuilt as well.
>
> > This is also one of the first databases I look after to be installed on
> > 10GR2 - and I thought that perhaps the re-org option in EM might allow
> > me to do all in one fell swoop, but the GUI has no provision for
> > switching tablespaces.
>
> > Is just issuing an "alter table move <new tablespace>" then rebuilding
> > all the indexes one at a time the best way to go about this?
>
> > thanks
>
> you can use following sql and save result to a
> sqlfile(eg:rebuileindex.sql),after
> excute this sqlfile:
>
> select 'alter index '|| index_name ||' rebuild;' from sys.all_indexes
> where owner='USERNAME'- Hide quoted text -
>
> - Show quoted text -

Which places the index in the SAME tablespace it was to begin with, so it doesn't move anything. The original question was regarding the most efficient method to MOVE objects from the SYSTEM tablespace to a non-SYSTEM tablespace.

Out of several options to effect this change (including export, extract the DDL, change the DDL, execute the DDL then import the objects with ignore-y) I think the alter index ... rebuild tablespace ... option is what I'd choose to execute:

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

spool rebuild_user_indexes.sql
/
spool off

@rebuild_user_indexes

Notice the 'tablespace' clause in the alter index .. rebuild statement, which places the index in a different tablespace.

David Fitzjarrell Received on Mon May 05 2008 - 09:25:44 CDT