Re: Moving tables between tablespaces?

"Niall Litchfield" <n-litchfield_at_audit-commission.gov.uk> wrote in message news:3d1b18f8$0$230$ed9e5944_at_reading.news.pipex.net...
> "Imprecise" <f_puhan_at_precise.com> wrote in message
> news:f_puhan-31EDC5.09285027062002_at_vienna7.his.com...
> > During a period of inactivity (forced or scheduled), or on a test
> > instance, I would
> >
> > 1) Query DBA_INDEXES for any that might exist based on the original
> > tables.
> > 1) RENAME the original tables. My convention is to append "__X"
> > (that's double-underscore-ex) to the table name.
> > 2) Issue the CREATE TABLE AS SELECT statement, specifying the new
> > tablespace in the storage parameter clause.
> > 3) REBUILD any indices as necessary.
> > 4) Query DBA_OBJECTS and recompile any views, etc. that may have become
> > invalidated through this activity.
> > 5) Turn back on the database
> > 6) After confirming all operations are working properly, DROP the
> > original -- now renamed -- tables.
>
> steps 1 (the second) and step 2 above can be replaced by ALTER TABLE BLAH
> MOVE TABLESPACE NEW_TS; on the version in question. Similarly the indexes
> can be rebuild with a new tablespace if required.
>
>

Thanks for the info, I managed to convince him to actually go and do it! The worrying thing is that I wouldn't have thought I'd *have* to be doing that sort of thing in the first place.....

chers,

[ste] Received on Thu Jun 27 2002 - 09:43:15 CDT