On May 5, 8:08 am, JACKY <zhp...@[EMAIL PROTECTED]
> wrote:
> On 4=D4=C230=C8=D5, =C9=CF=CE=E75=CA=B125=B7=D6, GS <G...@[EMAIL PROTECTED]
> 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=3D'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 ex****t,
extract the DDL, change the DDL, execute the DDL then im****t 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 =3D 'SYSTEM'
spool rebuild_user_indexes.sql
/
spool off
@[EMAIL PROTECTED]
the 'tablespace' clause in the alter index .. rebuild
statement, which places the index in a different tablespace.
David Fitzjarrell
|
