Re: moving tables from system tablespace

From: JACKY <>
Date: Mon, 5 May 2008 06:08:34 -0700 (PDT)
Message-ID: <>

On 4月30日, 上午5时25分, GS <> 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' Received on Mon May 05 2008 - 08:08:34 CDT

Original text of this message