Re: How do you change a tables tablespace?

This simply isn't true.

Firstly, when import can't find a tablespace of the same name, it will attempt to store the segment in the User's default tablespace. I think we'll happily agree on that. (Incidentally, the import will fail if the User has zero quota on his default tablespace).

Now you are suggesting that import *will* find a tablespace of the same name, but not being able to create it there because of quota restrictions, it will attempt to create it somewhere else (perhaps in the User's default tablespace??). Well, I've just tested that too.

User Fred had unlimited quota on SYSTEM and DATA. DATA is his default tablespace.
He created a table BLAH in SYSTEM
He then exported that into a dump file.
As SYSTEM/MANAGER, I then changed Fred's quota on SYSTEM to zero I then dropped the BLAH table
Fred then ran import, and got the error message : "space quota exceeded for tablespace SYSTEM". Note he still had unlimited rights to DATA.

It just isn't the case that when confronted with lack of space in a tablespace (which is effectively what a zero quota is) that import will decide to get clever and move the segment some place else. It only ever has two choices: the same tablespace, or the default tablespace of the User *if it can't find an identically named tablespace*. Clearly, my little test demonstrates that if it finds an identically-named tablespace, it stops trying to look elsewhere.

Hence to claim that "oracle will place it where it can" is just not true.

Regards
HJR "Doug C" <dcowles_at_i84.net> wrote in message news:o67v6tgv6jep7er2gh001m81p7fhsh794u_at_4ax.com...
> I would just like to point out a note on the original problem way back on
 the
> thread where someone was trying to move objects from one tablespace to
 another.
> It was mentioned that
> "Option 3 is utterly impossible using normal import commands because
 import
> always attempts to create the new table in exactly the same tablespace as
> the tablespace the segment came from -and if there is one tablespace we
 can
> guarantee exists in all databases (and hence will be re-used on import)
 it's
> SYSTEM. "
>
> This is easily avoidable by making sure the new user has no quota on the
> offending tablespace. Having no other option, oracle will place it where
 it
> can.
>
>
>
>
> On Mon, 22 Jan 2001 15:52:09 GMT, Brian Peasland
 <peasland_at_edcmail.cr.usgs.gov>
> wrote:
>
> >Falco,
> >
> >> The cluster implementation you describe might be right, but a table is
 still
> >> a table.
> >
> >A table is still a table, but a table that is part of a cluster is no
> >longer a "table". It is now one component to a cluster. If the cluster
> >is made of Table A and Table B, then Table A is no longer a distinct
> >entity. True, you can query from Table A only. But when you do this, you
> >in effect query the cluster, not just the table. So that means that you
> >query Table B as well. This is due to the physical nature of a cluster.
> >
> >> Frankly speeking as a DBA, I want to manage a clustered table just like
 a
> >> normal table.
> >
> >You can't do this. You may want to, but you can't. Once a table becomes
> >part of a cluster, then there are operations that you can not do
> >independently to the table and leave the cluster alone. For instance,
> >you can not move the table and leave the rest of the cluster.
> >
> >> Partitions are implemented as segments too, yet these I can manage in
 an
> >> normal fashion!
> >
> >Partitions have a completely different physical makeup than a cluster
> >does. A cluster has it's own unique physical characteristics. This is
> >exactly the point that HJR is trying to make. So while there are
> >operations that you can perform on a table, or a partition, you may not
> >be able to perform them on a cluster.
> >
> >> Further, if it's so difficult for Oracle to find the table rows, how
 can it
> >> query correctly then?
> >
> >It queries correctly, but it does not query only the one table. Going
> >back to my example where the cluster is composed of Table A and Table
> >B... If I issue the following:
> > SELECT * FROM Table_A;
> >then Oracle reads the *entire* cluster. This includes Table B. Before
> >giving the results back to you, Oracle discards the Table B component.
> >But it is still read.
> >
> >> Also, from a DBA standpoint, everyting in the docs tells me that a
 table in
> >> a cluster is
> >> just a table sitting in a cluster! There are ** NOWHERE ** distictions
 being
> >> made!
> >
> >You might want to go back and re-read the Oracle docs. Specifically
> >refer to
>
>http://technet.oracle.com/docs/products/oracle8i/doc_library/817_doc/server
 .817/a76965/c08schem.htm#12872
> >In that document, you will find "multiple tables have data in each
> >block". This sounds like a big distinction from normal tables to me!
> >
> >HTH,
> >Brian
>
Received on Wed Jan 24 2001 - 22:33:43 CST