Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: Moving tables between tablespaces?
"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.
-- Niall Litchfield Oracle DBA Audit Commission UK ***************************************** Please include version and platform and SQL where applicable It makes life easier and increases the likelihood of a good answer ******************************************Received on Thu Jun 27 2002 - 08:54:00 CDT