Re: Changing tablespaces on IMP?

On Feb 27, 7:49 pm, "Mark D Powell" <Mark.Pow..._at_eds.com> wrote:
> On Feb 27, 2:00 pm, Frank van Bortel <frank.van.bor..._at_gmail.com>
> wrote:
>
>
>
>
>
> > dean schreef:> Is it possible to change the tablespaces of the tables during an
> > > import, from their original ones? Right now when we import a schema
> > > from our clients, to bring the data down to out test servers, we have
> > > to watch the error messages from the tablespace not existing, create
> > > the tablespaces, and then try again.
>
> > > Oracle 9.2i, 10g v 1 and 2. Windows, Sun, Linux.
>
> > > Thanks
>
> > > Dean
>
> > If the tables are pre-created an the_other_tablespace, no problem.
> > If the user you import to does not have the right to create tablespaces,
> > nor does that user have unlimited tablespace rights (which grant
> > defaulted with the resource role since 8i), the tables will go
> > into the users default tablespace.
>
> > I would precreate the tablespaces, though. A simple
> > imp system/manager indexfile=showme.txt is enough to see
> > all create tables statement and tablespaces.
>
> > This does *not* import any data - it just creates a file
> > with create table, and create index statements. A remnant
> > from the time indexes "should be imported separately" (V5) ?
> > --
> > Regards,
> > Frank van Bortel
>
> > Top-posting is one way to shut me up...
>
> Be advised that there is a problem with using the set the owner
> default tablespace method of changing the target tablespace for a
> table. At least on 81.7 and 9.2 LOB columns that reference a non-
> existent tablespace do not default properly during the import
> resulting in an import error. In this case you need to preallocate
> the table.
>
> Pre-allocation is also the solution when more than one target
> tablespace is desired though you could run multiple table= imports
> changing the owner default tablespace each time. You can use the imp
> indexfile= parameter to generate table source code. It will be
> commented out but it exists in the index code file. You can also use
> the dbms_metadata package to generate table source.
>
> HTH -- Mark D Powell --- Hide quoted text -
>
> - Show quoted text -

Thanks for all the replies, just what I needed.

Dean Received on Wed Feb 28 2007 - 08:46:24 CST