Re: How do you change a tables tablespace?

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 - 21:26:30 CST