Re: Changing tablespaces on IMP?

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 nonexistent  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 -- Received on Tue Feb 27 2007 - 18:49:05 CST