In Oracle 8i a new command option was introduced to the ALTER TABLE
syntax to address this issue specifically - the MOVE option. With the
ALTER TABLE <tablename> MOVE syntax it is now possible to reorganize the
table while ensuring that security information and indexes are retained,
and the table is accessible to users while the MOVE is being performed!!
If your table was created on the ACCOUNTING tablespace, for example, and
you wanted to move it to the new ACCOUNTING2 tablespace you just created
on a new set of disks, you can issue the following command:
ALTER TABLE Customers MOVE TABLESPACE Accounting2
If you simply wanted to re-organize the table and keep it on the same
tablespace, you can do that too by issuing the command:
ALTER TABLE Customers MOVE TABLESPACE Accounting
In either case, Oracle will rebuild the table in the target tablespace,
allow users to query the existing table while the move is taking place,
keep the existing permissions assigned to the table, and rebuild the
indexes after the operation is completed. Optionally, you can also
specify storage clause characteristics to change the extent sizes and
other parameters (with the exception of FREELISTS or FREELIST GROUPS) if
you find that the pervious storage parameters are no longer compatible
with the table access and storage patterns.
Of course, there is a downside as well. Because the old copy of the
table is kept until the move completes to allow queries to take place,
this means that you will need sufficient disk space for both the old and
new versions of the table - typically double what was required before.
Note that the MOVE option of the ALTER TABLE syntax only applies to
non-partitioned or index-organized tables. If you want to move
partitions or sub-partitions to another tablespace, or reorganize them,
you need to issue the ALTER TABLE ... MOVE PARTITION or ALTER TABLE ...
MOVE SUBPARTITION commands for each partition or sub-partition.
Despite its main drawback - the requirement for double the disk space -
this one command can make it extremely easy to reorganize tables for
better performance without all the headaches of EXPORT and IMPORT or
CREATE TABLE ... AS SELECT.
Please take a few minutes to provide feedback on the quality of service you received. The Department of Education values your feedback as a customer. Commissioner John L. Winn is committed to continuously assessing and improving the level and quality of services provided to you by Department staff. Simply use the link below. Thank you in advance for completing the survey.
http://data.fldoe.org/cs/default.cfm?staff=Ed.[Email address protected]
-----Original Message-----
From: Sheuehua
[mailto:oracledba-ezmlmshield-x9836866.[Email address protected]
Sent: Wednesday, January 31, 2007 10:16 AM
To: LazyDBA Discussion
Subject: Re: Moving tables to a different tablespace
You have to do exp/imp on that table.
"Burris Mark " <oracledba-ezmlmshield-x22008645.[Email address
protected]
01/31/2007 10:15 AM
To
"LazyDBA Discussion" <[Email address protected]
cc
Subject
Moving tables to a different tablespace
I want to move some tables to a different tablespace and I can't do the
move table command because the table has long columns. What would be
the best approach to move these tables.
---------------------------------------------------------------------
TO REPLY TO EVERYBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
To post a dba job: http://jobs.lazydba.com
To Subscribe : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html
To: "LazyDBA Discussion" <[Email address protected]
cc:
From: "Burris Mark "
<oracledba-ezmlmshield-x22008645.[Email address protected]
**********************************************************************
This e-mail transmission and any attachments that accompany it may
contain information that is privileged, confidential or otherwise
exempt from disclosure under applicable law and is intended solely for
the use of the individual(s) to whom it was intended to be addressed.
If you have received this e-mail by mistake, or you are not the
intended recipient, any disclosure, dissemination, distribution,
copying or other use or retention of this communication or its
substance is prohibited. If you have received this communication in
error, please immediately reply to the author via e-mail that you
received this message by mistake and also permanently delete the
original and all copies of this e-mail and any attachments from your
computer. Thank you.
**********************************************************************
---------------------------------------------------------------------
TO REPLY TO EVERYBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
To post a dba job: http://jobs.lazydba.com
To Subscribe : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html
Oracle LazyDBA home page