Re: way to move a table to a new tablespace?

Didn't see the original, but....

"Connor McDonald" <connor_mcdonald_at_yahoo.com> wrote in message news:398943C0.432D_at_yahoo.com...
> Ian Warshak wrote:
> >
> > this sounds simple, but i have looked everywhere and cant figure out how
 to
> > move a table to a new tablespace. i'd appreciate some advice if you all
 dont
> > mind..
> >

Your Oracle version would be nice to know, since the advice is different in each case.

As Connor explains below, there is a 'move tablespace' command in 8i. That's very handy, because it preserves Indexes and constraints and so on.

On the other hand, I disagree with him about using export and import, since import explicity re-creates the table in precisely the same tablespace as it came from (assuming there is an identically-named tablespace in the destination as there was in the source database). If there is no exact match on tablespace name, only then will import create the table in whoever is running import's default tablespace. Not exactly an easy way of moving objects around.

A far easier approach is to issue the command 'create table newone tablespace Y as select * from oldone'. That creates a newone table in whatever tablespace you fancy as an exact copy of oldone. The drawback? It doesn't bring across any constraints (except, somewhat perversely, not nulls), so you'd have to re-define them all. All the indexes on the table still belong to oldone, so you'd have to re-create them too. When everything is done, you can simply drop oldone, and rename newone to the oldone name.

> > also - i have used mysql and there is a command to change databases,
 which i
> > think is roughly equivalent to changin tablespaces. is there a way that
 i
> > can effectively change my current tablespace to a different to so i can
 work
> > on some different tables without having to reference them by
> > tablespace.table? (say for example i have tables in 2 tablespaces)
> >

Table references in Oracle never rely on tablespace names. 'Select * from scott.emp' means that the emp table is owned by the User Scott -ie, it's said to be in his 'schema'. No mention of tablespaces, and neither syntactically can there be. That's why you can have any number of EMP tables in the same tablespace, provided they are all owned by different people. Scott.emp is different from system.emp and both are different from howard.emp.

Therefore, you can do your work on any number of tables whereever they happen to be stored.

If you mean can you do some work on ian.table and then switch to work on mary.bigtable, then you can do that without any fuss or bother, provided you have object privileges on mary.bigtable. Assuming she has granted you such rights, you don't have to switch anywhere or do anything -you just issue select statements as before, only remembering to explicitly include the schema name (ie, in tis case, 'mary').

Regards
HJR
> > thanks again
> > ian
>
> alter table XXX move tablespace NEW_TS;
>
> (available from 8i onwards)
>
> before that, its export/import time or equivalent.
>
> HTH
> --
> ===========================================
> Connor McDonald
> http://www.oracledba.co.uk
>
> We are born naked, wet and hungry...then things get worse
Received on Sat Aug 19 2000 - 23:10:41 CDT