RE: Tablespace
- From: "Michael Fontana" <mfontana@xxxxxxxxx>
- To: <nnahata@xxxxxxxxxxxx>, <compute@xxxxxxxxxxxxxxx>, "'Oracle Mailing List'" <ORACLE-L@xxxxxxxxxxxxx>
- Date: Thu, 30 Sep 2004 11:30:22 -0500
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@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Nahata, Naveen (US -
Hyderabad)
Sent: Thursday, September 30, 2004 7:34 AM
To: compute@xxxxxxxxxxxxxxx; 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@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] 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
--
//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.
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
Other related posts:
