Re: switching to locally managed tablespaces

On Wed, 3 Jul 2002 06:30:40 +1000, "Howard J. Rogers" <dba_at_hjrdba.com> wrote:

<snip>

>If it were me, and if you had sufficient disk space (and given your reported
>size, I think you probably do), I would instead be inclined to create a
>bunch of new LM tablespaces, and simply issue the 'alter table X move
>tablespace Y' command. When everything has been moved, you can then just
>drop the old DM tablespaces, as they'll be empty. You have then to rebuild
>all indexes, which is a slight bummer, but only a slight one.

</snip>

But be aware of tables with LONG columns! When migrating from 7.3.x there's a good chance to have some in the database (yes, I know they're deprecated). ALTER TABLE ... MOVE ... doesn't work with LONGs, so you have to migrate your LONGs to CLOBs first or do some nasty workarounds.

SQL> create table t
  2 (n number, l long)
  3 tablespace ts_a;

Table created.

SQL> alter table t
  2 move tablespace ts_b;
alter table t
*
ERROR at line 1:
ORA-00997: illegal use of LONG datatype

SQL> drop table t;

Table dropped.

SQL> create table t
  2 (n number, c clob)
  3 tablespace ts_a;

Table created.

SQL> alter table t
  2 move tablespace ts_b;

Table altered.

Marc Blum
mailto:marc_at_marcblum.de
http://www.marcblum.de Received on Sat Jul 06 2002 - 14:40:33 CDT