Re: How to change object's tablespace ?

Djuro,

If u are planning using exp-imp to do the objects tablespace relocation, it will
be the best and fastest way of doing that. You started a good step by creating a new user with default tablespace as u needed, but the problem is that u gave the user the "resource" role, which allow him to write on every tablespace.
Next time, create the user with connect, create table, create procedure etc and give him a "quota unlimited" on the target tablespace, and "quota 0k" on the
source tablespace. and then when the import will try create those objects on the
old tablespace - it will fail cause quota is 0k, and it will create the objects on
the default tablespace.

Asaf.

Djuro D <Djuro.Dretvic_at_Infodom.hr> wrote in message news:9omps7$c91$1_at_sunce.iskon.hr...
> Sorry, next time I'll try to be more percise.
>
> Detail's of a problem:
>
> 1. My database is running on RDBMS version 8i (8.1.6) for Wndows NT/2000.
> I'm planning to upgrade it on version 8.1.7. (Release 3)
> 2. The operating system is Windows 2000 Advanced Server (Service pack 2).
>
> My problem is how to move all user object from one tablespace to another,
> not one by one. I have approximately 800 object's.
> The hardest and longest way is to recreate the object and move their data
> (one by one), but is there another(better) way to reallocate them.
> I tried with EXPORT/IMPORT. (FROMUSER=JOHN TO USER=JOHN). User John has
> default tablespace "SYSTEM" in source database, but the same user(John)
has
> default tablespace "USERS" in targeted database. After the import
complete,
> the object's remain in the same tablespace. I have the same situation as
> before. My goal is that all created John object's are in the USERS
> tablespace.
>
>
> Regards, Djuro.
>
>
>
>
> "Sybrand Bakker" <postbus_at_sybrandb.demon.nl> wrote in message
> news:tqrs9d8pi05v3d_at_news.demon.nl...
> >
> > "Djuro D" <Djuro.Dretvic_at_Infodom.hr> wrote in message
> > news:9okpjf$rqa$1_at_sunce.iskon.hr...
> > > Hi,
> > >
> > > What is the best way to move object (tables, indexes etc. ) from one
> > > tablespace to another tablespace ?
> > >
> > > Here is my problem. Our developers put their object's in system
> > tablespace.
> > > I have to move them into the useres tablespace.
> > > What is the easiest solution ?
> > >
> > > Thanks in advance.
> > >
> > > Regards, Djuro
> > >
> > >
> > >
> >
> > Tne answer is partly version specific. Please *always always* include
> those
> > 3 digits and two dots. No one in this group has a crystall ball and/or
> will
> > assume you are running with the latest version.
> > From 7.3 onwards indices can be rebuild using alter index rebuild
> > In 8i a table can be moved with alter table move
> > Before 8i this would require either a CTAS (Create table as select) and
> some
> > fiddling, or export / import.
> >
> > Regards,
> >
> > Sybrand Bakker, Senior Oracle DBA
> >
> > >
> >
> >
>
>
Received on Mon Sep 24 2001 - 16:05:32 CDT