ALTER TABLE MOVE does not support LONG columns.
Use exp/imp as previously mentioned or the SQL Plus Copy Command. You
may want to check the SQL Plus manual for specifics of the COPY command.
The COPY command does not handle privileges or dependencies, it just
copies the data.
From SQL Plus do something like this,
-- create a new table to hold the date
SQL>CREATE TABLE new_table_name ( column specs ) TABLESPACE new_tablespace;
SQL> SET LONG 100000
SQL> COPY FROM user_name/[Email Address Removed] -
> INSERT new_table_name -
> using select * from schema.orig_table_name
-- verify data then rename or drop original table
SQL> ALTER TABLE orig_table_name RENAME TO orig_table_name_bak;
-- now rename the new table to the original table name
SQL> ALTER TABLE new_table_name RENAME TO orig_table_name;
Pat Reither
Edwards Ed wrote:
>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
>
>
>---------------------------------------------------------------------
>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