move table to new tablespace

[bookouri]

I have one table I need to move into its own tablespace. Ive created the new tablespace and am ready to move the table. But, it is a very large table and i need to move it with as little interruption to the users as possible. What is the best way to accomplish this?

thanks in advance for any suggestions

 

[SantaMufasa]

Bookouri,

By far, the fastest and least intrusive method to move a table from one tablespace to another is:

alter table <owner>.<table_name> move parallel nologging tablespace <new_tsname>;

The one restriction for this to work is that the table must not contain a LONG column. If it contains a LONG, then the only method is to export then re-import. Another provision is that you should do the MOVE when no one is accessing the table.

If you wish to move indexes for the table, then the code would be:

alter index <owner>.<index_name> rebuild parallel tablespace <new_tsname>;

There are no restrictions in the REBUILD command per se.

Both of these commands run remarkably quickly. You will be very pleased with the speed.

Let us know your findings.

Mufasa
(aka Dave of Sandy, Utah, USA @ 16:30 (13Sep04) UTC (aka "GMT" and "Zulu"), 09:30 (13Sep04) Mountain Time)

 

[bookouri]

The table does contain a LongRaw column. Does it have the same restriction?

 

[bookouri]

Yes, i tested the script and it fails because of the LongRaw column...

 

[SantaMufasa]

Bookouri,

Unfortunately a LONG RAW is a LONG and thus the restriction applies. You can circumvent the restriction by ALTER-ing the column from LONG RAW to something like CLOB, but doing so opens another can of worms: although Oracle allows you to MODIFY a LONG RAW to CLOB, CLOBs do not behave in quite the same was as LONG RAWs and you cannot MODIFY a CLOB back to a LONG RAW directly.

You also may not do a "CREATE TABLE...AS SELECT..." from a table with a LONG RAW either, so it appears that if you wish to changes tablespaces, it's going to require the proverbial "exp" followed by an "imp" (unless a Tek-Tipster smarter than I can propose a solution).

Sorry,

Mufasa
(aka Dave of Sandy, Utah, USA @ 16:49 (13Sep04) UTC (aka "GMT" and "Zulu"), 09:49 (13Sep04) Mountain Time)

 

[bookouri]

with 9i can i export a table and re-import into a different tablespace?

 

[SantaMufasa]

Yes, but it, too, is a bit tricky. Since the "exp" dumpfile includes the original tablespace assignment, "imp" attempts to bring the table back into the original tablespace, you must do the following:

1) perform your "exp"
2) confirm the integrity of your dump file to whatever level you choose.
3) DROP the original table.
4) Re-create the table in the new tablespace. You can do this either "by hand", or you can "imp" the dumpfile but specify the import parameter "indexfile=<file_name.sql>". Then text edit <file_name.sql>, changing the TABLESPACE definition to the new tablespace. Next, from SQL*Plus, "@<file_name>", which re-creates an empty version of your table in the new tablespace.
5) "imp" the dumpfile with the import paramter "ignore=y" so that the import program does not fail due to the pre-existence of the target table.

When you do effect your method, please post your results here.

[santa]Mufasa
(aka Dave of Sandy, Utah, USA @ 17:00 (13Sep04) UTC (aka "GMT" and "Zulu"), 10:00 (13Sep04) Mountain Time)

 

[bookouri]

I havnt had a chance to try yet, but will that method maintain all the triggers, constraints, grants, synonyms and everything associated with the table?

 

[SantaMufasa]

Bookouri,

When you drop the original table, the triggers, views, procedures, functions, et cetera that depend upon the table then have a status of "INVALID". Once you successfully re-import the table, then upon next invocation of the invalidated object Oracle attempts to recompile the object. If all goes well during recompile, Oracle will cause the status to become VALID. The constraints come in with the import; GRANTS will come in if you ensure that for both "exp" and "imp" you supply the "GRANTS=Y" parameter. Once the import runs successfully, all the synonyms should be effective.

Mufasa
(aka Dave of Sandy, Utah, USA @ 06:42 (14Sep04) UTC (aka "GMT" and "Zulu"), 23:42 (13Sep04) Mountain Time)