Re: QUESTION: Rebuilding default indexes for MVs into different tablespace

On Jul 18, 11:51 pm, ErikYkema <erik.yk..._at_gmail.com> wrote:
> On Jul 18, 12:49 am, BD <robert.d..._at_gmail.com> wrote:
>
>
>
>
>
> > I'm on 10.2.0.3 on Win32.
>
> > I have some materialized views which were created without the 'USING
> > INDEX' clause.
>
> > The only provision I had put in that clause (for those MVs which were
> > created correctly) was for a separate tablespace for the indexes.
>
> > I have located the "I_SNAP$_" indexes for the MVs - specifically,
> > those that are in an incorrect tablespace.
>
> > Is there a risk in relocating these indexes to the correct tablespace
> > with a standard 'ALTER INDEX REBUILD' statement?
>
> > The only other option I see is to drop and recreate the MV, which is
> > not appealing because some of them are over 1GB in size. Hopefully a
> > quick 'gen' script to create a series of ALTER INDEX REBUILD
> > statements is all I'll need...
>
> > Thanks,
>
> > BD
>
> Please show your current create statement, I do not get what you mean
> by the 'provisioning' sentence.
> Also seehttp://download.oracle.com/docs/cd/B19306_01/server.102/b14200/statem....
> Experiment with a bogus mview until you may get it right, using using
> index I assume. I recommend to follow the documentation and not to
> touch the underlaying technical objects directly, unless explicitly
> supported.
> Regards, Erik Ykema- Hide quoted text -
>
> - Show quoted text -

What I meant by the 'provisioning' bit was that for *most* of my MVs, the statement was, for example, "CREATE MATERIALIZED VIEW TABLE_X TABLESPACE MVTS_1 BUILD IMMEDIATE USING INDEX TABLESPACE MVTS_2 REFRESH FORCE ON DEMAND WITH PRIMARY KEY AS SELECT FROM OWNER_1.TABLE_X_at_REMOTEDB NOLOGGING;" For a few of them, the statement was, for example, "CREATE MATERIALIZED VIEW TABLE_X TABLESPACE MVTS_1 REFRESH FORCE ON DEMAND WITH PRIMARY KEY AS SELECT FROM OWNER_1.TABLE_X_at_REMOTEDB NOLOGGING;" I see several repurcussions of this distinction, including the fact that the default index used for maintaining the MV in the FAST refresh type ("I_SNAP$_TABLE_X") is in the default tablespace for the schema owner, not in the tablespace which I'd intended the indexes to reside (MVTS_2). My question amounted to whether I could/should simply "ALTER INDEX I_SNAP$_TABLE_X REBUILD TABLESPACE MVTS_2;" etc for all indexes which were created in such a way.

I've also posted to the OTN forums, and did get at least one opinion that such a rebuild was entirely reasonable.

I did spend some time poring over the syntax diagrams you refer to before posting, and while the ALTER MATERIALIZED VIEW statement does have options for altering storage parameters for the MV object itself, I saw no options for altering the storage parameters for its default index.

Thanks,

BD Received on Thu Jul 19 2007 - 18:38:58 CDT