Re: moving tables from system tablespace
Date: Mon, 5 May 2008 06:08:34 -0700 (PDT)
Message-ID: <76a26531-df5d-4e38-95a2-882f3ff3ac45@w5g2000prd.googlegroups.com>
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' Received on Mon May 05 2008 - 08:08:34 CDT