Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> 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