RE: Tablespace

I cannot recommend "alter table....move tablespace" in a production environment without first testing it's impact and considering all dependencies.

Indexes and references will brake as a result of it's use. These must be rebuilt and/or recreated. You must also reanalyze the moved table and it's indexes in a cost-based optimization environment, as these will be lost as a result of the move. Keep in mind that this analysis could take awhile when a large table is involved, and that performance may suffer while it is running.

Finally, if the table size has changed significantly since the last time it was analyzed, the new statistics may result in different query plans resulting in different and sometimes unpleasant performance anamolies.

Also consider that if you have third party software, be sure that any scripts or processes it runs may be impacted by this change. We moved a Peoplesoft tablespace three times, only to have a particular process relocate it (and reallocate it's size improperly).

Just some things to consider which are not immediately obvious from reading the manuals.

Michael Fontana
Sr. DBA
NTT/Verio

-----Original Message-----

From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Nahata, Naveen (US - Hyderabad)
Sent: Thursday, September 30, 2004 7:34 AM To: compute_at_ponyneedles.com; Oracle Mailing List Subject: RE: Tablespace

Balu,

You can create separate tablespaces for different modules and then migrate the tables and indexes to the new ones.

To move the tables you can use 'ALTER TABLE .... MOVE TABLESPACE ...' command and to move indexes you have to rebuild them to the new tablespace.

Regards
Naveen

-----Original Message-----

From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Computer Centre - NIIPL
Sent: Thursday, September 30, 2004 5:23 PM To: Oracle Mailing List
Subject: Tablespace

Dear All,

We are having one user tablespace which contains all our data (Accounts, Payroll, Sales,Materials etc). We would like to create separate data tablespace and index tablespace for the different modules. E.g.:- Accounts data tablespace, Accounts Index Tablespace.

Please guide us to achieve the same.

Thanks and Regards,
Balu.
This mail is scaned by eScan Anti Virus Software

--

http://www.freelists.org/webpage/oracle-l

This message (including any attachments) contains confidential = information intended for a specific individual and purpose, and is = protected by law. If you are not the intended recipient, you should = delete this message. Any disclosure, copying, or distribution of this = message, or the taking of any action based on it, is strictly = prohibited.
--

http://www.freelists.org/webpage/oracle-l

--

http://www.freelists.org/webpage/oracle-l Received on Thu Sep 30 2004 - 11:26:08 CDT