Re: How do you change a tables tablespace?

Howard,

thank you for clarifying this (seemingly) common misunderstanding of the behavior of Oracle import.

maybe this misunderstanding started when somebody who knew someone that had heard from another that it would be nice if the oracle import utility would work this way... and not a one of them made the effort to consult the Utilities manual or run a test case...

to relocate a table from one tablespace to another using import, just create the table in the desired tablespace BEFORE running imp with IGNORE=Y.

thanks again for setting the record straight.

"Howard J. Rogers" <howardjr_at_www.com> wrote in message news:3a6fac7c_at_news.iprimus.com.au...
> 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 - 23:57:39 CST